If you have hidden rows in your dataset and you select the whole dataset using a mouse or CTRL+A shortcut and copy, Excel will select and copy the hidden rows too. If you want to copy the rows in Excel excluding the hidden rows, this article is here to guide you with 4 easy methods with proper explanations.
How to Copy Excluding Hidden Rows in Excel: 4 Ways
Let’s get introduced to our dataset first that represents some salespersons’ sales in different regions.
1. Apply Keyboard Shortcut to Copy Rows in Excel Except for the Hidden Ones
Here, I have hidden some rows in my dataset. Now we’ll apply a keyboard shortcut to select and copy rows while excluding hidden rows.
Steps:
- Select the whole dataset B5:D12.
- Then right-click on your mouse and select Copy from the Context menu. Or use the shortcut key Ctrl+C to copy.
- Then select the destination cell where you want to copy, e.g.I’ve selected B14.
- Finally, press Paste.
Now see that the data range is copied excluding the hidden rows.
2. Select the Rows Manually and Copy Avoiding the Hidden Rows
In this section, I’ll show a manual way to copy rows in Excel excluding hidden rows. This method is not suitable for a wide range of data but you may find it useful in some cases. In my dataset, the hidden row numbers are 6,7, and 11. Now follow the steps to see how to select visible rows manually and copy.
Steps:
- Press and hold the CTRL key on your keyboard.
- Then select row numbers 5 and 8.
- Later, release the CTRL key, press and hold the SHIFT key, and select row number 10.
- Then again press and hold the CTRL key and select row number 12.
- The dataset is now selected except for hidden rows, just press CTRL+C to copy.
You can also select the rows one by one while holding the CTRL key.
- After that, click the destination Cell A14 and press CTRL+V to paste.
Then you will get that. Excel has copied the rows excluding hidden rows.
Read More: How to Copy Alternate Rows in Excel
3. Use Go To Special Command to Copy Rows in Excel Except for Hidden Rows
In this method, we’ll use the Go To Special tool to do the same operation.
Steps:
- Select the dataset B5:D12.
- Then click as follows: the Home tab> the Editing group > Find & Select option> Go To Special.
Then Go To Special dialog box will appear.
- Select the Visible cells only button.
- Then press OK.
- The visible rows are selected now, just press CTRL+C to copy.
- Finally, click the destination cell and press CTRL+V to paste.
The visible rows are now successfully copied.
Read More: How to Copy Rows in Excel with Filter
4. Use Excel Quick Access Toolbar to Copy Rows without Hidden Rows
To select visible cells only, we’ll use the Excel Quick Access Toolbar instead of Go To Special to copy rows in Excel excluding hidden rows. First, we’ll activate the command, and then we’ll apply it.
Steps:
- Click Customize Quick Access Toolbar icon.
- Then click on More Commands.
A dialog box named Excel Options will open up.
- Select All Commands from the Choose commands from drop-down.
- Later, from the Command box, click on the Select Visible Cells option.
- Then press Add >> button.
- And finally, press OK to finish.
The command is added properly.
- Now select the data range and click the Select Visible Cells icon from the toolbar.
Only visible cells are now selected.
- Press CTRL+C to copy them.
After that, click the destination cell and press CTRL+V.
Then you will get the output like the image below.
Things to Remember
While using the manual method, always paste in a cell of the first column otherwise it won’t work.
Download Practice Workbook
You can download the free Excel template from here and practice on your own.
Conclusion
I hope the procedures described above will be good enough to copy rows in Excel excluding hidden rows. Feel free to ask any question in the comment section and please give me feedback.
