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
- In a blank sheet enter 1 in A1,2 in A2 and 3 in A3
- Hide row 2 (right click Hide)
- In cell A10 enter Num and make it bold
- In cell A11 enter 1 and in cell A12 enter 2
- Have cell A10 selected and press Ctrl + Shift + L this applies the filter icon.
- Filter by 1
- Copy A1:A3 (note the dotted lines around the copied range)
- 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.