In Excel, you can automatically copy data from one sheet to another, without using a macro. For example, there’s a long list of orders on one worksheet, and you’d like to find your largest orders, and copy those to a different worksheet.
You can use Excel’s Advanced Filter feature to automatically select and copy the data that you want, if you do a bit of work to set things up first. For this Advanced Filter, you’ll need to set up three things in the workbook:
- the list of data that we want to filter
- a criteria area, that sets the rules for what to filter
- an Extract area, where we want the results to end up
The List to Filter
In this example, you’ll filter a list of recent orders, which is on a sheet named Orders, in columns A:D.
The Criteria Area
Next, you’d set up a criteria range, and enter the rules for the filter. In this example you want to copy the orders with the largest totals.
On the Orders sheet, the criteria range is set up in cells F1:F2. (Note: You could set up the criteria range on a different sheet, if you prefer.)
- Cell F1 has the heading Total, which exactly matches the heading in column D.
- Cell F2 contains the criteria for the Total column: >1500
This will filter the list for order totals that are greater than $1500.
The Extract Area
You’d like the largest orders to be copied to the second sheet – Top Orders. You can leave the extract area empty, to copy all the columns from the source list, or add headings for specific columns that you’d like to see in the filtered results.
On the Top Orders sheet, two headings, Date and Customer, have been added. Those are the only fields that you want to pull from the original list.
Run the Advanced Filter
If you want to copy data to a different sheet with an Advanced Filter, you have to start on the sheet where you want the data to end up.
- Select the Top Orders sheet – that’s where you want the data to be copied.
- Select an empty cell, away from any other data on the sheet. In this example you could select cell C4.
- On the Ribbon, click the Data tab, then click Advanced. (In Excel 2003, click Data | Filter | Advanced Filter.)
See the Advance Filter Results
When you click OK in the Advanced Filter dialog box, all the orders with a total greater than $1500 are copied to the Top Orders sheet.
There are 3 orders in the source list that are greater than $1500, and those dates and customers appear on the Top Orders sheet.
Advanced Filter Criteria
This example has a simple criteria range (F1:F2), with one column and a single rule (>1500).
You can create complex Advanced Filter criteria ranges, with multiple columns and rules. For example, you could pull one or two products, or orderes in a specific date range for the filtered data. There are Advanced Filter examples with complex criteria on the Contextures website.
Watch the Video
To see the Advanced Filter steps in Excel 2003, you can watch this short video tutorial.