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

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.

Download Practice Workbook

You can download the free Excel template from here and practice on your own.

4 Ways to Copy Rows in Excel Excluding Hidden Ones

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.

Read More: How to Copy Alternate Rows in Excel (4 Ways)

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 Rows Automatically in Excel to Another Sheet (4 Methods)

Similar Readings

• VBA Code to Compare Two Excel Sheets and Copy Differences
• Excel VBA: Copy Cell Value and Paste to Another Cell
  
• How to Copy and Paste Multiple Cells in Excel (7 Quick Ways)
• Excel VBA: Copy Range to Another Workbook
• [Fixed]: Right Click Copy and Paste Not Working in Excel (11 Solutions)

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.

The 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 and Paste in Excel Without Hidden Rows

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

Read More: How to Autofilter and Copy Visible Rows with Excel VBA

Things to Remember

While using the manual method, always paste in a cell of the first column otherwise it won’t work.

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

• How to Copy Multiple Cells to Another Sheet in Excel (9 Methods)
• Difference Between Paste and Paste Special in Excel
• Excel VBA to Copy Data from Another Workbook without Opening
• VBA Paste Special to Copy Values and Formats in Excel (9 Examples)
• Apply VBA PasteSpecial and Keep Source Formatting in Excel
• How to Copy and Paste in Excel and Keep Cell Size (7 Examples)
  
• How to Copy Visible Cells Only without Header Using VBA