Please Note: This article 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. For a version of this tip written specifically for earlier versions of Excel, click here: Copying the Results of Filtering.
Written by Allen Wyatt (last updated September 22, 2021)
This tip applies to Excel 2007, 2010, 2013, and 2016
If you use the advanced filtering capabilities of Excel, you are not limited to filtering "in place." You can also do the equivalent of a database extraction, which is a two-step process. First, the list is filtered, and then the records that match your criteria are copied to a different area of the worksheet.
To instruct Excel to copy the results of a filtering, follow these steps:
Figure 1. The Advanced Filter dialog box.
When you specify a destination for the copy (step 6), you have three options. First, if you specify a single cell as the destination, then Excel copies the results of the filtering, regardless of the number of records extracted. If you are working with a large list and the results of the filtering might be many, many rows, however, you might not want to do this. In this case, make the destination a row selection. Excel will 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 can find a version of this tip for the older menu interface of Excel here: Copying the Results of Filtering.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
Some types of data may have certain fields that contain partially identical information. In such cases you may want to ...
Discover MoreThe filtering capabilities of Excel can come in handy for zeroing in on the data you want to work with. When your data ...
Discover MoreIf you have a large number of data records, each with an associated date, you might want to filter that data so you see ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2017-10-25 10:25:32
Dennis Costello
Matt ... you might want to check for redundant conditional formats - it's really easy to accumulate these inadvertently.
2017-10-24 08:52:41
Matt Parker
Dennis - that is what I normally do. I find that sometimes when I copy filtered data it seems to make my excel slow down or blow up the size of the file. Not every time but just sometimes.
Speaking of - I have been dealing with an issue where I get the error message "Too many excel formats". I try to copy paste just values and not include the formats. I try to delete unnecessary tabs but sometimes continue to get the message. I am wondering if I just need to rebuild these files all together.
2017-10-23 15:55:17
Dennis Costello
A much less formal approach also works perfectly well: after setting the filter(s) on your data as desired, select the filtered data (or as I often do, a few columns of it) and do a simple Copy (Ctrl-C) and Paste (Ctrl-V). If you're pasting it to a different part of the same worksheet, you'll probably want to undo the filters between the Copy and Paste (the Clear icon in the Data ribbon's Sort & Filter section is very handy here).
2017-10-10 06:32:54
Peter Atherton
Matt, try this link
http://www.contextures.com/xladvfilter01.html
Drill down and it contais a video on copying to a new sheet.
2017-10-09 10:05:27
Matt Parker
I tried using this tip on a data table and it did not work. I re-read the instructions three times to make sure I was doing it correct. I keep getting the message, "You can only copy filtered data to the active sheet". I specified the copy range to be one cell on a new sheet within the same workbook. I am on excel pro 2010
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2023 Sharon Parq Associates, Inc.
Please Note:
This article 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. For a version of this tip written specifically for earlier versions of Excel, click here:
Comments