Saturday, September 10, 2011

Simple method to make Pivot table

Pivot table is a very powerful and helpful tool available in excel for data processing. It helps in summarizing large amount of data with ease. It can be used to derive sum, averages, count from the data stored in a spreadsheet in various combination.  For e.g. geography / product wise total sales revenue or Department/location wise headcount etc.


The screenshot 1 below shows the various components of an Pivot tables and the screenshot 2 shows the Sales revenue generated by each product in various geography with an option to select the Sales Person (in the Report filter).

Screenshot 1: (Pivot table components)
Screenshot 2: (Pivot table example)
Below mentioned are the steps to make Pivot table.
  1. Select the data on which you want to create a pivot table.
  2. Click Insert à PivotTable à PivotTable

3. Select a table range (by default you should get the data range selected in this) & cell where you want to put the new pivot table. Select New worksheet to put the pivot table in new worksheet. Click OK.

4. A new worksheet will be added in the file as per Screenshot 1 above.
5. Drag fields in the pivot layout. Notice the changes in Pivot table after dragging each field
  •  Drag “Product Name” into Row Labels 

  • Drag “Geography” into Column Labels

  • Drag “Revenue ($)” into Values

  • Drag “Sales Person” into Report Filter

  • Select the data and change the Number format as required













That’s it, the pivot table as shown in the example above is ready. You can try different slice and dice options of the pivot table and derive meaningful information out of your data.

No comments:

Post a Comment