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