Excel Pivot Tables Basics for Accountants
Pivot tables in Excel are an extremely useful tool in analysing large quantities of tabular data. A pivot table will allow you to slice and dice the data, present it in different ways e.g. using different columns, and to format it to how you wish to present the data
You can use filters, and other users can also manipulate the data (provided that the worksheet is not protected)
You can include statistical formulas such as average, mean, etc.
The whole pivot table is interactive and can be easily changed to show the data.
How does it work and how to create a pivot table from a single worksheet?
To start a pivot table, you need some tabulated data in your worksheet. Here is a simple example:
As it is presented, it is not easy to get the unique details for a particular product or a specific store.
This data can be turned into a pivot table very easily. Click on the ‘Pivot Table’ icon in the ‘Tables’ group of the ‘Insert’ tab on the ribbon and a pop-up form will appear.
Excel will unusually automatically detect the range to be used, but if the worksheet is complex then you may have to select this yourself
In the example below, the data range is A1:E9 and the pivot table will be constructed at cell K1. This can be edited as required.
Click OK and a Pivot Table Fields pane will appear on the right-hand side of the worksheet which allows you to choose how you want to see your data.
The one rule for a pivot table is that it must have at least one row heading, one column heading, and one value field.
Supposing you wish to see the value sold by Product and by Store. You drag Product into the Columns box, Store into the Rows box and Value into the Value box in the Pivot Table Fields Pane.
As you do this you can see the pivot table being constructed at cell K1
Close the Pivot Table Fields pane by clicking on the X in the top right-hand corner, and you have a pivot table
This gives an overall summary of your data based on Product, Store, and Value sold.
Note that the row and column labels allow drop downs for filtering as required. Also, note that the Excel ribbon now has additional tabs for Pivot Table Tools. These extra tabs are only visible when you select the pivot table.
Next step is that a user wants to see the prices and quantities as well. Normally, this would mean a lot of re-working in the worksheet, but it is very easy with a pivot table
Either right -click on the pivot table and select ‘Show Field List’ or click on the ‘Field List’ icon in the ‘Show’ group of the ‘Pivot Table Tools’ ribbon
Drag the additional fields of quantity and price into the value box on the Pivot Table Fields pane, and this will instantly change the pivot table.
The order that fields are shown in can be altered in the Pivot Table Fields pane by simply dragging the field names within the row, column, or value box. For example, when price and quantity were added in the value box, they came below quantity, in the next available position.
Just drag the names upwards within the value box to change the order.
The column headings in this example tend to look a bit messy. You have headings, such as ‘Sum of Price’, whereas you would probably want it to read as ‘Price’
Right click on the heading that you wish to change and click on ‘Value Field Settings’ in the pop-up menu. This will show a further pop-up menu
This will allow you to enter a custom name for the column. However, if you change the name to ‘Quantity’, you will get an error message when you click OK saying that the name already exists. This is because it is also the Source Name and cannot be used a second time.
The way to get around this is to put a trailing space after ‘Quantity’ so that it looks like ‘Quantity ‘in the pivot table. The trailing space will not be visible, but it satisfies Excel that it is a different name.
The Value Field Settings pop-up will also allow you to change how you want to see the numbers e.g sum, average, count, etc. You can also change the number format by clicking on the ‘Number Format’ button, which will give you access to all the standard Excel number formats
The ‘Show Value As’ tab on this pop-up will allow you to show the values as percentages.
You can use the ‘Filters’ box on the Pivot Field List pane to summarise by specific fields. For example, if you drag ‘Product’ into the ‘Filters’ box a filter will be added above the pivot table which allows you to select a product to summarise by e.g. Apples. You can change the filter by clicking on the ‘Filter’ icon in the Product row
You can expand the detail by right-clicking on a column label e.g. K4 and choosing ‘Expand/Collapse’ in the pop-up menu
An important point with pivot tables is that they do not get automatically refreshed. If your source data changes, this will not be reflected in the pivot table until it is refreshed. You can do this by right-clicking anywhere on the pivot table and clicking on ‘Refresh’ in the pop-up menu
A very useful feature of a pivot table is the ability to drill down on a number to see the data behind it. Users will often challenge a number, so it is useful to provide an audit trail of how the number was calculated.
To do this in the example shown above, change the filter to ‘All’ and then double click on a numeric cell e.g. N5. A new worksheet will be inserted with a table showing the detail on that number
If you click on the ‘Grand Total’ numbers you will get everything on the new tab
Do not forget that if you want to delete the new tab, right click on the tab name at the bottom of the Excel window and click on ‘Delete’. Do not click on the X in the top right-hand corner of the worksheet window as this will close the entire workbook. This is an easy mistake to make!
This a useful Excel formula that allows you to interrogate a pivot table using specified criteria, and place the resulting value in a cell
In the expanded example above, you may want to have a cell with the quantity for ‘Oranges’ in it so that it is separate from the pivot table.
The formula for this is:
=GETPIVOTDATA ("Quantity”, K3,"Product","Oranges")
This will give the result of 35
The parameters are:
Data Field – Field to return the number from (Quantity)
Pivot Table – First cell in pivot table (K3)
Criteria Field – Field to be used for interrogation (Product)
Criteria Value – Value to be searched for within criteria filed (Oranges)
Multiple pairs of criteria can be added if required.
The data being searched for must be visible within the pivot table otherwise a #REF error will occur.
Creating a pivot table from multiple worksheets
You can consolidate data into a pivot table by using ranges from the same worksheet or another tab or linking to a range in another workbook.
The data must be in tabular from and have the same column headings
To consolidate the source data, you need to open the Pivot Table Wizard by pressing ALT+D then press P. This will display a wizard.
Click on ‘Multiple consolidation ranges’ and click ‘Next’
The next step is to define page fields. Select ‘I will create the page fields’ and click ‘Next’
Click Next again, and you will see a form that allows you to select your ranges to be consolidated
Select each range in the range box, and click ‘Add’ after each one has been selected
Click on ‘Next’ which takes you to the step to define where you want your pivot table to appear.
Click on ‘Finish’ to complete your consolidated pivot table
Note that the wizard tends to use ‘Count of Value’ rather than ‘Sum of Value’. This can be changed by right clicking on ‘Count of Value’ in the Pivot Table Field pane, and selecting ‘Value Field Settings’ from the pop-up menu
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.