I’ve had a few people ask me recently:
“What formula can I use to extract a list or subset of data from a master sheet into separate sheets for each division/sales person/product etc., and when I add to the master sheet I want it to automatically feed through to the other sheets?”
My answer is always; Pivot Tables!
What, that’s not a formula, and Pivot Tables summarise data, don’t they? I want all of the data listed in each sheet not a summary.
I hear you :), don’t worry, they can do it*. You can use a formula if you want but it’s much better to use Pivot Tables, for these reasons:
- They’re more efficient, but you must make sure you use the same Pivot Cache (I’ll tell you how in a minute).
- They’re easier to set up and use than the equivalent formula.
- If you link the Pivot Table to an Excel Table any new data automatically gets incorporated in the extracts.
Let me show you how. Here is my source data. It’s a list of sales by salesperson.
And I want the data for each salesperson on a separate sheet.
Tip: see the nice formatting of my source data? That is because I have inserted an Excel Table.
I’ve done this for a few reasons:
- When you insert a Table, Excel gives the table a name and structured references (you can see the Table name in the name box in the top left of the image above; Table1).
- When I insert my Pivot Table Excel will use the Table name as the source range.
- Then when I add new sales data to the bottom of the table it will automatically be included in my Table range. This means I don’t have to edit the data source of my Pivot Table to include the new rows of data. I can just click the refresh button and job done.
- If you don’t like the formatting simply select the Excel Table and from the Design tab select the Table Style ‘none’. It’s usually the first one in the list.
Structured references work in the same way as a dynamic named range except you don’t have to do all the work to set them up.
Right, back to the task.
Inserting a Pivot Table
- Select any cell inside your source data.
- On the Insert tab of the Ribbon select PivotTable:
- Excel will automatically detect the range of cells and put it in your Table/Range field. Remember mine is called Table1. You can leave the default as New Worksheet and click ok.
- Ok, now you’re ready to build your choose the fields. We’re going to set up the first one for the sales person Buxton.
Since we want every record listed (as opposed to summarising the data) we need to put every field in an area:
- drag the Salesperson field into the Filter area
- drag the Order Amount field into the Values area
- and the remaining fields go into the Rows area like this:
Note: if you don’t have a column/columns with amounts you can just drag all remaining fields into the Rows area.
Right click on it and select PivotTable Options > on the Display tab select ‘Classic PivotTable layout’, and while you’re there un-check ‘Show expand/collapse buttons’ as we don’t need these since every record will be displayed.
Tip 1: If you want you can turn off the Grand Total row. Right click the Pivot Table > PivotTable Options > Totals & Filters tab > uncheck ‘Show grand totals for columns’.
Tip 2: If you’ve got Excel 2010 or later you can repeat the country label down the column. Right click a cell in the Country column > Field Settings > Layout & Print tab > check the ‘Repeat Item labels’. So now it looks like this:
Excel will create a new worksheet for each salesperson in the filter.
I know this was loads of fun and you might be tempted to repeat the above steps to create the reports for the other salespeople but it’s better to either use the technique in step 9, or copy the PivotTable rather than insert a new one.
This is because every time you insert a new PivotTable Excel creates a copy of the source data, called the Pivot Cache, and as a result you run the risk of creating an unnecessarily large, and probably slow Excel file.
Handling Blank Cells
If your source data has blank cells you might end up with the word ‘blank’ in your table like this in the OrderID column below:
This is because each column (except the Total column) is actually a row label and it’s expected that the row labels will have a name, not be blank.
To remove the blanks simply click in one of the cells that contain the word ‘blank’ and enter a space in the cell. Excel will automatically replace all of the blank’s with a space.
The good news is if you later enter data in the source the fields in your table will still bring this data in when you refresh the pivot table.
*Note: This method assumes every row in your source data is unique. If there are any duplicates the PivotTable will summarise it.
How about automatically refreshing your PivotTables when you add new data. Here's a cool Macro you can use.
Or, if you’re new to Pivot Tables check out this tutorial which will take you through the steps.
Thanks to those who asked this question recently and a special thanks to Theresa Diers and Nicoline Botha who gave me the tip in step 9.
If you liked this tip or know someone who would please use the sharing buttons below to spread the word on LinkedIn, Google+1, Facebook and Twitter.