Advanced Excel

Pivot Table Tutorial

Pivot table are one of the several wonderful tools available in Microsoft Excel. By using pivot table you can quickly summarize your data as per your requirements. Pivot Table also has very attractive built in design features as well. Some of the other useful options available in Pivot Table are :

  • Field Setting – Show Value As (Including Running Total, % of Column, % of Row and many more
  • Grouping Date in Year, Quarter and Month
  • Calculated Field and Calculated Items
  • Pivot Charts including Slicer for Dynamic Dashboards

Your pivot table can be based on range of data, table or some external data source as well. Step 1 — Place your cursor any where on your data and Click Insert

Step 2,3 and 4 — Click PivotTable. Create PivotTable dialogue box will appear. In select a table or range your data range will appear if your cursor was on the data as per step 1. Choose where you want the PivotTable report to be placed by default New Worksheet is selected by default. You can change it as per your requirements.

Step 5 — This is the area where Pivot table field appears.

Step 6,7,8,9 and 10 — This dialogue box is Pivot Table field chooser. You can see all the heading of column in your data. You can drag drop fields to the pivot table areas 7,8,9 or 10. Normally you drop numeric data to VALUES areas. If you mark the check box in pivot table field chooser, excel drop the fields at its own in any of the areas in your pivot table report.

Step 11 — We have dropped Product Category to Filters, Product to COLUMN, Customer to ROWS and Amount to VALUES from pivot table field chooser. You can see these in your below pivot table report in 12, 13, 15 and 14. Now you can also experiment by dragging and dropping fields at your own.

In your pivot table report drop down sign is visible at three places in FILTERS, ROWS and COLUMN areas and you can filter your pivot table report with any of these.

When your cursor is on pivot table report the PIVOTTABLE TOOLS become active. In excel 2010/2013 you have two further options, one is ANALYZE and the other is DESIGN.
If your right click on your pivot table report, you have the below options. Some of these options are available PIVOTTABLE TOOLS as well.

Sometime, when we drag numeric data to VALUE area then instead of showing sum of the selected column, the pivot table report shows the count of (name of Column) as per below:

In order to resolve the above, right click on any value showing count instead of sum and select value field setting. Next click on sum in Summarize value field by. It will change the count of Amount to Sum of Amount.

Download Excel File

Leave a Reply

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