MS Excel Advanced Pivot Tables Techniques for Accountants
Having created a simple pivot table, it is important to examine the more advanced options that are available in terms of presenting the data.
Using the Slicer Tool
Having put together a simple pivot table, we can now make user of the Slicer tool. This is found in the ‘Filters’ group of the ‘Insert’ tab of the Excel ribbon. It offers a fast way to slice and dice your pivot table data.
Select any cell within the pivot table and then click on the ‘Slicer’ icon and a pop-up menu will appear, automatically based on the data in the pivot table.
Tick the boxes that you wish to analyse and then click OK
If you ticked all boxes as in the example above, then your screen will look like this
You can drag the individual windows to new positions by holding the cursor on the title of the window and dragging it to its new position
You can now use these windows to filter your data. For example, if you wanted to know how well ‘Apples’ were doing in ‘Store B’, you simply click on ‘Apples’ in the ‘Products’ window and click on ‘Store B’ in the ‘Store window
Note that only your selected values are shown as black text on a blue background – the others are greyed out.
You can multiselect by clicking on the multiselect button in each window. If you wanted to show both ‘Apples’ and ‘Bananas’ then click on multiselect and the windows will be adjusted
Note that as you use the filters within the Slicer windows, the pivot table itself automatically adjusts to reflect the filters chosen
Also, note that where a number of Slicer windows have been selected, as in this example, the data does not line up within each window. For example, in the ‘Product’ window, the first product is ‘Apples’. However, in the ‘Value’ window, the first line refers to ‘Bananas’
You can remove the Slicer windows by clicking on the window in turn and pressing the ‘Delete’ key
Inserting a Calculated Field or Item
Going back to the original example, you may wish to calculate a 3% bonus where the quantity exceeds 500. You can do this by inserting a calculated field.
Click anywhere on the pivot table and then click on the ‘Fields, Items, & Sets’ drop down in the ‘Calculation’ tab of the ‘Analyze’ group of the ribbon. Note that this group will only appear when a pivot table is selected.
Click on ‘Calculated Field’ and a pop-up menu will appear
The default name is ‘Field1’ – this can be edited to your own requirements e.g. ‘Bonus’
Enter the formula for the bonus based on ‘Quantity’:
Click on ‘Add’. Click on the ‘Name’ dropdown and delete any calculated fields that may be under ‘Field1’ as they will appear in your pivot table
Click on ‘OK’ and your new calculated field will appear in your pivot table
You can modify the calculation by clicking on the ‘Name’ drop down to find your calculated field heading and then editing the formula. Click on ‘Modify’ and then click on ‘OK’ for this to change in the pivot table.
You can also use a Calculated Item to give a restricted total in the total columns of the spreadsheet.
Suppose that you wanted a total of only ‘Apples’ and ‘Bananas’. You can add this by clicking on the ‘Calculated Item’ item in the ‘Fields, Items, & Sets’ drop down
This will produce a pop-up menu
Enter a name and a formula by clicking on the ‘Items’ window
Click on ‘Add’ and then click on ‘OK’
This will provide a total column specifically for ‘Apples & Bananas’
Creating Multiple Pivot Tables from one Pivot Table
You may wish to replicate your pivot table on other worksheets, so that it can be referred to easily without having to go to another worksheet. Users will also be able to portray the data in a different format.
All you need do is to select the source pivot table by selecting the entire pivot table range. Right click on the selected range and click on ‘Copy’, or instead of right clicking you can use CTRL+C
Go to your new sheet and select the destination cell for the copied pivot table. Use CTRL+V and this will copy the entire pivot table into the new destination. Refresh the pivot table by right clicking in any cell in the pivot table and clicking on ‘Refresh’. This will ensure that column widths are re-established.
Do not forget that if the source data changes, the original pivot table and every copy of it will need to be refreshed. You can do this by clicking on ‘Refresh All’ on the ‘Refresh’ drop down in the ‘Data’ group of ‘Pivottable Tools – Analyze’ on the ribbon.
Deleting a Pivot Table
Sometimes, a pivot table may become obsolete in a spreadsheet, and you will need to delete it. To do this, drag the cursor across the entire pivot table range and then press the Delete key (del)
Grand Totalling and Sub Totalling
You can change how the totals are shown on a pivot table very easily. Click anywhere on your pivot table and you will see an extra set of tabs called ‘Pivottable Tools’ appear on the ribbon. Select the group ‘Analyze’ and in the ‘Pivot Table’ group select the ‘Options’ drop down
Select ‘Options’ in the drop down and a pop-up menu will appear.Select ‘Totals & Filters’ and you will see a tab with tick boxes for the ‘Grand Totals’.You can hide or show the ‘Grand Totals’
Sub totals are dealt with on the ‘Field List’ menu
To see this, right click anywhere on the pivot table, and then click on ‘Show Field List’ in the pop-up menu.
Click on ‘Store’ in the ‘Rows’ window, as this is where the totals are showing. Click on ‘Field Settings in the pop-up menu and a further pop-up window will appear
This will give options for showing or hiding the sub totals. Bear in mind that you can add or remove sub totals for any of the row or column headings.
If you find this article is helpful and you want to help others too, just share it in any social media (such as Facebook, LinkedIn).
Got it Pass Team is happy to work with Richard in presenting this article.
About Richard Shepherd-
Richard is an expert in MS Excel and Access with extensive experience of developing MI reporting systems, databases and dashboards using VBA or RAD for major companies such as Santander, Nationwide Building Society and Zurich Insurance. He is also the author of Access 2010 VBA Macro Programming and Excel VBA Macro Programming. He was graduated in University of the Thames Valley, United Kingdom.
Note: Some of the links in this page contain affiliate links. I will earn a small commission for any purchases you make, though there is no extra cost to you.
The products or services I recommend only I use them myself or after my investigation and truly believe they are useful to you. Don’t forget there are many other options out there – these are simply my preferred choices.