Filter Excel Data to Another Sheet

You can automatically copy Excel data from one sheet to another, without using a macro.For example, you might have a long list of orders, and want to copy one salesperson's orders to a different sheet. Or, you could automatically select your largest orders, and copy those.

In this Hub you'll see how you can use Excel's Advanced Filter feature to select the data that you want, and that data will appear on the other sheet when you click the OK button.

In this example, the workbook has two sheets.

• The first sheet is named Orders, and it contains a list of recent orders from customers.
• The second sheet is named Top Orders, and you'll use an Excel Advanced Filter to copy the highest valued orders to that sheet.

  
  

On the Orders sheet, a criteria range is in cells F1:F2.

• Cell F1 has the heading Total, which exactly matches the heading for the total column in the main list.
• Cell F2 contains the criteria for the Total column: >1500

  
  

  This will filter the list for order totals that are greater than $1500.

  
  

On the Top Order sheet, two headings, Date and Customer, have been entered. Those are the only columns that we'd like to see from the original list.

When you want to send data to a different sheet, by using an Advanced Filter, you have to start from the sheet where the data will end up.

1. Select the Top Orders sheet. That's where we want the data to be copied.
2. On the Top Orders sheet, select an empty cell that's away from any of the other data on the sheet. In this example we'll select cell C4.
3. From the Data menu, choose Filter, Advanced Filter. (Note: If you are using Excel 2007, click the Data tab on the Ribbon, then click Advanced Filter.)

4. Choose Copy to another location.

5. Click in the List Range box

6. Select the Orders sheet, and select the entire list.

7. Click in the Criteria range box.

8. On the Orders sheet, select the criteria range (F1:F2)

9. Click in the Copy to box.

10. Select cells A1:B1 on the Top Orders sheet.

11. Click OK

When you click the OK button in the Advanced Filter dialog box, the orders that meets the criteria are copied to the Top Orders sheet.

There are 3 orders in the main list that are greater than $1500, and those are the dates and customers that appear on the Top Orders sheet.

This example has a simple criteria range (F1:F2), with one heading and one rule (greater than $1500).

You can create much more complex Advanced Filter criteria ranges when you need to filter out different kinds of data from a list in Excel. For example, you could specify just one customer, or a specific date range for the filtered data.