How to Copy Excluding Hidden Rows in Excel (4 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.

Apply Keyboard Shortcut to Copy Rows in Excel Except the Hidden Ones

Steps:

  • Select the whole dataset B5:D12.

Apply Keyboard Shortcut to Copy Rows in Excel Except the Hidden Ones

  • Then right-click on your mouse and select Copy from the Context menu. Or use the shortcut key Ctrl+C to copy.

Apply Keyboard Shortcut to Copy Rows in Excel Except the Hidden Ones

  • 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.

Select the Rows Manually and Copy Avoiding the Hidden Rows

  • After that, click the destination Cell A14 and press CTRL+V to paste.

Select the Rows Manually and Copy Avoiding the Hidden Rows

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.

Use Go To Special Dialog Box to Copy Rows in Excel Except Hidden Rows

  • Select the Visible cells only button.
  • Then press OK.

Use Go To Special Dialog Box to Copy Rows in Excel Except Hidden Rows

  • The visible rows are selected now, just press CTRL+C to copy.

Use Go To Special Dialog Box to Copy Rows in Excel Except Hidden Rows

  • 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.

Use Excel Quick Access Toolbar to Copy Rows without Hidden Rows

  • Select All Commands from the Choose commands from drop-down.

Use Excel Quick Access Toolbar to Copy Rows without Hidden Rows

  • Later, from the Command box, click on the Select Visible Cells option.
  • Then press Add >> button.
  • And finally, press OK to finish.

Use Excel Quick Access Toolbar to Copy Rows without Hidden Rows

The command is added properly.

Use Excel Quick Access Toolbar to Copy Rows without Hidden Rows

  • Now select the data range and click the Select Visible Cells icon from the toolbar.

Use Excel Quick Access Toolbar to Copy Rows without Hidden Rows

Only visible cells are now selected.

  • Press CTRL+C to copy them.

Use Excel Quick Access Toolbar to Copy Rows without Hidden Rows

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.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel projects.
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Hello! I am Md. Sourov Hossain Mithun. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is an amazing software to learn or work. Here, I will post Excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo