How To Copy Filtered Data In Excel

Please Note: This commodity is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, and 2016. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you lot. For a version of this tip written specifically for earlier versions of Excel, click here: Copying the Results of Filtering.

Copying the Results of Filtering

If y'all utilise the advanced filtering capabilities of Excel, you are not limited to filtering "in place." You tin can likewise practise the equivalent of a database extraction, which is a 2-pace procedure. Outset, the listing is filtered, and then the records that friction match your criteria are copied to a unlike area of the worksheet.

To instruct Excel to copy the results of a filtering, follow these steps:

Select the area you lot want to filter.
Brandish the Data tab of the ribbon.
Click the Advanced tool, in the Sort & Filter group. Excel displays the Advanced Filter dialog box, with the address of your original information table already filled in, in the List Range box. (See Figure i.)

Effigy one. The Advanced Filter dialog box.

Set your filtering options as desired.
Make sure the Re-create to Another Location radio button is selected.
Specify a copy destination in the Re-create To field.
Click on OK.

When yous specify a destination for the copy (step 6), you have three options. First, if you specify a single prison cell every bit the destination, then Excel copies the results of the filtering, regardless of the number of records extracted. If yous are working with a big list and the results of the filtering might exist many, many rows, nevertheless, you might non want to practice this. In this case, make the destination a row option. Excel volition then only copy that many rows. Thus, if the result of the filtering was 47 records, and your destination was a selection of 12 rows, only the first 12 records are copied. The final option is to select a range of cells. This limits the copy to the number of rows and columns specified by the range.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (7561) applies to Microsoft Excel 2007, 2010, 2013, and 2016. You tin find a version of this tip for the older carte interface of Excel hither: Copying the Results of Filtering.

Author Bio

With more than fifty non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a figurer and publishing services company. Learn more about Allen...

MORE FROM ALLEN

Adjusting a Range's Starting Point

Select a range of cells, and one of those cells will ever be the starting point for the range. This tip explains how to ...

Discover More than

Turning Off Borders for Data Series

Don't want a border to announced around a data series represented in a Microsoft Graph chart? You can easily command the ...

Find More

Removing Spaces

Demand to get rid of spaces in a range of cells? There are two ways you lot can approach the task, as described here.

Observe More

More than ExcelTips (ribbon)

Non-PivotTable Slicers and Timelines

When working with a PivotTable, slicers and timelines can make short work of big information sets. This tip looks at all the ...

Discover More

Filtering to a Date Range in the Past

If you lot take a large number of information records, each with an associated engagement, yous might want to filter that information so you see ...

Discover More

Performing Calculations while Filtering

The advanced filtering capabilities of Excel let you lot to hands perform comparisons and calculations while doing the ...

Discover More