Excel Filter Data to Different Sheet

In Excel, pull data from another sheet, based on criteria, with an Advanced Filter. Watch this video to see the steps, and the step-by-step written instructions are below the video.

Video Timeline

0:00 Introduction
0:11 Sales Orders
0:35 Top Orders
1:00 Extract & Criteria
1:44 Advanced Filter
3:44 Check the Data
4:16 Warnings
4:52 Get the Excel File

Video Transcript

If you'd like to read the video transcript, click on the green check box below. When you're finished, you can click the check box again, to hide the transcript

✅ Click here for Transcript

Video Transcript

In this video, you'll see how to take data from one worksheet in Excel and send it to a different sheet, without using any macros.

This is Debra Dalgleish from Contextures.com.

Sales Orders

On this worksheet, there's a named Excel table and it has sales orders.

I would like to send the top orders where the total was over a certain amount onto a different sheet.
I don't want to include all the columns in the data that gets sent, just the order number, the cost and the total amount.

To do that, I'm going to use an advanced filter.

Top Orders

This is the top order sheet where I want the data to end up.

To tell Excel what I want to get from the other table

I'm going to put some headings up at the top, and this will be the extract area.
I'm going to create a criteria range in column E to say which column I want to check, and what setting I want to check for.

Extract & Criteria

When we set up an extract area, the headings have to be exactly the same as are in this source data, so I'll select three headings that I want (order, cost, and total) and copy those with Ctrl+C.

Then I'll go to top orders and just paste the values there.
I could make those bold and I want to check the total column. So I'll copy this and paste it here.
The heading here in most cases for our criteria has to be an exact match as well.
I want to check for orders where the total is greater than 200, so I'll type a greater than symbol, and 200.

Advanced Filter

The next step is to run the advanced filter.

We want the data on a different sheet from the original data, so we're not going to start here, we're going to start on the destination sheet.

We don't want to have an active cell anywhere near the other data on the sheet or Excel will assume that's our source data.

So I'm going to click away from my headings and from the criteria.
In this cell, I'll go up to the data tab on the ribbon, click Advanced and the Advanced Filter dialogue box opens.
Our action: we don't want to filter in place, we want to copy to another location.
The list range is our source data, so I've clicked in there.
Then I'll go to the food sales sheet.

This is a table, so to select it, I'm going to click up at the top left and you can see a diagonal arrow there

.I'll click once and you'll see that just the data has those little marching ants around it.
I'll click again, and now the headings have been included.
Sales_Data is the name of the table, and ''All' tells me that everything is selected.
The criteria range, if I go to my other sheet.

If you've done a filter before you might have settings left over in here

So I'll clear that out.
The criteria range is this heading cell E1 and the number the criterion in E2.
Copy to, clear that out. I want it to copy to this extract range.
So those three cells click, OK

And we get any order where we've got a total greater than 200.

Check the Data

If we go back and look at the data, there's a bit of formatting in this table, and this column has conditional formatting for any total that's over $300.

So we'll look at the results from the advanced filter and see what it kept.

It kept the cell formatting.
This cell was manually colored yellow, and it kept this number formatting.
It did not keep any of the conditional formatting.

Warnings

Also if we go back to food sales, this column had a formula, but in the results that were filtered, everything's a value.

So it's not going to keep your formulas for you.

Another thing to keep in mind is that this is just a copy of the original data.

If you make a change here, it does not affect the original data.

And if you make a change here, it does not affect the results of this filter.

If you want to update this list, you would have to run the advanced filter again.

Excel Data: Sales Orders

In this Advanced Filter example, there is a named Excel table, with food product sales orders.

There are 100 rows of data
There are 6 columns: Order; Region; Category; Qty; Cost; Total
Total column has a formula to multiply Qty by Cost: =[@Qty]*[@Cost]

Note: For instructions on setting up a table, go to the Excel Named Tables page.

named Excel table with food sales orders

Set up Extract Range

With an Advanced Filter, you can extract all columns from the data table, or you can specify one or more of columns that you want to extract.

To set up an extract range for this Advanced Filter, follow the steps below:

Go to the TopOrders sheet, which is the destination sheet for the extracted data
Type the following headings, which are an exact match for 3 column headings in the data:
- A1: Order
- B1: Cost
- C1: Total

Note: For more examples of setting up an Extract Range, go to the Advanced Filter Basics page.

Set up Criteria Range

To extract data based on one or more criteria, you need to set up a criteria range in the Excel workbook. In this example, the criteria range is set up on the destination sheet for the extracted data, TopOrders.

A Criteria range has one or more heading cells, and one or more rows with criteia.

To set up a criteria range for this Advanced Filter, follow the steps below:

Go to the TopOrders sheet, which is the destination sheet for the extracted data
In cell E1, type the heading, Total, which are an exact match for a column headings in the data
In cell E2, type criteria to find orders where total is greater than 200: >200

Note: For more examples of setting up a Criteria Range, go to the Advanced Filter Basics page.

extract range and criteria range

Run the Advanced Filter

Follow these steps to apply the Advanced Filter, and send the data to a different sheet

Go to the TopOrders sheet, where you want the extracted data to be sent.
- It is important to start from the destination sheet, when you want the filter's extracted data on a different sheet.
Select a cell in an unused part of the sheet (cell B5 in this example).
On the Excel Ribbon's Data tab, click Advanced, to open the Advanced Filter dialog box

Criteria Range

Advanced Filter Dialog Box

When the Advanced Filter dialog box opens, follow the steps below, to send data to a different worksheet, based on your criteria.

Here is the Advanced Filter dialog box, with the settings used for this example. The settings are explained below

Advanced Filter dialog box

Action

First, in the Action section , click the radio button for Copy to another location.

⚠️ Warning: If you use the Copy to another location option:

All cells below the extract range will be cleared when the Advanced Filter is applied
Be sure there isn't any data below the extract range that you want to keep

List Range

Next, tell Excel where to find the data that you want to filter

Click in the List Range box
Go to the FoodSales sheet where your data is
Select the Excel table, including the heading row.
- Tip: Click twice at top left corner of the first heading cell

After you select the table, the ListRange box should show the table name, followed by [#All], like the example in the screen shot below: Sales_Data[#All]

click twice at top left corner for table's first heading cell

Criteria Range

Next, tell Excel where to find the criteria to use for filtering the data.

In the Advanced Filter dialog box, click in the Criteria range box.
Excel should automatically activate the TopOrders sheet, where you started the Advanced Filter
- If not, click on the TopOrders sheet to activate it
On the TopOrders sheet, select the criteria range -- cells E1 and E2

The Criteria range box should show the sheet name and cell address range.

Note: If you ran the Advanced Filter previously, using this criteria range, the Criteria range box might show the sheet name and the range name, Criteria

click twice at top left corner for table's first heading cell

Copy To

Next, tell Excel where to put the data that the filter extracts. These are the starting cells for the extract, and the data will fill down the column, as far as needed.

In the Advanced Filter dialog box, click in the Copy to box.
Excel should automatically activate the TopOrders sheet, where you started the Advanced Filter
- If not, click on the TopOrders sheet to activate it
On the TopOrders sheet, select the extract range -- cells A1:C1

The Copy to box should show the sheet name and cell address range.

Note: If you ran the Advanced Filter previously, using this Extract range, the Copy To box might show the sheet name and the range name, Extract

Unique Records Only

If there are duplicate records in your Excel data, and you only need to see one copy of multiple records, you can turn on the option to extract unique records only.

(optional) Check the box for Unique Records Only
- Do not use this setting for this TopOrders filter

OK Button

Click the OK button, to apply the Advanced Filter, and to close the dialog box

Review Extracted Data

After you click the OK button, data that meets the criteria is extracted onto the TopOrders sheet. The screen shot below shows the results from this example:

There are 18 records in the data extract
All records have a total of $200 or greater
Cell formatting is copied with the extracted data
- Conditional formatting is NOT copied
Formulas are not copied -- Total column has values, instead of formulas

In the results shown below

Bold format is coied from the source table (A2, A3)
Fill colour is copied from the source table (A18)
Currency format is copied from the source table (C2)

Advanced Filter extracted data on TopOrders sheet

Static Results With Advanced Filter

After you run an Advanced Filter, the extracted data is static, and no longer connected to the source data table.

If you make changes to the source data, the Advanced Filter results do NOT update automatically
If you make changes to the Advanced Filter results, the source data records are NOT affected
To extract the current data, based on your criteria, run the same Advanced Filter again, either manually, or with a macro.

Excel FILTER Function

Instead of using Advanced Filters, you can use the Excel FILTER function in Excel 365, or other versions that support dynamic spill functions.

Unlike the static results from an Advanced Filter, the FILTER function results are dynamic.
If the data changes, the FILTER function results will update automatically, to show the current data.

Excel FILTER function results are dynamic

Tip: See more dynamic array formulas on the Spill Function Examples page

Advanced Filter Macros

Advanced filters work well with macros, which will help you save time, for filters that you need to run frequently. For example, use Advanced Filter macros for your weekly reports, instead of repeating the series of steps manually every week.

To get started, go to the Advanced Filter Macros page. That page shows you:

how to record a macro while you run an Advanced Filter
make simple changes to the macro's VBA code
run that macro later, to quickly apply an Advanced Filter

Get the Sample Workbook

Download zipped Excel Filter Data to Different Sheet workbook with sample data and criteria.

Excel Filter Data to Different Sheet

Video Transcript

✅ Click here for Transcript

Video Transcript

Sales Orders

Top Orders

Extract & Criteria

Advanced Filter

Check the Data

Warnings

Excel Data: Sales Orders

Set up Extract Range

Set up Criteria Range

Run the Advanced Filter

Advanced Filter Dialog Box

Action

List Range

Criteria Range

Copy To

Unique Records Only

OK Button

Review Extracted Data

Static Results With Advanced Filter

Excel FILTER Function

Advanced Filter Macros

Get the Sample Workbook

More Tutorials