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.