Select Visible Cells Only Hack

Who knew what a filter could do?

When you only want to copy the visible cells Excel has a special option that allows you to select visible cells only before copying. But there a way to make that the default option.

As with many Excel options I found out about this when I was trying to help someone do something else.

By the way, to select visible cells only use the keyboard shortcut Alt + ;

To see how the hack works try this

  1. In a blank sheet enter 1 in A1,2 in A2 and 3 in A3
  2. Hide row 2 (right click Hide)
  3. In cell A10 enter Num and make it bold
  4. In cell A11 enter 1 and in cell A12 enter 2
  5. Have cell A10 selected and press Ctrl + Shift + L this applies the filter icon.
  6. Filter by 1
  7. Copy A1:A3 (note the dotted lines around the copied range)
  8. Click cell B5 and Paste – only 1 and 3 are pasted even though you didn’t select visible cells only

Having a filter in place on a sheet (anywhere) defaults the copy on that sheet to visible cell only.

So if you are doing a lot of Visible Cells Only copies, first apply a simple filter to the sheet – at the bottom is OK – and then all the copies will default to Visible Cells Only.

By the way if you didn’t know, in a filtered list when you copy it defaults to visible cells only – that makes sense in a table.

The above hack works anywhere on the sheet after a filter is applied on that sheet.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

6 thoughts on “Select Visible Cells Only Hack

  1. Thanks for sharing! This workaround helped me out a ton; I was afraid that I’d have to try some overly complex VBA jazz to get my sheet to behave as intended. This way was much easier 🙂

  2. Thanks for this work-around. Is there any way to PASTE to visible cells only.
    I have set a filter on a column so say only 5 out of 10 rows are visible. I want to copy data from 5 contiguous rows from another sheet and paste it here in this sheet BUT only to the 5 visible rows. Is that possible?
    Thanks

    • Hi Narendra
      Sorry, I can’t see any way to do that.
      Can you sort the list to get the list in the same sequence you want to paste in.
      If the existing sequence of the list is important make sure you can return the list to that sequence.
      Regards
      Neale