When you’re working with the data set that has hidden rows, and you select and copy the cells in this dataset, it is going to copy the visible cells as well as the hidden cells.
That’s the default behavior in Excel.
But what if you only want to copy the visible cells (and not the hidden cells)?
Thankfully, it’s pretty easy and can be done with a very simple keyboard shortcut or an inbuilt feature in Excel.
Let me show you how it works.
This Tutorial Covers:
Click here to download the file and follow along
Shortcut to Copy Visible Cells
There’s a special keyboard shortcut in Excel to select only the visible cells in a data set. Once these visible cells are selected, you can easily copy them using the regular Control + C (or right-click and Copy).
And below is that shortcut (both Windows and Mac)
Windows - ALT + ; (semicolon)
Mac - Command + Shift + Z
Now, let me show you how it works.
Below, I have a data set where I have some rows hidden:
Here are the steps to copy only the visible cells:
- Select all the visible cells that you want to copy. In this example, I will select A2:C8.
- Hold the ALT key and press the Semicolon key (or use Command + Shift + Z if using a Mac). As soon as you do this, you will notice a thin white border wherever there are hidden rows (as shown below). This indicates that only the visible cells have been selected, and the hidden cells have not been selected.
- Copy the cells using Control + C (or right-click on the selection and then click on the Copy option). You will notice that the green marching ants lines appear around the overall data set as well as in between the data set wherever there are hidden rows.
- Go to the destination cell and paste this using Control + V (or right-click and then click on the Paste option)
Pro Tip: Do you know that if you have a filtered dataset and you select the visible cells and use Control + C, the filtered rows that are not visible won’t be copied? But if you have hidden rows, those would be copied.
Also read: How to Select Non-adjacent Cells in Excel?Copy Visible Cells Only Using Go To Special Dialog Box
If you don’t want to remember a keyboard shortcut, here is another way to copy only the visible cells.
Below, I have a data set with some rows hidden, and I want to copy only visible rows.
Here are the steps to copy only the visible cells:
- Select all the visible cells that you want to copy.
- Press the F5 key on your keyboard. This will open the ‘Go To’ dialog box.
- Click on the ‘Special’ button in the Go To dialog box. This will open the ‘Go To Special’ dialog box.
- Select the ‘Visible cells only’ option in the Go To Special dialog box.
- Click Ok.
- Copy the cells using Control + C (or right-click on the selection, then click on the Copy option)
- Go to the destination cell and paste this using Control + V (or right-click and then click on the Paste option)
Note: You can also open the Go To Special dialog box by clicking on the Home tab, then clicking on the ‘Find and Select’ option in the Editing group, and then clicking on the ‘Go To Special’ option.
Also read: Select Till End of Data in a Column in Excel (Shortcuts)Add an Icon to Copy Visible Cells to the Quick Access Toolbar
If copying visible cells is something you need to do quite often, you can add the option to Select Visible Cells only in the Quick Access Toolbar (QAT).
This way, whenever you want to copy only visible cells, you can select the dataset and then click on the Select Visible Cells icon in the Quick Access Toolbar. Once done, you can then easily copy only the selected visible cells.
Below are the steps to add the Select Visible Cells only icon to the Quick Access Toolbar:
- Click on the Customize Quick Access Toolbar icon. It’s the downward pointing arrow after all the icons in the QAT.
- Click on the More Commands option.
- In the Choose commands from dropdown, click on All Commands.
- Scroll down in the options and click on the Select Visible Cells option.
- Click on the add button to add this option to the Quick Access Toolbar.
- Click Ok.
The above steps would add the select visible cells icon to the QAT.
Now, when you want to copy only the visible cells in a dataset, select the dataset, click on the Select Visible Cells icon in the QAT, and then use Control + C to copy only the visible cells.
So these are three simple methods you can use to quickly copy visible cells or rows/columns in a data set in Excel.
I hope you found this Excel article useful.
I would love to hear your thoughts in the comments section below. Also, if you know of any other method you can use to copy only the visible cells in Excel, do share it with us all.
Other Excel articles you may also like:
- How to Count Filtered Rows in Excel?
- Paste into Filtered Column (Skipping Hidden Cells) in Excel
- Fill Down Blank Cells Until the Next Value in Excel
- 7 Easy Ways to Select Multiple Cells in Excel
- Select Till End of Data in a Column in Excel (Shortcuts)
- Remove Dashes in Excel
- Select a Far-off Excel Cell or Range
- How to Swap Cells in Excel?
