Pivot Tables
Introduction to pivot tables
Pivot tables can be used for many things however their best function is served by collating data for you. This is easier to create when the data is presented in log format. What we mean by this is that you should log each transaction individually and not auto-sum or total anything as it will be done by the Pivot table for us.
an example of a log entry with sales data could be:
| Date | Sales Person | Product | Price |
|---|---|---|---|
| 13/12/2017 | Dan | table | £110 |
| 14/12/2017 | Helen | Chair | £50 |
this can continue for as many log entries you have. To reiterate you don't need to create =SUM totals anywhere or collate the data before you start a pivot table.
There is also an exercise file contain some sample data for you to test with: Click here for the file
Selecting the data for your Pivot table
Once you have your data you can add it into a pivot table in two ways:
- Click a cell within your data and then go to "insert" --> "Pivot Table" - (this method will attempt to guess the range of the data, but may miss some)
- Click and drag around all the data you wish to be included in the pivot table then go to "insert" --> "pivot table"
this will then bring up a dialogue box which you can confirm the selected data range (which has been input for us) and choose where you would like to place the pivot table:
As you can see in the above image tile, if you select your data prior to inserting a pivot table it will automatically input the table/range in for you. You can of course amend it here too afterwards. The menu also asks where you'd like to input the pivot table, which generally I recommend placing it on it's own "new worksheet", so that it doesn't affect anything else.
Building your Pivot Table
When you have select a location for your pivot table you will see placeholder report area (which will show the data as you build the pivot table):
when you click into this area you will open the "field list" on the right hand side. The field list is how we can structure to suit our needs. This is where sometimes a little trial and error is needed to get you the required information:
Drag fields
When the PivotTable Fields pane appears on the right. To get the totals and relevant data of each product/category, drag the fields to the selected areas.
1. Drag a field such as "Product" or "sales person" to the Row column: (This will create the products found in the range as singular items on the rows of your pivot table):
2. We can then add "Sales Person" or "Region" to the column to see a better breakdown of the information:
however as you can see there is no information in the cell matrix between the list of both fields, this means we need to add one final field to our pivot table.
3. To add the missing information we need to add "Price" in the values field in the pivot table:
at this point you may decide your pivot table is breaking down the data enough for you to make the decisions you need to. However we can expand upon this as we still have no regional breakdown in this current pivot table.
4. If we wanted to show a regional breakdown we could add that to the Row, column or filter category as to your preference:
here you can see I've added "region" to the rows field (highlighted in red). It also allows for collapsible rows or columns for fields you have added a second field.
ultimately this pivot table looks like this in the field list:
