Device links
When you copy and paste a list of cells in Excel, the hidden cells are always added along with the visible ones. However, these default Excel settings may not suit all your data entry needs. If you’re working with filtered data, this simple copy and paste method doesn’t always work as well as you might expect. It is possible to copy only the visible cells in Excel and skip the hidden ones when working with a spreadsheet 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 achieve it.
How to copy only visible cells on a PC
Let’s say you have an Excel table that has been filtered to display a particular data set. It can be difficult to copy and paste the table because, by default, Excel will also show hidden rows in the new table. However, there is a neat trick you can use to make sure this hidden data doesn’t appear in your new worksheet. Here’s how to copy only visible cells on a PC.
- Select the cells you want to copy and paste.
- Navigate to the “Home” tab and tap the “Search & Select” icon. It looks like a magnifying glass.
- From the drop-down menu that appears, select “Go to Special.”
- Press “Visible cells only” in the window that appears, then click “OK.”
- Tap Ctrl + C on Windows or Command + C on Mac to copy the selected cells.
- Go to the location where you want to paste your cells and press Ctrl + V on Windows or Command + C on a Mac to display them there.
You have now copied and pasted rows in Excel without showing the hidden rows.
From time to time, you may be working with a table with features such as subtotals. It may be difficult to copy and paste this worksheet, even with the method described above. This is how you can copy and paste these types of tables.
- Press Alt +; (semicolon) on Windows or Cmd + Shift + Z for a Mac PC. This shortcut selects only visible cells.
either
- Tap Ctrl + C for Windows or Command + C for Mac to copy the data.
either - Navigate to the new table and click Ctrl + V for Windows or Command + V for Mac to paste the information there.
either
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, by default, will also show hidden rows. However, a simple trick will prevent hidden data from appearing in your new worksheet. The steps below will guide you through the process.
- 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.
- Once the text is highlighted, remove your finger from the screen. A menu will appear with the options to cut, copy or paste.
- Drag your finger across the table and select the information you want to copy.
- Navigate to the “Home” tab and tap the “Search & Select” magnifying glass icon.
- Choose “Go to Special” from the drop-down menu.
- Press “Visible cells only” in the window that appears, then tap “OK.”
- Click “Copy” in the menu displayed next to the selected cells.
- Go to your new worksheet, then press and hold one finger on the screen. The word “Paste” will appear in a few seconds.
- Press “Paste” to enter the data into the worksheet.
You have now copied and pasted only the rows visible in Excel.
Sometimes you will work in a table with features such as subtotals. This type of worksheet may be more difficult to copy and paste using the method described above. See how to copy data from this type of table.
- Press Cmd + Shift + Z to select only the visible cells.
- Click Cmd + C to copy the selected data.
- Head to your new artboard 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 continue to display all rows in a filtered document, showing visible and hidden data. However, this trick will prevent hidden rows from appearing in the new worksheet.
- Place your finger on the Excel document and hold it there until a section of the text is highlighted.
- Once you remove your finger from the screen, a menu will appear with options to cut, copy, or paste.
- Drag your finger across the text to select the sections you want to copy.
- Go to the “Home” tab and click the “Find and Select” icon.
- Choose “Go to Special” from the drop-down menu that appears.
- Tap “Copy” in the menu next to the highlighted text.
- Navigate to your new worksheet and hold your finger on the screen.
- Press “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 previous worksheet.
Sometimes you will need to copy and paste data from a table that has subtotals. This is a little more difficult to do, but the method described here will show you how to do it.
- Tap Cmd + Shift + Z to highlight visible cells.
- Press Cmd + C to copy the highlighted data.
- Go to the new worksheet and press 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 show the visible and hidden rows of the previous worksheet when you copy and paste. However, there is a way to prevent hidden rows from appearing in your new table. Here’s how to copy and paste only the visible data.
- Choose the cells you want to copy and paste.
- Go to the “Home” tab at the top of the page and press the “Search & Select” magnifying glass icon.
- Tap “Go to Special” from the drop-down menu that appears.
- Click “Only visible cells” in the new window that opens, then tap “OK.”
- Press Ctrl + C to copy the data from the selected cells.
- Go to the new worksheet and tap Ctrl + V to paste the information.
The visible rows have now been copied and pasted into your new table.
Sometimes you may be working on a table that has subtotals. Copying and pasting this form can be complicated, even with the methods described above. Here’s how to do it.
- Click Alt +; (semicolon) to select only visible cells.
- Press Ctrl + C to copy the information.
- Navigate to the new table and press Ctrl + V to paste the data.
The new table now has information only for the visible cells.
Excel’s hidden feature
Copying and pasting an Excel spreadsheet is quite simple. However, it can be a bit tricky to do this with a filtered spreadsheet, as Excel will copy the visible and hidden rows by default. However, using the clever tricks described above, you will be able to copy only the visible rows, excluding hidden data from your new table.
Have you copied an Excel table without hidden rows? Let us know how you did it in the comments below.
