How To Select Only Visible Cells In Excel
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.
Hi!
Could you please help me with the following issue related to this post?
I have Excel 2016 in a Mac and previously I was using the following shortcut to select ONLY VISIBLE ROWS from filtered data:
SHIFT + CMD + *
After that I could copy the data and will copy only the visible information and not the hidden information, or I could delete only the visible rows without deleting the hidden ones.
But a month or so ago after an update I cannot longer do that and when I do SHIFT + CMD + * what it does is to select all cells including the headings and it includes the hidden cells.
Do you know if there’s a new shortcut to do what I was previously doing? Or another way to do so? Take into account that in Mac there’s no “GoTo Special” function available.
Thanks in advance