excel tutorialExcel Pivot Tables
Pivot tables in Excel are very powerful when you need to extract the significance from a large, detailed data set. This chapter teaches you how to insert a pivot table, how to add some fields to a pivot table report, how to filter a pivot table, how to sort a pivot table, how to refresh a pivot table and how to change the type of calculation that you want to use to summarize the data.
The easy example below uses a data set that consists of four fields. Last Name, Sales, Country and Quarter.
Insert a Pivot Table
Pivot tables are actually very easy to create. Just execute the following steps and you'll master pivot tables in no time.
1. Open the accompanied Excel file, and click any single cell inside the data set.
2. Click on Insert, PivotTable.
The following dialog box appears.
Excel automatically selects the data for you. The default location for a new pivot table is New Worksheet.
3. Click on OK.
Add fields to a Pivot Table
To add some fields to the pivot table report, execute the following steps.
1. Check the fields Last Name and Sales. Excel automatically places the non-numeric field (Last Name) into the Row area of the pivot table and places the numeric-field (Sales) into the Values area.
2. Click on Country and drag it to the Report Filter area. If you accidentally check Country first, Excel will automatically place the Country field into the Row area. No worries. Just drag it from there into the Report Filter area.
Result:
Done. You've just created a pivot table report. Although this is a very simple pivot table report, it already extracts some valuable information from the data set.
Apparently, Williams is your best salesperson.
Filter Pivot Table
Because we have added the Country field to the Report Filter area, we can now filter the pivot table.
1. Click the filter drop-down and select UK.
As a result, the pivot table will only include sales in the UK.
2. For the next examples, remove the filter by clicking on All.
Sort Pivot Table
In order to get the person with the largest Sum of Sales at the top of the column, we need to sort the pivot table. Simply execute the following steps to achieve this.
1. Click a single cell inside the Total column. The PivotTable Tools contextual tab activates.
2. On the Options tab, click the Sort Largest to Smallest button (ZA).
Result:
Refresh Pivot Table
Any changes you make to the data set are not automatically picked up by the pivot table report. You have to refresh the pivot table manually to update the pivot table report with the applied changes.
1. Click any single cell inside the pivot table.
2. Right click and click on Refresh.
Note: if you change the size of the data set by adding or deleting items, you need to update the source data for the pivot table. Click on Change Data Source in the PivotTable Tools contextual tab.
Change Summary Calculation
Excel, by default, summarizes your data by either counting or summing the items. To change the type of calculation that you want to use to summarize the data, execute the following steps.
1. Click a single cell inside the Sum of Sales column.
2. Right click and click on Value Field Settings...
3. Choose the type of calculation you want to use. For example, click on Count.
Result:
After going through this tutorial, we hope you will be able to create your own valuable pivot tables in Excel.
Did you like this free Excel tutorial? Show your appreciation, vote for us.