I regularly get asked:
“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?”
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.
- They’re easier to set up and use than the equivalent formula./li>
- If you link the Pivot Table to an Excel Table any new data automatically gets incorporated in the extracts.
*Note: This method assumes every row in your source data is unique. If there are any duplicates the PivotTable will summarise it.
Watch the Video
Download the Example Workbook
Enter your email address below to download the sample workbook.
Let me show you how to extract data using PivotTables. 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.
- 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:
- Ok, so far so good. Let’s tidy it up now:
- Lastly, select the salesperson from the Filter at the top and now you’ve got your extract of the records for one salesperson:
- Now to repeat the process for the other sales people, select the PivotTable then on the Ribbon tab PivotTable Analyze: Options select Options > Show Report Filter Pages....
Since we want every record listed (as opposed to summarising the data) we need to put every field in an area:
Note: if you don’t have a column/columns with amounts you can just drag all remaining fields into the Rows area.
Select a cell in the PivotTable > on the Design tab under Report Layout select, ‘Show in Tabular Form’, and under Subtotals select 'Do not show subtotals'.
On the PivotTable Analyze tab deselect ‘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.
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 blanks 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.
Or, if you’re new to Pivot Tables check out this tutorial which will take you through the steps.
Extracting Data with Formulas
Before we had the FILTER function we would use convoluted array formulas that looked like this:
You had to manually copy the formula down to any new rows and if the inexperienced user forgot to enter it with CTRL+SHIFT+ENTER, it broke!
This approach was therefore error prone and fragile, which is why I always recommended using a PivotTable. However, now that we have the FILTER function we can easily extract data to separate sheets, and FILTER will automatically pick up new data as it gets added to the master sheet (assuming it’s formatted in an Excel Table). No need to edit the formula and therefore less chance of it breaking.
Using the example data in Table1 I can write FILTER like this:
Which returns the rows of data for the salesperson ‘Buxton’ selected from the data validation list in cell C4:
If you have Microsoft 365 you can improve on it by including the column labels using the VSTACK function like so:
In the image below I’ve added bold formatting and a bottom cell border for the headers:
As already have the salesperson name in cell C4, we can exclude the salesperson column from the table using the CHOOSECOLS function (Microsoft 365 only):
Best Option for Extracting Data
If you don’t have the FILTER function, then by far the best option is to use PivotTables to extract data to separate sheets. However, if you have the FILTER function then I’d say there are pros and cons:
Pros and cons of using PivotTables to extract data:
- Pros: Easy to build, update and not easily broken.
- Cons: requires clicking on the Refresh button to get updates from the source table
Pros and cons of using the FILTER function to extract data:
- Pros: updates automatically
- Cons: More easily broken than PivotTables, more complex to write