Creating an Excel Pivot Table
This article is based on Excel 2019. It is still applicable to earlier versions of Excel, but there may be some differences in menus and options
Creating an Excel Pivot Table is a powerful way of being able to transform and analyze tabular data in a spreadsheet. It will allow the user to present the data in any format, using different headings as rows and columns in the pivot table, and will also allow the user to drill down on specific numbers to see the detail behind.
The pivot table is very easy to edit to show different views on the same data
There is also a very useful Excel formula called GETPIVOTDATA that allows specific numbers to be returned based on user criteria. It is very similar to creating a query on a database table, except that you do not need any knowledge of how to write a query. This is extremely useful if you wish to incorporate specific pivot table results into a dashboard within the workbook.
Pivot tables need tabular data as the data source. Here is some sample data:
This is a simple table of people who have various computer hardware and software
In this example, there is a numeric field of quantity, but you do not have to have a numeric field for the value field in the pivot table. Instead, you could have the value field as Count of Product, which would the number of identical products against each person
Creating an Initial Pivot Table
On the Excel ribbon, click on Insert and then click on “Pivot Table”. A pop-up window will appear which will allow you to choose a range of data as the source and a location in the workbook
You choose a table of data by entering either a range name or data coordinates into the first input box on the pop-up. You can select a range using the mouse by clicking on the up arrow on the right-hand side of the text box.
You can choose where you wish to see your pivot table located in the second text box. There is an option to place it on a new worksheet or the existing worksheet
Click OK and an initial pivot table will be created, and a pane will show on the right-hand side of the screen which will allow you to design how you wish to see your data
To create a simple pivot table, you will need at least 1-row heading, 1 column heading, and a value
To set these headings, you simply drag and drop the field names into the appropriate box. For example, you may want Name to be a row heading, in which case you click on Name in the field list and drag it to the Rows box.
A tick will appear against each field name as it is dragged into the appropriate box. Your pivot table should now look like this, depending on how the rows and column headings have been arranged.
If you want to remove a field name, simply untick the box against the field name list at the top of the pane.
You can also change the row and column headings by dragging and dropping the field names. Here is a different pivot view based on the same data:
Product is now a row heading and category is now a column heading
Formatting the Pivot Table
The pivot table initially uses default settings for items such as number format, headings, and where totals appear. All of these can be changed very easily.
Each field name in the design pane has a Down Arrow next to it. Click on this for the value field and you will see a pop-up menu.
Click on Value Field Settings and you will see a further pop-up window that will allow you to format the numbers and make other changes.
For example, you may not like the heading of ‘Sum of Quantity’. You can enter a new heading on this window. The one restriction is that you cannot change it to an existing field name e.g. Quantity, so you would need to call it something like ‘Quantity Total’
Number Format allows the use of all the standard Excel numeric formats, and you are not restricted to just using Sum. You can use averages, counts, maximum, and minimum, etc.
On the ‘Show Values As’ tab there is a huge number of options for calculations
If you look at the Field Settings on a row or column heading, you will see options for ‘Layout & Print’ which allows further formatting options
When you click on a pivot table, an extra ribbon appears called PivotTable Tools. The Design tab gives access to many different styles and formats
Filtering Data on the Pivot Table
The pivot table provides a number of ways of filtering data so that you only see what you choose to see
The pivot table allows filtering on the first row or column label simply by clicking the dropdown.
This allows you to select specific names and your pivot table will shrink to only show that name.
However, suppose you want to filter by the second-row label (Product)? This can be achieved by adding the second-row label into the filter box in the design pane.
This keeps the filter for ‘Name’ on Row Labels, but now allows you to filter by ‘Product’ as well
Drill Down Facility
Excel pivot tables have a very powerful drill-down facility which will give you an audit trail on any number. Final users of data that is summarised like this will frequently challenge numbers, particularly if they are not what is expected, and fortunately, there is a simple way to provide the explanation behind the numbers
Simply double-click on any number within the pivot table and the full data behind that number will be shown on a new tab.
Double-clicking on the Grand Total numbers will provide everything
This is a very useful formula to interrogate your pivot table to produce values within cells
An example is:
=GETPIVOTDATA(“Quantity”,G3,”Product”,”Laptop”,”Name”,”John Smith” )
In this example, the numeric field to be returned is called ‘Quantity’ and the first cell in the pivot table is G3. You can then enter pairs of criteria e.g. Product and Laptop where Product=Laptop and Name=John Smith
This will produce a value of 2 based on this pivot table (John Smith has 2 laptops):
The disadvantage is that it can only pull back one value.
If you tried using =GETPIVOTDATA(“Quantity”,G3,”Product”,”Windows 10″) then you would get a #REF! error because there is more than one number for the product ‘Windows 10’. If you remove ‘Name’ from the pivot table so that it summates products, you will get the correct result of 3
You would also get this error if you entered a product that does not exist in the pivot table
Fortunately, you can use the IFERROR formula to stop this happening
=IFERROR(GETPIVOTDATA(“Quantity”,G3,”Product”,”Windows 7”),”Not found”)
Please try all these tips out using your own data. The best way to learn pivot tables is to experiment around and see what you can produce. Don’t forget that there is always the Undo function if you get stuck and things do not happen as intended