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.