The Report Generator in Microsoft Excel; Master the Pivot Table in Minutes!

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

clip_image002

The sample data is,

Data

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

clip_image004

The report looks as follows:-

Report

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:

clip_image006

You will get the below window:-

clip_image008

Click Finish.

You will get a new tab (see Sheet1 tab) below in the same excel sheet.

clip_image010

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

clip_image012

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

clip_image014

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:

clip_image016

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

25 easy pivot table reports

Pivot Table 101

Lightweight data exploration in Excel

Sanjeev Mishra is a professional blogger and an Internet Marketing Consultant based in India. He has built the Internet Techies to provide you updates in technology and web application area.

2 Comments

  1. Pingback: Tips to improve your experience on Excel 2007 | Internet Techies

  2. Pingback: Resources on Financial usage of Excel like Loan or Insurance Calculation | Internet Techies

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>