What is a Pivot Table?
Pivot table is the very useful data summarization tool in excel sheets (Microsoft, Openoffice.org, Lotus 1-2-3 etc.). In the simplest way, pivot table can create a report based on the haphazard data that you enter in the spreadsheet. No need to sort the columns or adding sum functions for the columns or any summarization activity. Pivot table do everything for you.
Consider the below excel sheet with a very simple data, double click on below image to download it
The sample data is,
This data shows the number of units sold (Sales) and models inquired (Inquiry) on a particular date (Date) of a particular model (Model) of the respective company (Company).
I have created a report with the above data using the pivot table as below. Double click on below image to download the report:-
The report looks as follows:-
Thus, the report shows the Sales and Inquiry for different company models on 11/2/08. It also shows total for the company and Grand total of Sales and Inquiry for that date.
I did this in less than 1 minute. How?
On the original excel sheet Data drop down menu on the top select Pivot table and Pivot Chart Report option as below:
You will get the below window:-
Click Finish.
You will get a new tab (see Sheet1 tab) below in the same excel sheet.
Company and Model are the row fields for our report and the Date is the column field. So from the Pivot Table Field List (if hidden click on the pivot table, you should get it), drag the Company and drop it to ‘Drop Row Fields Here’ position. Drag Model and drop it to the same location. Drag Date and drop it to ‘Drop Column Fields Here’ position. The report would look like
Note: – If you get the rows sequence in different order than as you need, you can drag and drop the rows to the position of the row you want. The rows can shift as you wish.
Sales and Inquiry are the Data fields. So drop them one by one to the ‘Drop Data Items Here’ location. The report would look like
Now, you can do anything with your report.
-
Right click the Sum of Sales field and in the field settings you can change the name of the field to Sales. It may show the Pivot Table field name already exists1 message. Put a Space before typing Sales. Now you will be done. You can do the same with Inquiry.
-
In the field setting, you can see various criteria for summarization like Sum, count, Average, Max, Min etc. Thus, you can get summarization of the data items on any criteria.
-
You can drag the Data dropdown and drop it on the columns (say any of the dates 11/2/08). You will get the columnar entries of the Sales and Inquiry data items.
-
From the Date dropdown tick only the 11/2/08 checkbox. Uncheck all other dates check boxes. You will get the report only for 11/2/08.
-
Right click on any part of the filled report/pivot table, and explore all the options. If you click the Refresh Data option, which is one of the most necessary one, you can refresh the table with the changes that you have done in the original excel sheet. I also feel, ‘Table Options’ option is very useful and important one. It looks like below:
All the options above are self explanatory. Try each one on our pivot table. In the Data Options you can see the Refresh on Open option. If you check this, on every open of the Pivot table tab, the table will be refreshed with the updated data on the original excel sheet. Do not edit the pivot table reports. They are totally derived from the original excel sheet data and is the summarized report representation of that data.
-
Pivot table gets saved when you save the excel sheet. Original excel sheet tab is just beside the pivot table tab in the excel sheet. Whenever you update the original excel sheet, you just need to refresh the pivot table to get your changes reported.
Related Articles
Lightweight data exploration in Excel