Move Excel Data Without a Macro

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.

Advanced Filter List

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

Advanced Filter Criteria Area

  • 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.

Advanced Filter Extract Area

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.

  1. Select the Top Orders sheet – that’s where you want the data to be copied.
  2. Select an empty cell, away from any other data on the sheet. In this example you could select cell C4.
  3. On the Ribbon, click the Data tab, then click Advanced.  (In Excel 2003, click Data | Filter | Advanced Filter.)
    • Excel Ribbon Data Advanced
  4. In the Advanced Filter dialog box, click Copy to another location.
  5. Click in the List Range box, then select the Orders sheet, and select the list of orders.
  6. Click in the Criteria range box, then the Orders sheet, select the criteria range (F1:F2)
  7. Click in the Copy to box, and select cells A1:B1 on the Top Orders sheet.
  8. Click OK

Advanced Filter dialog box

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 results

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.

________________

You may also like...

3 Responses

  1. sam says:

    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

  2. Dale says:

    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.

  3. devi says:

    Can I do the same method if the criteria is in text or date format

Leave a Reply to Dale Cancel reply

Your email address will not be published. Required fields are marked *