If you’ve got a long list of sales orders on an Excel worksheet, how can you send one sales rep’s orders to a different sheet? There are lots of creative ways to do that, but my favourite solution is to use an Excel Advanced Filter. Would you use that solution too, or something different?
Advanced Filter Button
Advanced filters are one of Excel’s most under-rated and under-used features, and we can blame the Excel Ribbon for that.
Here’s the proof. If you didn’t already know how awesome the Excel Advanced Filter tool is, would you click that “Advanced” button on the Ribbon’s Data tab, in the Sort & Filter group?
“Ooooh – Options for filtering using complex criteria! Let me try that!”
Said no one, ever.
Advanced Filter Dialog Box
For the adventurous few Excel users who DO click that Advanced button, here’s the hidden “treasure” that they discover – a tiny, confusing, old-fashioned dialog box.
Most people will click the X at the top right corner, and never go back there again!
Why Bother With Advanced Filters?
Since Advanced Filters look so confusing and complex, why should you bother learning how to use them?
The best thing about Advanced Filters is that they can:
- magically send data to a different worksheet
- based on simple or complex criteria
- without using macros!
Advanced Filter Setting to Send Data
Here’s an example of the filter setting you’d enter, to send Excel data to another sheet, if the customer name is MegaMart
With the settings in the screen shot below, the filter will:
- Send a copy of the data
- from Table1
- to the sheet named Filtered Data, starting in cell A1
- based on the criteria in cell F1:F2
In the criteria range:
- Cell F1 has a column heading from Table1 header row – Customer
- Cell F2 has a customer name – MegaMart
Tip: You can use a wildcard character, like an asterisk or question mark, in the criteria, for flexibility. Or, use an equal sign formula, when you need an exact match. See more tips and examples on the Advanced Filter Criteria page.
List of Unique Records
The second-best thing about Advanced Filters is that they can create a list of unique records from a dataset, using one column or multiple columns.
Here’s an example of the filter setting you’d enter, to send a unique list of customer names to a different location
With the settings in the screen shot below, the filter will:
- Send a copy of the data
- from the Customer column of Table 1
- to the sheet named Filtered Data, starting in cell A1
- with NO criteria
- sending unique records only
Advanced Filter Macros
Advanced filters work well with macros too, and they’re lightning fast! There are sample files with VBA code on my Contextures site that you can test, to see how speedy Advanced Filters are when you need to filter a data set.
One sample file – Send List to Sheet or File (Specific Settings) – has buttons on the worksheet, so it’s easy for you to test the macros.
Flexible Filter Macro
Here’s a screen shot from the Send List to Sheets (Flexible) sample file. When you run the macro in this workbook, it prompts you to enter a column name. You could choose a different column each time, to create whatever data extracts you need.
I typed “region”, and the macro ran two Advanced Filters:
- Created a unique list of regions
- Sent each region’s data to a new sheet, named for that region
Try Advanced Filters
If you haven’t tried Advanced Filters yet, I hope you’ll give them a chance! There are basic instructions and a video on my Advanced Filters Intro page.
If you’re ready to try the macros, get the sample files on the Advanced Filter Macros page, and see how quick and efficient Advanced Filters are at extracting data.
Video: Send Data to Different Sheet
With an advanced filter, you can extract data to a different sheet. Watch this video to see the steps.
There are written steps on the Send Excel Data to Another Sheet page, and sample files to download. The full transcript is below the video.
More Excel Filter Examples
Send Excel Data to Another Sheet
Advanced Filter Criteria Slicers
_________________
Video Transcript
In Excel, you can use an Advanced Filter to pull data from a table and put it somewhere else in the workbook, either on the same sheet or a different sheet.
In this example, we have a list of orders and we would like to pull the orders with the highest totals and put them on a different sheet in the workbook. I would like to get the date and the customer for each of the high priced orders.
Criteria Area
Back on the Orders sheet, I’ve started a criteria area here. I’m using total, which is the column where the order amount is in the main table. That’s the criteria heading, and below that I’m going to enter my criterion, which is greater than 1500.
I only want orders where the total is greater than 1500. I’m not putting dollar signs in here. It’s just the number that I wanted to check.
We want to pull orders onto a different worksheet. In this case, we have to start on this destination worksheet, and I want to stay away from the cells near the heading. So, I’m just going to click a cell somewhere in an empty area of the worksheet.
Start Filter
To start the filter on the ribbon, I’ll go to the Data tab and click Advanced. In the Advanced Filter dialog, I’m going to select Copy to Another Location.
The List range is my main table. So, I’ll click here, then go to the Orders sheet and select the entire table, including the headings. That’s entered as the list range.
My Criteria range, I’ll click in that cell. Go back to the Orders sheet where I created my criteria range and select the heading cell and the criteria cell below that.
For CopyTo, this is where I want the data to end up. I’ll click here and select my two heading cells on the TopOrders sheet and I’ll click OK, and there are the 3 orders where the total is higher than 1500.
If we look back, we can see 1, 2, 3 orders, and those have been copied automatically to the other worksheet using an Advanced Filter.
_______________________________
Send Data to Different Sheets in Excel Based on Criteria
_______________________________