[Excel Shortcut] - Copy Only Visible Cells - Autofilter

Select & Copy Only Visible Cells in Excel

In Excel, a row or column may be hidden when we use auto-filter option or manually hide them. Add an auto-filter over a column or a table & Select a valid value for the filter. This action will hide few rows which does not satisfy filter criteria. To select & copy only these visible cells, the conventional method Ctrl+A, Ctrl+C will not work out. Try one of these methods in such cases.

1. Excel Keyboard Shortcut to Select/Copy visible Cells

Follow the steps as mentioned in below sequence in Windows & Mac systems.

Select rage of cell to be copied.
Selecting Only visible cells:
- Windows: Press “Alt + ;” (Alt + Semicolon)
- Mac: Press Control + Shift + Z
Press “Ctrl + C”
Select target location cell.
Press “Ctrl + P”

Note: Mac users instead of Alt+ colon, use Ctrl + Shift + Z This keyboard shortcut comparatively involves fewer steps than the option 2. Just remember to use “Alt + Semicolon” before using copy shortcut. The below option will also copy only the visible cells in your current view.

2. Excel Select/Copy Visible Cells only - Menu option

MS Excel has built in option to select, copy & paste visible cells only. They have added this option to the Excel menu itself for ease of use. After applying a filter, carefully follow the steps explained below one by one. (Application to MS Excel 2007 & above).

Select the range of cells that needs to be copied.
Choose Excel ‘Home’ Menu.
In ‘Editing Tab’, click “Find & Select” option.
Click on “Go to Special”.
Choose “Visible cells only” & click Ok.
Press ‘Ctrl + C’.
Select target location or sheet.
Press ‘Ctrl + P’.

Now, you can see only the visible cells are being pasted. Yes. It is bit a lengthy process for first time. Once if you know that the trick is in the “Find & Select” menu option “Go to Special”, then it is all easy after that. If You skip any of the steps or order is changed, the hidden rows will be also get copied & pasted. So, be careful while following the steps.

Difference between 'Copy All Cells' & 'Copy Visible Cells'

Sometimes We use Copy + Paste in Excel to select visible cells, just like how we use it to select all cells. After pasting we will realize that we don't need all the data. You will go back to source sheet & reconfirm your selection area again. Every Excel Novice would have come across this problem where you might've overlooked the hidden rows/columns. WYSIWYG(What You See Is What You Get) logic will go wrong here. Many times you have to undo & clear the filters and need to do selective copy/paste which is manual intensive work. If your sheet has thousands of records, master these tiny Tips. It will save a lot of your productive time.