Excel lets you hide columns and rows.This is a great way to make a clean, presentable Excel sheet. You can insert formulas in rows and columns, and hide them later. It also keeps other users from editing your formula. If the formula isn’t visible, chances are people are less likely to try and edit it. Hiding rows and columns is also a good way to hide the data used to create graphs or dashboards in the spreadsheet. The only problem with hidden cells is that while they are invisible, Excel still selects and copies their content. It still reads the data in hidden rows and columns even if it doesn’t show it. This is a problem if you want to copy only the visible cells in a sheet. Here’s how you can select only visible cells in Excel, and copy their content.
There’s two ways to select only visible cells in Excel. One is the easy keyboard shortcut way. The other is the dragged out, ribbon menu method. We’re going to detail both.
Select Only Visible Cells In Excel – Keyboard Shortcut
Open the Excel sheet you want to copy cells from. Select the cells you want to copy. Don’t worry if it includes hidden rows and columns in the selected cell range.
Once you’ve selected the cells, type the Alt+; shortcut. A white outline will appear around the selected cells.
This outline means that only the visible cells are selected. Go ahead and use the Ctrl+C and Ctrl+V shortcuts to copy and paste the content of the visible cells.
Select Only Visible Cells In Excel – Menu
Select the cells you want to copy. Again, don’t worry if the selected range includes hidden columns and/or rows. On the Home ribbon, click the Find & Replace button and select ‘Go to special’ from the menu.
A window will appear with a long list of options to select. Select the ‘Visible cells only’ option and click ‘Ok’. The same white outline that appeared around the selected cells when you tapped the Alt+; keyboard shortcut will appear again. It shows that only the visible cells are selected. Go ahead and use the copy/paste shortcuts or buttons to copy and paste the cell contents.
It goes without saying that the keyboard shortcut is the easiest way to select only visible cells in Excel. It’s an oddly obscure keyboard shortcut and the menu option is very well hidden. Users who don’t know about the Alt+; shortcut paste cells in an intermediate worksheet and clean it before pasting the content where they need to. It’s hardly convenient though still easier than going through the menu options. That said, Alt+; is a keyboard shortcut you want to write down somewhere.