Home > Filters > Advanced > Diff Sheet

Excel Filter Data to Different Sheet

In Microsoft Excel, you can pull data from another sheet, based on criteria, with an Advanced Filter, an Excel macro, or the Excel FILTER function.

extract range and criteria range

1) Advanced Filter to Pull Data

In all versions of Excel, you can use an Advanced Filter to pull data to a different sheet.

Watch this video to see how to send data to a different sheet, based on criteria, by using an Advanced Filter. There are written steps, and the video timeline, 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

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. You can specify a single destination cell in the extract cell range, or multiple cells.

In this example, the extract range will have 3 heading cells, based on columns in the database.

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

  1. Go to the TopOrders sheet, which is the destination sheet for the extracted data
  2. 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:

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

Note: The criteria range can contain an Excel formula, instead of typed criteria. The range can also have multiple rows and columns, to create complex and/or criteria settings. 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 no 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.

2) 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

Video: Get Started With Excel FILTER Function

In this video, see how to use the new FILTER function to create a list of cities in a specific region. Next, remove duplicates with the UNIQUE function, and finally, the use SORT function to put items in alphabetical order.

The written instructions are on the Excel FILTER function page.

3) Advanced Filter Macros

Advanced Filters work in any version of Excel, but it takes a few steps to run them manually. It also takes a bit of practice, so that might discourage your co-workers from using them. To overcome those problems, you can create Advanced filter macros, with worksheet buttons to run the macros!

These macros could 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.

Watch this video to see how to get started, by using the Excel macro recorder. After you turn off the recorder, see how to make a few simple changes to the code. The revised VBA code that I created in the video is shown in the next section.

Advanced Filter VBA Maco Code

Here is the code, after making a few changes to it, as shown in the video above.

Note: I added a few extra line breaks with the underscore character, so the code would fit better on this page.

Sub TopOrderFilter()

Sheets("Orders").Range("A1") _
  .CurrentRegion.AdvancedFilter _
    Action:=xlFilterCopy, _
    CriteriaRange:=Sheets("Orders") _
        .Range("F1:F2"), _
    CopyToRange:=Sheets("TopOrders") _
        .Range("A1:D1"), _
    Unique:=False
End Sub

More Advanced Filter Macro Examples

To see more Advanced Filter macro example, and the steps for getting 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.

More Tutorials

Excel FILTER function

Advanced Filter Basics

Advanced Filter Criteria

Advanced Filter Macros

Advanced Filter Criteria Slicers

 

 

Last updated: April 20, 2024 9:49 AM