Extract Data to Separate Sheets the Right Way!

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, salesperson, product, etc. And when I add to the master sheet, I want it to automatically feed through to the other sheets. Now before we had the filter function, my answer was always don't use a complicated array formula, you should use pivot tables. Now there's a little known tool for pivot tables that will automatically extract the data into separate sheets, which I'll show you in this video, as well as how to use the filter function for those with Microsoft 365 or Excel 2021. We'll start with the pivot table approach and I'll be using this example data containing orders by salesperson. And just note that I have 35 rows of data. I'm just going to start by formatting it in a table. Ctrl T is the keyboard shortcut and I'll click OK. If we look on the table design tab, we can see the table name is table 3. Now the benefit of formatting the data in a table is as you add data to the table, the pivot table or formula will automatically pick it up. Now from here, I'm going to insert a pivot table, clicking summarize with pivot table, put it on a new worksheet and click OK. Let's bring the pivot table fields closer into view. All I need to do is add the columns to the pivot table that I want displayed in my reports. So we want the country, the salesperson and the order amount. And the trick here is that you need to add enough columns to ensure that there are no records that can be aggregated. For example, if you have data that contains the same row labels, the pivot table will sum the values together and you can see it's done that here. If you find it's summing the data, you simply add more columns until you have unique rows. So let's add order date and I'll just Ctrl Z to undo the automatic grouping of the dates. Now I could have multiple orders by the same salesperson on a single date, so I need to add another column and the last one I have is order ID. By default, the pivot table subtotals the data. So I'm going to remove that via the design tab and over here, do not show subtotals. We also want to change the report layout to show in tabular form and I'll also get rid of the grand totals. And while we're here, we'll get rid of the expand and collapse buttons, turn them off. Now if I count the rows, you can see I have 35 rows, which is the same as my source data. So effectively, I have a copy of my source data, except it's in a pivot table. Now remember, I want to extract it to a separate sheet for each salesperson. So I'm going to move the salesperson into the filters area and then on the pivot table analyze tab, under options, I'm going to show report filter pages. I only have one field in my filters area, so I'm going to click okay for salesperson and you can see Excel has automatically inserted a sheet for each salesperson. Let's just expand the columns so we can see we've got Everton, Jarvis and Maxwell. Now if I add some data to my source, we can automatically update these reports as well. So I've got some new data on a sheet here, let's just copy it and on the very next blank row of the table, I'm going to control V to paste it in. Notice that the table has expanded to include that new data and now I have 39 rows. Let's go back to any of the sheets, it doesn't really matter, but just so we can see the effect. If I go to the data tab and then refresh all, you can see the new record's been added to the pivot table. So there you have a super easy and robust way to extract data from a master sheet into separate sheets based on the field that you put in the filters area of the pivot table. I still have my original pivot table here, but I can delete it if it's no longer required. If you have Microsoft 365 or Office 2021, or later if you're watching this video in the future, you can also use the filter function. For example, if I filter table 3, which is the table my data's in, and the criteria is to include the data where the salesperson equals, go back to my formula sheet, salesperson selected in cell C4. If I have empty results, I'm just going to return a blank, so two double quotes, close parentheses, press enter, and there's my data. Now you can either copy this sheet and edit the salesperson in this cell, or in my case, I've linked it to a data validation list, so I can just choose a different name from the list, and I get the results. Now, if you have Microsoft 365, you can improve on this by including the column labels using the VStack function, so let's take a look at that. If I wrap this in VStack, and then go to my source data, and select the headers, so I'm going to stack the headers, that's my first array, with my filter table, close parentheses, and press enter, and you can see now I have the headers. Let's go and make them bold, and we'll give them an underline. It still updates the same. Now I can take it one step further, because I already have the salesperson name in cell C4, so I can exclude the salesperson column from the data, and we can do that using Choose Coles. So let's wrap it in Choose Coles, this is also available in Microsoft 365 only at the moment. So I'm going to Choose Coles, and VStack the data, and then the columns that I want returned are only the country, the order date, order ID, and order amount. I can rearrange the order of the columns here as well, so instead of the country first, I'm going to get the order date, so that's column three, then I'll get the order ID, which is column four, then I'll get the country, and finally the order amount, which is column five, and I'm skipping the salesperson column altogether. Close parentheses on Choose Coles, and now we have a more succinct table. Let's change the formatting here, so this one should be Date Format, and this one should just be a number, so I'm just going to set it General, and I don't need the formatting there, so let's clear that, and like before, if we change the selection, it updates, and if I add data to the table, let's do this again. I'm just going to copy in some new data, so notice this data is for March 3rd. If I go back, I just need to update the formatting in these cells, make that a short date, you can see March 3rd is now present, and I can select a different salesperson, and their date is there as well. As you add new data to the table, it's automatically picked up by the filter function. So which is the best option, the pivot table or the filter function? Well, if you don't have filter, then by far the best option is to use pivot tables to extract data to separate sheets, rather than some complicated array formula. However, if you have the filter function, then I'd say there are pros and cons. The pros of using pivot tables to extract data is they're easy to build and update, and they're not easily broken. The cons are that you have to click the refresh button to get updates from the source table, versus the filter function pros are that it updates automatically, and the cons are, well, it's a formula, so it's more easily broken, especially if the user doesn't know what's going on with this formula, and it's a bit more complex to write, so this formula might be a bit overwhelming to some users. I hope you found this tutorial useful. You can download the excel file for this lesson from the link here, and if you like this video, please give it a thumbs up and subscribe to our channel for more, and why not share it with your friends who might also find it useful. Thanks for watching.