Data Query and Report | Pivot Tables

Summarize Your Data With Pivot Tables!

A pivot table is a powerful data summarization tool in Microsoft Excel. It can automatically sort, count, and total data stored in a spreadsheet and create a second table displaying the summarized data, among various other functions. The following examples demonstrate how a huge amount of data can be summarized and cross tabulated.

Pivot Tables

A pivot table is a powerful data summarization tool in Microsoft Excel. Among other functions, it can automatically sort, count, and total data stored in a spreadsheet and create a second table displaying the summarized data. The following examples demonstrate how a huge amount of data can be summarized and cross tabulated.

Pivot Tables Example

The data used in these examples come from a college. Figure 1. shows part of the raw data in the database. Class (Subject), Department (Group). Year and Grade fields are used to construct the pivot tables.

Fig 1. Screen shot of school raw data

The pivot table shows in Figure 2. answers the question: "how many students enrolled into each of the departments during the last 10 academic years?".

Fig 2. Screen shot of number of students by department by year

The pivot tables show in Figure 3. shows how did the students perform under each department.. The table on the left shows the number of students by department and by grade. The right table shows the figures in percentage from the total. This provides a comparison on how each department is performed percentage wise.

Fig 3. Screen shot of number and percentage of students by grade by department

This example adds another dimension, year, to the prior example. Fields Department, Grade, and Year are included.

Fig 4. Screen shot of number of students by grade by department by year

The final example includes another layer, subject (class), to the pivot table. This pivot table shows how many students received what grade in which class under which department and in what year

Fig 5. Screen shot of number of students by grade by department by year by subject

Quick Contact Box

Data Quality Services

The importance of data management is often overlooked by many companies. They frequently underestimate the important contribution data management makes to the success or failure of their operations. Data quality is vital to business intelligence. Companies typically spend thousands and even millions of dollars setting up business intelligence systems to improve their operations, but the results generated by these efforts are only as good as the data that is fed into them.

Many fall short of their expectations because of poor data quality issues. Contradictory, inconsistent or inaccurate information exposes companies to many business risks that lead to increased costs, customer dissatisfaction, poorer decision making and lost business. Clean, high quality data helps company decision makers to accurately and correctly assess their business activities and avoid potential pitfalls that can significantly impair a company's profitability.

At Excel Business Solutions we offer companies with data cleansing, data integration, data enrichment and data mining services in support of accurate reporting, analysis and business decisions; and consequenentially, minimize risk and cost, enhance business opportunity and increase returns.
More Info