How to Copy Without Hidden Rows in Google Sheets
Google Sheets is a web-based spreadsheet program similar to Microsoft Excel, and both share many features. On the former, you’ll likely end up copying some cells, but after pasting, find that hidden rows or columns appeared. These are visually displeasing, and you’ll have to remove them manually.
If you use Google Sheets often and wonder if there’s a way to remove them, you’ve come to the right place. There are ways to get rid of these hidden objects and to copy and paste without creating them. Keep reading to find out how.
How to Copy Without Hidden Rows in Google Sheets on a PC
Anyone with a Google account and browser can use Google Sheets for free, as with plenty of other Google software. An advantage Google Sheets has over Microsoft Excel is letting others collaborate on projects in real-time. You can also share documents with your colleagues in just a few clicks.
When copying tables, you’re sometimes liable to copy hidden rows or columns. These can be misleading, especially if you use Google Sheets to calculate sums of gathered data.
Other times, you’ll want to copy only the visible rows and leave the hidden ones behind. The reasons for doing so vary depending on the project. However, standard copying and pasting methods still bring those over.
You can copy and paste everything and delete the hidden rows later, but this is time-consuming, and you might accidentally miss one. Your results might then be skewed until you find the culprit.
Fortunately, there is a solution that allows you to copy objects without hidden rows interfering. The process involves copying only the visible rows specifically. Here’s how it’s done:
- Launch Google Sheets in your browser.
- Go to your project.
- Hold down the Ctrl key.
- Click on all the visible cells you wish to copy.
- When you’ve selected them all, copy them using Ctrl + C or by right-clicking.
- Paste the rows in a different location or another file.
- Repeat if necessary.
There used to be a different solution that involved pasting special values, but it has become obsolete with updates to Google Sheets.
Users have tried to copy and paste all rows to Excel and then move the values from Excel back to Google Sheets. However, this other method doesn’t work, as the hidden rows will still get copied.
Some suggest a third fix: to paste in a new tab and paste back. But this is also ineffective. Currently, the only way to copy and paste without hidden values appearing is to select individual rows, as seen above.
How to Copy Without Hidden Rows in Google Sheets on an iPad
iPad users will also encounter the problem of copying hidden rows when they copy and paste everything. PC and mobile device users can collaborate on the same document, though iPad users won’t have the same user interface. Nevertheless, it’s still possible to copy and paste without seeing any hidden rows duplicated as well.
Unless you’re planning to use a mouse and keyboard with your iPad, you’re stuck with touch screen controls. They’re more than sufficient for the job, of course. Here’s how to copy and paste without much trouble:
- On your iPad, tap and launch your Google Sheets app.
- Load your project.
- Tap and select all of the rows you want to copy.
- Paste the values onto a new page or file.
- They should be pasted without hidden rows, and you can repeat the process if necessary.
The “fixes” we mentioned before don’t work, so this is the only method you can currently use. Pasting special values, pasting them to another location, repasting them, and copying them from a new page are all either obsolete or ineffective. That applies regardless of whether you’re using touch screen controls or mouse and keyboard.
Deleting Filtered Rows
The reason some of the rows on your spreadsheet are hidden is that you’ve activated a filter. These filters make it so that the user will only see the values that fall into a specific group. Therefore, anything that doesn’t match gets hidden from sight.
That doesn’t mean the values are gone, however. If you’re using these values in an equation or formula, they’re still being factored in, even if you can see them.
If you wish to remove them for some reason, you can do so by selecting the appropriate options in Google Sheets. Here are the steps:
- Launch Google Sheets.
- Open your spreadsheet.
- Click on the filter button on the row you wish to copy from.
- Uncheck the criterion or criteria you want to keep.
- Make sure only the rows you want to delete are checked.
- Select all the filtered rows.
- Right-click anywhere in the area.
- Click on “Delete Selected Rows.”
- Unhide the hidden rows by rechecking them or clicking on “Select All.”
- Click on “OK” to confirm
The instructions for iPad or other mobile devices are similar:
- On your iPad or another mobile device, launch the Google Sheets app.
- Open the spreadsheet you wish to modify.
- Tap the filter button on the columns with filtered rows.
- Uncheck everything you want to save from deletion.
- Check only the criteria you want to remove.
- Select all the filtered rows with your fingers.
- Tap on the area.
- Select “Delete Rows.”
- Open the filter menu again and unhide all the hidden rows.
- Confirm your choice.
If any hidden rows bother you, you can permanently delete them before copying and pasting the data. That way, there’s no risk of copying hidden rows because they don’t exist anymore.
No Extra Numbers Needed
As Google Sheets gets updates, the older fixes for the problem of copying hidden rows have been made useless. Today, you only have one way to prevent copying them.
How do you think Google can address this problem effectively? Do you think they’ll listen to user feedback? Let us know in the comments section below.