Trouble with formulae? Lookups not going so well? The Wizard of Excel can help. Dan Harrison offers practical tips and examples, as well as Excel training and consulting.If you're cutting or copying content from a filtered range, beware. Here are some behaviours that may catch you out.
First of all, copying.
- If you copy from a filtered range, Excel will only copy those cells on display. Which is as you'd probably expect
- When you paste them, however, they will paste into a contiguous range. So if rows 4 and 7 are showing from your filtered range, and you copy copy these two rows to the top of a brand new sheet, the contents of rows 4 and 7 will appear in rows 1 and 2 of your new sheet
- But if you instead try to copy the contents of a filtered column into another area of the filtered range, you'll get in a mess. So if you try to copy filtered cells A4 and A7 into column B, you'd highlight B4 and paste. But this would paste into B4 and B5, B5 being a hidden unfiltered cell. Very odd.
Now let's look at cutting, a completely different kettle of fish.
- If you cut from a filtered range, Excel will cut everything in between the top and bottom of your selection, including those intervening cells that are hidden because of filtering.
- So if you cut cells B4 to B7 (what looks like two cells given the filter hiding B5 and B6), and paste it to the top of a new sheet, the top four cells will be populated (with the contents of B4, B5, B6 and B7)
- If you instead paste them into column C, then they will paste beautifully.
It's not intuitive. And it's caused me a lot of issues in the past. So be aware of what's going on. And make sure your wits are about you when you're copying or cutting in close proximity to a filtered range.
Hi Wizard of Excel,
Thanks for the tip! I have a pressing question -- how then can I copy and paste my filtered rows from spreadsheet A to another similar filtered spreadsheet B?
My friend and I are working on different spreadsheet and will like to consolidate them. Realised that when we copy and paste his entries into mine, all rows were messed up.
Please help me, Wizard of Excel. Please help me, Dan. Please help...
Desperately need help,
Ricky
Hi Ricky,
There is no way of doing this simply. This is because Excel has no way of knowing whether your filtering in spreadsheet A is the same as your friend's in spreadsheet B. (E.g. you may hav rows 1, 4, 5 filtered in; your friend may have 1, 3, 5).
The best way to do this is to unfilter both spreadsheets, and create a column (column X, say) that replicates the filtering rules:
E.g. =if(and(or(A2="London",A2="New York"),B2>100000,"Yes","No")
Then in your spreadsheet, for the column that you want to copy across (column J, say), create another pseudo column to contain the data you want to copy across. Put this in column Y using the formula below.
=if(X2="Yes",J2,"")
This will contain the value in your spreadsheet where you want to copy across. And nothing for the cells you don't want to copy across.
Copy this column, unfiltered, into the first free column in your friend's spreadsheet. Let's say this is column P. Then create column Q which is populated like this:
=if(P2<>"",P2,J2)
This says if the copied column has something in column P, use that, if not, use the original value that was in your friend's spreadsheet which is in column J.
Then copy column Q and paste special values into column J to overwrite the originals. And delete columns P and Q from your mate's spreadsheet.
Job done. I hope!
Dan.
Hi Dan,
Thanks so much for your swift reply! I will try your approach right away.
You are truly, truly a wizard of excel. Can I share your useful website with my friends too?
Ricky
Most certainly. Thanks for the compliment. 🙂
Dan.
Hi:
I have a worksheet with several filters, therefore have many hidden rows. I want to copy an entire column (including hidden values) and paste into another column. What happens is that the values populate until a hidden cell is encountered. I have tried to cut and paste and this works very well. Am I doing something incorrectly or is there a way around this? Could you explain why cut and paste works but copy and paste does not?
Thanks very much! I have learned a lot from your website!
Pingback: Cut And Paste Filtered Data – windowsloadon.com
Hello.
There is differences when we copy and cut from a filtered range of rows. What happen when with the filtered range when we delete rows from these filtered ranges then? Will it work like copy or cut? For example we have a range of filtered rows appeared as rows 1,2,4,7,8,9. Rows 3,5,6 is filtered and hidden. If I select rows from 2 to 7 in this range in this case is row number 2,4,7 and right click and select delete rows, will excel only delete rows numbered 2,4,7 or will it delete the whole rows from 2 to 7 says 2,3,4,5,6,7. It seems excel will work on the delete of filtered range of rows like when we copy (you say above) but I want to verify there is no fault on my Windows. Please tell me a bit.
Thanks.