How To Copy Without Hidden Rows In Excel

Device Links

• Android
• iPhone
• Mac
• Windows
• Device Missing?

When you copy and paste a list of cells in Excel, the hidden cells are always added along with the visible ones. However, this default Excel setting might not suit all your data entry needs. If you are working with filtered data, this simple copy-paste method doesn’t always work as smoothly as you would expect. It is possible to copy only visible cells in Excel and skip the hidden ones when working with a worksheet where specific rows, cells, or columns do not appear. If this is what you would like to do, this article will show you how to accomplish that.

How to Copy Only Visible Cells on a PC

Supposing you have an Excel table that has been filtered to display a particular data set. It may be challenging to copy and paste the table because, by default, Excel will also show any hidden rows in the new table. However, there is a nifty trick you can use to ensure that this concealed data does not appear in your new worksheet. Here’s how to copy only visible cells on a PC.

1. Select the cells that you would like to copy and paste.
   
2. Navigate to the “Home” tab and tap the “Find & Select” icon. It looks like a magnifying glass.
   
3. From the dropdown menu displayed, pick “Go to Special.”
   
4. Press “Visible Cells Only” in the window that appears, then click “OK.”
   
5. Tap Ctrl + C on Windows or Command + C on a Mac to copy the selected cells.
   
6. Go to the location where you want to paste your cells and hit Ctrl + V on Windows or Command + C on a Mac to display them there.
   

You have now copied and pasted rows in Excel without displaying the hidden rows.

Every once in a while, you might be working with a table with features like subtotals. This worksheet might be tricky to copy-paste, even with the method described above. This is how you can copy and paste these kinds of tables.

1. Press Alt + ; (semicolon) on Windows or Cmd + Shift + Z for a Mac PC. This shortcut selects only the visible cells.
   
   or
   
2. Tap Ctrl + C for Windows or Command + C for Mac to copy the data.
   
   or
   
3. Go to the new table and click Ctrl + V for Windows or Command + V for a Mac to paste the information there.
   
   or
   

Your new worksheet will only contain data from the visible rows.

How to Copy Only Visible Cells on an iPad

When you copy and paste a table that has been filtered to show a certain set of data, Excel will, by default, also show the hidden rows. However, a simple trick will prevent the hidden data from appearing in your new worksheet. The steps below will walk you through the process.

1. In the Excel document you want to copy, place your finger on the section you are interested in and hold it there for a few seconds until some text is highlighted.
2. Once the text is highlighted, take your finger off the screen. A menu will appear with the options to cut, copy or paste.
   
3. Drag your finger across the table, selecting the information you would like to copy.
   
4. Navigate to the “Home” tab and tap the “Find & Select” magnifying glass icon.
5. Pick “Go to Special” from the dropdown menu.
6. Press “Visible Cells Only” from the window that appears, then tap “OK.”
7. Click “Copy” from the menu displayed next to your selected cells.
   
8. Move to your new worksheet, then press and hold a finger on the screen. The word “Paste” will be displayed within a few seconds.
   
9. Hit “Paste” to input the data into the worksheet.
   

You have now copied and pasted only the visible rows in Excel.

Sometimes, you’ll be working on a table with features such as subtotals. This type of worksheet might be more challenging to copy and paste using the method described above. Check out how to copy data from this type of table.

1. Press Cmd + Shift + Z to select only the visible cells.
2. Click Cmd + C to copy the data selected.
3. Head over to your new work table and tap Cmd + V to paste the information.

Your new table will now be populated with only the visible rows.

How to Copy Only Visible Cells on an iPhone

When creating a new worksheet, Excel will still show all the rows from a filtered document, displaying visible and hidden data. However, this trick will prevent the hidden rows from appearing in the new worksheet.

1. Place your finger on the Excel document and hold it there until a section of the text is highlighted.
   
2. Once you remove your finger from the screen, a menu will appear with the options to cut, copy or paste.
   
3. Drag your finger across the text to pick the sections you would like copied.
   
4. Go to the “Home” tab and click on the “Find & Select” icon.
5. Choose “Go to Special” from the dropdown menu displayed.
6. Tap “Copy” from the menu next to the highlighted text.
   
7. Navigate to your new worksheet and hold your finger down on the screen.
   
8. Hit “Paste” from the pop-up menu to enter the data into your new table.
   

The visible rows have now been copied and pasted from your old worksheet.

Sometimes, you’ll need to copy and paste data from a table that has subtotals. This is a bit more challenging to do, but the method outlined here will show you how to do that.

1. Tap Cmd + Shift + Z to highlight the visible cells.
2. Press Cmd + C to copy the highlighted data.
3. Go to the new worksheet and hit Cmd + V to paste the information.

You have successfully copied and pasted only the visible rows of your worksheet.

How to Copy Only Visible Cells on an Android

When you create a new worksheet, Excel will automatically display both the visible and hidden rows of the previous worksheet when copy-pasting. However, there is a way to prevent the hidden rows from appearing in your new table. Here’s how to copy and paste only the visible data.

1. Choose the cells that you would like to copy and paste.
   
2. Go to the “Home” tab at the top of the page and hit the “Find & Select” magnifying glass icon.
3. Tap “Go to Special” from the dropdown menu that appears.
4. Click “Visible Cells Only” in the new window that opens, then tap “OK.”
5. Press Ctrl + C to copy the data from the selected cells.
   
6. Head to the new worksheet and tap Ctrl + V to paste the information.
   

The visible rows have now been copied and pasted to your new table.

Sometimes, you might be working on a table that has subtotals. This form may be challenging to copy and paste, even with the methods described above. Here’s how to go about it instead.

1. Click Alt + ; (semicolon) to select only the visible cells.
2. Press Ctrl + C to copy the information.
3. Navigate to the new table and hit Ctrl + V to paste the data.

The new table now has information from only the visible cells.

Excel’s Hidden Feature

Copy-pasting an Excel spreadsheet is pretty straightforward. However, it can be a little challenging to do so with a filtered worksheet, as Excel will copy both the visible and hidden rows by default. However, by using the nifty tricks described above, you will be able to copy only the visible rows, excluding the hidden data from your new table.

Have you copied an Excel table without the hidden rows? Let us know how you did it in the comments below.