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.
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 🙂
You are welcome – I had forgotten about this workaround thanks for reminding me.
Great solution! Thanks for that. I have shared the link to this page on the microsoft community where in the past someone was looking for a solution for this issue. Let me know if you do not consent. https://techcommunity.microsoft.com/t5/excel/set-default-to-copy-and-paste-visible-cells-only/m-p/132326/highlight/false#M2956
This way the information is more broadly available for possible other people looking for this solution in the future.
No problems, thanks.
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