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:-
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.
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.