How to use the Pivot Table
Your Pivot Table is created and you’re super excited to use it.
But how do you use the Pivot Table?
That’s what this section is all about.
Using Pivot Table Fields
A Pivot Table ‘field’ is referred to by its header in the source data (e.g. ‘Location’) and contains the data found in that column (e.g. San Francisco).
By separating data into their respective ‘fields’ for use in a Pivot Table, Excel enables its user to:
- Display datasets in a more logical output e.g. ‘Total Units Sold’ in San Francisco. This is covered in greater detail later when we discuss ‘Value Field Settings’.
- Extract and display relevant data by organizing it in a way that is more suitable – either figuratively or aesthetically.
- Filter the data accordingly based on values found in the selected ‘field’.
Adding Columns to Your Pivot Table Fields
Let’s take a step back and recreate the Pivot Table with our added October data:
Now we have our blank table with which to start arranging the data with our Pivot Table ‘fields’.
All available fields found in the data set – as defined by its headers – are listed on the right-hand side in the ‘PivotTable Field List’ area.
To add data columns into the table, drag and drop the desired field into ‘Column Labels’, ‘Row Labels’, or ‘Values’ (these 3 are also covered in more detail later).
This example setup would list the data in rows separated by ‘Location’ and ‘Item’. Columns are separated by ‘Month’.
This forms a grid to display the ‘Total of Units Sold’ in each ‘Location’ by ‘Month’.
The output of this arrangement looks like the following:
When we select ‘Location’ and ‘Item’ for our rows, something cool happens:
- The ‘Location’ totals for each month are now viewed as subtotals for each month.
- The breakdown by ‘Item’ is also visible within each ‘Location’.
To hide ‘Item’ rows, click on the minus next to the ‘Location’ rows.
This leaves only the subtotals visible for that ‘Location’.
Using Report Filters
With the Pivot Table set up, you can see the breakdown of total sales across the chosen ranges (‘Month’, ‘Location’, and ‘Item’).
What if you needed to provide a look at units sold by item for all the locations?
This is where ‘Report Filters’ come into play.
You drag and drop ‘Item’ into the ‘Report Filter’ area.
Let’s also set ‘Rows’ to ‘Month’ and ‘Columns’ to ‘Location’.
This gives you a brand-new perspective on the data.
This adds another row above your Pivot Table with the selected ‘Filter Field’.
There’s also a drop-down box to make a selection…
This allows the user to select only the relevant data required for the query:
If you want to select multiple entries from the filter, check the box next to ‘Select Multiple Items’ at the bottom of the drop-down.
Then you will be able to select all available entries in the filter in whatever combination you need.
Here we will select “Car” Rack and note the change in values:
Value Field Settings
In the previous example, we used the ‘Sum Value Field Setting’.
This displays ‘Total Units Sold’ for each ‘Month’ and ‘Location’ listed in the Pivot Table.
By using different ‘Value Field Settings’, the data in the field presented under ‘Values’ is presented in different ways.
To access a field’s ‘Value Field Settings’, click on its entry. Then select it from the resulting drop-down:
There are several possibilities for ‘Value Field Settings’. I’m going to show you the most common.
These settings are only available for fields placed in the ‘Values’ area.
It’s not possible to apply them elsewhere.
Click on ‘Value Field Settings’ and select ‘Average’.
This tells the Pivot Table to average the content of all the entries in that field and display that as the value.
But, this doesn’t tell you anything about how many entries (transactions) there are.
Neither does it tell how they’re spread across all products.
It gives you the average number of products sold.
Selecting ‘Count’ tells the Pivot Table to add up the number of entries in that field.
Then display that as the value.
In this case, this translates to the number of transactions (or rows) in our original data.
Performing this action on the example data yields the following:
Notice how the upper left cell has changed to show it is now displaying values as ‘Count of Units Sold’.
This data shows that there are 25 transactions occurring in April at Chicago.
Distinct Count (Count unique values)
Selecting ‘Distinct Count’ is like ‘Count’.
But it will ignore (not count) any entries with a value it has already encountered.
Using the example data with ‘Units Sold’, let’s say two transactions in April at Chicago both sold 5 units.
Only one is unique. So, it’s only counted as one.
Notice how the upper left cell has changed to show it is now displaying values as ‘Distinct Counts’.
Using ‘Distinct Count’ is a useful ‘Value Field Setting’.
For instance, if you wanted to count how many types of an Item was sold in each month at each location.
Still using the above example, the fields have been set up.
Any relevant filters are applied and the ‘Value Field Settings’ have been set to ‘Sum’.
This shows us ‘Total Units Sold’.
What if you no longer want to see it monthly, but want to see the data organized into financial quarters?
That’s where ‘Grouping’ comes into play!
First, select any entries you wish to group (this is only applicable for ‘Row’ and ‘Column’ fields).
In this case, we will group April, May, and June.
Right click and select ‘Group’:
This will display a new column preceding it with a defaulted group name.
Doing this for the remaining months splits them into quarters as follows:
Now, this operation has achieved the technical task of grouping the data.
But visually, it does not portray what we are trying to achieve.
To rename the separate group entries, click on their cell and replace the text.
In this case, ‘Group 1, 2, and 3’ are replaced with ‘Quarter 1, 2, and 3’.
Perform the same to ‘Month2’ to achieve the following:
Finally, you need to display the values in these groups.
Add ‘Subtotals’ to the groups by right clicking on ‘Quarter’ and select ‘Subtotal “Quarter”’.
Now you will have subtotals for ‘Units Sold’ by quarter.
This displays subtotals in addition to the separate entries in the group.
If the sub-entries are no longer needed, collapse each group using the +/- box next to each ‘group field’.
This displays the table with collapsed groupings:
‘Calculated Fields’ allow you to insert values into the Pivot Table based on formulas.
These formulas can use the values of fields that are present.
Let’s say the manager at each location gets a bonus percentage added to his salary. This bonus is based on the number of units sold each month.
If that bonus % was equal to Units Sold / 100 we’d have to add more data to the original table to be able to calculate this.
But, with ‘Calculated Fields’, this isn’t necessary!
Select the Pivot Table.
Then, navigate to the ‘Analyze’ tab.
Find ‘Fields’, ‘Items’ and ‘Sets’ in the ‘Calculations’ group.
Clicking ‘Calculated Field’ here opens the ‘Calculated Field’ dialogue.
Experiment using different operations and using different fields to see what is possible.
Using the example of ‘Bonus’ as a new ‘Calculated Field’, inserting it in the ‘Values’ area yields:
Now it’s easy to see what percentage bonus each manager should get in each month.
All this without adding any extra data to the original source.
Pivot Table Drill Down
The primary purpose of the Pivot Table is to combine multiple ‘fields’ of the same type.
Then display them in the desired way.
It’s also handy to be able to do the opposite:
Namely, view the results that comprise the given value entry on the table.
Consider the scenario where a ‘Count of Units Sold’ is provided.
This is broken down into ‘Month’ and ‘Location’ on a 2×2 grid layout (as in the earlier examples).
Let’s say you are asked for a list of transactions that comprise the entry in May at San Francisco.
Further, it requires all data associated with those transactions.
Then the Pivot Table’s collapsible functionality has hidden these details from view!
The operation of drilling counters this. Instead, every record associated with that field entry is displayed.
Using the above example, right click the ‘Value’ cell associated with May and San Francisco.
Then select ‘Show Details’:
This will drill that data point to gather all its associated entries.
These will then be displayed in another sheet by default:
This is a way of backtracking to the original data.
You now have an auto-generated list of entries for this data point.
Use data from a Pivot Table in a formula with GETPIVOTDATA
So far, I’ve shown you how to use a Pivot Table with data that comes from somewhere else.
For example, you use a Pivot Table to create a report from a set of sales data.
Although a Pivot Table is powerful, you still have plenty of uses for other formulas in Excel. Sometimes, these formulas refer to data inside a Pivot Table.
This poses an issue; what happens when the Pivot Table is rearranged?
Excel has made the ‘GETPIVOTDATA’ function to help you with references to Pivot Tables.
This function glues together the references and the cell they are referring to. It’s just like using relative references.
The ‘GETPIVOTDATA’ function is unique. As opposed to other functions, all you need to do is to click a cell.
Start a formula in cell G5.
Type the equal sign and click a cell with data within the Pivot Table. Then hit ‘Enter’.
It will look like this:
The entire ‘GETPIVOTDATA’ function is constructed for you by Excel.
This is the syntax of the function:
GETPIVOTDATA(Data_field, Pivot_location, [field1, item1, field2, item2], … )
Which turns into:
- ‘Data_field’ = Name of the field whose value is desired. In this case, “Units Sold”.
- ‘Pivot_location’ = Cell reference to the top left cell of the Pivot Table. In this case, an absolute reference to A3.
- The fields and items are extra identifiers to tell where the cell is located.
You can edit a ‘GETPIVOTDATA’ function but don’t try to create it from scratch yourself. Let Excel do all the work and just click the cell within the Pivot Table when you need it.
Note that ‘GETPIVOTDATA’ only returns data that is visible.
Try to remove a field from the field list. If that field removes the cell entirely, the function returns an error.
Put the field back.
The function recognizes the reference again and returns the result.