Data analysis on a large set of data is quite often necessary and important. It involves summarizing the data, obtaining the needed values and presenting the results.
Excel provides PivotTable to enable you summarize thousands of data values easily and quickly so as to obtain the required results.
Consider the following table of sales data. From this data, you might have to summarize total sales region wise, month wise, or salesperson wise. The easy way to handle these tasks is to create a PivotTable that you can dynamically modify to summarize the results the way you want.
To create PivotTables, ensure the first row has headers.
As you can see in the dialog box, you can use either a Table or Range from the current workbook or use an external data source.
A Blank PivotTable and a PivotTable fields list appear.
In case you are new to PivotTables or you do not know which fields to select from the data, you can use the Recommended PivotTables that Excel provides.
Click the data table.
Click the INSERT tab.
Click on Recommended PivotTables in the Tables group. The Recommended PivotTables dialog box appears.
In the recommended PivotTables dialog box, the possible customized PivotTables that suit your data are displayed.
Click OK. The selected PivotTable appears on a new worksheet. You can observe the PivotTable fields that was selected in the PivotTable fields list.
The headers in your data table will appear as the fields in the PivotTable.
You can select / deselect them to instantly change your PivotTable to display only the information you want and in a way that you want. For example, if you want to display the account information instead of order amount information, deselect Order Amount and select Account.
You can even change the Layout of your PivotTable instantly. You can use the PivotTable Areas to accomplish this.
In PivotTable areas, you can choose −
An instant update helps you to play around with the different Layouts and pick the one that suits your report requirement.
You can just drag the fields across these areas and observe the PivotTable layout as you do it.
If you have more than one field in any of the areas, then nesting happens in the order you place the fields in that area. You can change the order by dragging the fields and observe how nesting changes. In the above layout options, you can observe that
The resulting PivotTable is as follows −
In the PivotTable Areas, in rows, click region and drag it below salesperson such that it looks as follows −
The nesting order changes and the resulting PivotTable is as follows −
Note − You can clearly observe that the layout with the nesting order – Region and then Salesperson yields a better and compact report than the one with the nesting order – Salesperson and then Region. In case Salesperson represents more than one area and you need to summarize the sales by Salesperson, then the second layout would have been a better option.
You can assign a Filter to one of the fields so that you can dynamically change the PivotTable based on the values of that field.
Drag Region from Rows to Filters in the PivotTable Areas.
The filter with the label as Region appears above the PivotTable (in case you do not have empty rows above your PivotTable, PivotTable gets pushed down to make space for the Filter.
You can see that −
Click the arrow in the box to the right of the filter region. A drop-down list with the values of the field region appears.
The data pertaining to South and West Regions only will be summarized as shown in the screen shot given below −
You can see that next to the Filter Region, Multiple Items is displayed, indicating that you have selected more than one item. However, how many items and / or which items are selected is not known from the report that is displayed. In such a case, using Slicers is a better option for filtering.
You can use Slicers to have a better clarity on which items the data was filtered.
Click ANALYZE under PIVOTTABLE TOOLS on the Ribbon.
Click Insert Slicer in the Filter group. The Insert Slicers box appears. It contains all the fields from your data.
Select the fields Region and month. Click OK.
Slicers for each of the selected fields appear with all the values selected by default. Slicer Tools appear on the Ribbon to work on the Slicer settings, look and feel.
Selected items in the Slicers are highlighted. PivotTable with summarized values for the selected items will be displayed.
In the examples so far, you have seen summarizing values by Sum. However, you can use other calculations also if necessary.
In the PivotTable Fields List
The Value Field Settings box appears. Several types of calculations appear as a list under Summarize value field by −
The PivotTable summarizes the Account values by Count.
Follow the steps given below to learn to use the PivotTable Tools.
The following PivotTable Tools appear on the Ribbon −
Some of the ANALYZE Ribbon commands are −
Some of the DESIGN Ribbon commands are −
You can either expand or collapse all items of a selected field in two ways −
All the items under East will be collapsed. The Collapse symbol to the left of East changes to the Expand symbol .
You can observe that only the items below East are collapsed. The rest of the PivotTable items are as they are.
Click the Expand symbol to the left of East. All the items below East will be displayed.
You can collapse or expand all items in the PivotTable at once with the Expand Field and Collapse Field commands on the Ribbon.
All the items of the field East in the PivotTable will collapse.
Click Expand Field in the Active Field group.
All the items will be displayed.
You can choose the presentation style for your PivotTable as you would be including it as a report. Select a style that fits into the rest of your presentation or report. However, do not get over bored with the styles because a report that gives an impact in showing the results is always better than a colorful one, which does not highlight the important data points.
Blank rows will be displayed after each value of the Region field.
You can insert blank rows from the DESIGN tab also.
PivotTable in Outline Form with the selected Style will be displayed.
To understand how to use Timeline, consider the following example wherein the sales data of various items is given salesperson wise and location wise. There are total 1891 rows of data.
Create a PivotTable from this Range with −
Click Date and click OK. The Timeline dialog box appears and the Timeline Tools appear on the Ribbon.
Timeline is selected to Q2 – Q4 2014.
PivotTable is filtered to this Timeline.