Watch Video – 3 Ways to Select Visible Cells in Excel
What do you do when you have to copy a range of cells in Excel and paste it somewhere else?
In most cases, the below three steps get the work done:
- Select the cells that you want to copy.
- Copy the cells (Control + C).
- Select the destination cell and paste these cells (Control + V).
But what if you have some hidden cells in the dataset?
Then – these above three steps are not enough.
Let me show you what happens when you try to copy cells that have hidden rows/columns in it.
Suppose you have a dataset as shown below:
Note that there are hidden rows in this dataset (look at the row numbers).
Now see what happens when I try to copy these cells and paste it somewhere else.
In the above example, I selected the visible cells, but when I paste these cells into another location, it copied the visible as well as the hidden cells.
The workaround to this is to make sure that Excel only selects the visible cells. Then I can copy and paste these visible cells only.
In this tutorial, I will show you three ways to select visible cells only in Excel.
Select Visible Cells using a Keyboard Shortcut
The easiest way to select visible cells in Excel is by using the following keyboard shortcut:
- For windows: ALT + ; (hold the ALT key and then press the semicolon key)
- For Mac: Cmd+Shift+Z
Here is a screencast where I select only the visible cells, copy the visible cells (notice the marching ants around selection), and paste these:
Select Visible Cells using Go To Special Dialog Box
While using the keyboard shortcut is the fastest way to select visible cells, if you don’t want to use the keyboard or don’t remember the shortcut, there is another way.
You can use the ‘Go To Special’ dialog box to select visible cells in a dataset.
Here are the steps:
- Select the data set in which you want to select the visible cells.
- Go to the Home tab.
- In the Editing group, click on Find and Select.
- Click on Go To Special.
- In the ‘Go To Special’ dialog box, select ‘Visible cells only’.
- Click OK.
This would select all the visible cells in the dataset.
Also read: Select Till End of Data in a Column in Excel (Shortcuts)
Select Visible Cells using a QAT Command
Another great way to select visible cells in Excel is to add a command to the Quick Access Toolbar (QAT).
Once added, you can simply click this command in the QAT, and it will select visible cells in the dataset.
Here are the steps to add ‘Select Visible Cells’ command to the QAT:
- Click on the Customize Quick Access Toolbar icon.
- Select ‘More Commands’.
- In the ‘Excel Options’ dialogue box, from the ‘Choose command from’ drop-down, select ‘All Commands’.
- Scroll down the list and click on ‘Select Visible Cells’ option.
- Click on the Add button.
- Click OK.
The above steps would add the ‘Select Visible Cells’ command to the QAT.
Now you when you select a dataset and click on this command in the QAT, it will select visible cells only.
You May Also Like the Following Excel Tutorials:
- How to Deselect Cells in Excel
- How to Select Non-adjacent cells in Excel?
- Number Rows in Excel
- How to Count Filtered Rows in Excel?
- How to Hide a Worksheet in Excel (that can’t be unhidden).
- How to Select Every Third Row in Excel (or select every Nth Row).
- Highlight EVERY Other ROW in Excel (using Conditional Formatting).
- How to Quickly Select Blank Cells in Excel.
7 thoughts on “3 Quick Ways to Select Visible Cells in Excel”
Excellent! the select visible cells works perfect. Thank you for the great work!
Hi, the windows command works perfect for me; e.g. I can select all filtered cells of a column and press alt+; and it selects only that column.Mac however selects all the visible cells, I know the commands Cmnd+shift+* as well as Cmnd+shift+* do the same ( select all visible). My question; do you know the shortcut to select only the column or row( only the cells you want to select)?
Thank you for sharing your knowledge!
excellent but now if you want to paste this data where there is still hidden rows.
Hi Summit nice work.
How do you add a small gif file into a web page? Can you tell me please?
I use Camtasia for it.
Just go CTRL + C, but paste from the clipboard. Works like a charm 🙂 CTRL + C + C also works, in case you have activated that option from the clipboard.https://uploads.disquscdn.com/images/9acb06e2928b92cdd9eec91f104155a13b36732d3ba1af0886dd71bb8dd5a015.png
And happy new year to all.
It will be very interesting if you show what happens when you are trying to copy filtered rows: I thing there is not need to select visible cells. Please correct me case I am wrong.
Comments are closed.