Danger: copying and cutting in a filtered range

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.

This entry was posted in Advice and tagged , , , . Bookmark the permalink.

7 Responses to Danger: copying and cutting in a filtered range

  1. Pingback: Cut And Paste Filtered Data – windowsloadon.com

Leave a Reply

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