EZ guide to PivotTables: Grouping values areas
Simon Hurst delves further into his analysis of specific techniques used when working with PivotTables.
Now that we have explored grouping data in a PivotTable and a PivotChart, we will consider in this part the different ways users can calculate figures shown in the Values areas.
As done in the previous tutorial, we are continuing our analysis of house sales values in England, Scotland and Wales using publicly available data.
We are using the first CSV (comma separated value) file in the list - Average price.csv - and have downloaded it straight into an Excel workbook before turning it into the PivotTables we have created in the previous parts of the series.
A Values cell is just an aggregation of all the items in a 'virtual' table
It's worth pointing out that the table of data that we are using is already aggregated to calculate an average sale price per region per month. It's also worth thinking about what each value in the values area of any PivotTable represents. Each value cell is the aggregation of all the individual items in the underlying table, subject to filters.
If you have no fields in the Rows, Columns or Filters area, then the single figure shown when you add a field to the Values area will be the aggregation of all of the individual items in the underlying table for that field. Fairly obviously, when you add a field to the Filters area and select one or more items, the underlying table is filtered to only include rows that are 'passed' by the filter, and so the values area figure shows the aggregation of just those rows.
Although not actually described as filters, the Rows and Columns areas work in a similar way. The Values cell at the intersection of any row or column shows the aggregation of all the rows in a virtual table created by filtering the full data table by:
- Any specific selection in any Filters area field
- The items in any Rows field in that row
- The items in any Columns field in that column
Although we have described this as a 'virtual' table, it's easy to see the actual table that lies behind each Values area cell. You just need to double-click on the cell to create a new worksheet that contains the table.
In this example, we can see the total of 521963.6391 at the intersection of the Year 2008 and the Quarter Qtr3. The whole table is specifically filtered by the Region_Name Aberdeenshire:
If we double-click on our cell, we can see the resulting table on the newly inserted sheet:
We can see that the only rows in our table are those for the year 2008 and the quarter 3 and where the Region_Name is Aberdeenshire. We have added a SUM() total just to show the agreement with the PivotTable Values cell.
Just as we can use a whole range of Excel aggregation functions in a cell, we can choose different aggregation types for a PivotTable Values field. If we right-click in a Values cell and choose Summarize Values By we can see the available options, including More Options.
Most of the basic aggregations are straightforward. Here we have removed the Quarters field from the Columns area and added the Average_Price field to the Values area six times and chosen each of the basic aggregations. To improve clarity, we have then changed the column headings and the number formats:
Because, as mentioned above, our figures are already monthly averages, the Count column shows us the number of months for which we have figures in each year. As the data only includes results up to October 2016, we can see that for this year we have just 10 items. Average shows us the Average monthly sales value for the year (the Sum value divided by the Count value) while Max and Min show us the highest monthly average total and lowest average monthly total respectively. Product is, certainly in this case, somewhat less useful, showing us the 12 monthly values multiplied together. We can see how these different aggregations work in more detail by adding the Date field to the Rows area in order to show us the individual values that are involved in each calculation:
More Options displays the full list of aggregate functions that can be used, adding the following to those shown above:
- Count Numbers
In some circumstances, you might see an additional option. Where a PivotTable is based on an OLAP cube or the Workbook Data Model used by Power Pivot (and, from Excel 2013, within Excel itself), you will also be able to calculate the DistinctCount. Rather than just the count of all the items in our virtual table, this is the count of unique items. In our example, each of our Region_Names is included for each of our months, so the count will be the number of different regions multiplied by 12 (or 10 for 2016). DistinctCount will give us the actual number of different regions for which we have figures in each year:
Show Values As
So far, we have looked at the different aggregation options in the Values area, but there is another option on the right-click menu that changes what the Values area displays: Show Values As. This takes each of our values and applies a further calculation, such as calculating what percentage of the overall total each value represents.
Here we have used The Running Total In… option to demonstrate how Show Values As works. Because we have three fields in the Rows area, we can choose which of these fields to use to create our Running Total. If we choose Years, each year total will be added to the cumulative year total. For Quarters, each quarter will be added to the cumulative quarter total and then reset at the end of each year and Dates will be cumulative within quarters:
For each of the Show Values As options, the ellipsis indicates the need to provide further information such as specifying which item to calculate a Difference From or Percentage of, and which Base Field to use as the basis for calculating a Rank.
Simon Hurst is the founder of technology training consultancy The Knowledge Base and is a past chairman of the ICAEW's IT Faculty.