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.
Excel Advanced Filter
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 send Excel 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.)
- In the Advanced Filter dialog box, click Copy to another location.
- Click in the List Range box, then select the Orders sheet, and select the list of orders.
- Click in the Criteria range box, then the Orders sheet, select the criteria range (F1:F2)
- Click in the Copy to box, and select cells A1:B1 on the Top Orders sheet.
- Click OK
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 orders 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, watch this short video tutorial.
- [Update] The video in the next section shows a newer version of Excel
Video: Send Data to Different Sheet – No Macro
[Update] In this 5-minute video, I show how to send Excel data to a different sheet with an Advanced Filter in a newer version of Excel.
More Excel Filter Examples
Send Excel Data to Another Sheet
Advanced Filter Criteria Slicers
________________
The Copy To option is a curious thing.
a) If you evoke the Advanced Filter dialog via VBA
Application.Dialogs(xlDialogFilterAdvanced).Show (1), Selection.Address, “”, “”
then the Copy To option can be used to push data to another sheet else it can only pull..
b)The Copy to Option can be used to copy more than 8192 areas from one sheet to another
How do I create a shopping list from a list of items.
I wish only to extract items >0 to another sheet but I want the list to be ‘live’- I do not want to use the advanced filter method as shown above.
Can this be done?
I am not yet familiar with macros – are there any templates that I could use?
Many thanks,
Dale.
Can I do the same method if the criteria is in text or date format