Google Sheets is a web-based spreadsheet program similar to Microsoft Excel and they both share many features. In the first one, you will probably end up copying some cells, but after pasting them, you will discover that hidden rows or columns appear. These are visually unpleasant and you will have to remove them manually.
If you use Google Sheets frequently and are wondering if there is a way to delete them, you’ve come to the right place. There are ways to get rid of these hidden objects and copy and paste without creating them. Read on to find out how.
How to copy without hidden rows in Google Sheets on PC
Anyone with a Google account and browser can use Google Sheets for free, as with many other Google programs. One advantage Google Sheets has over Microsoft Excel is allowing others to collaborate on projects in real time. You can also share documents with your colleagues with just a few clicks.
When you copy tables, you may sometimes copy hidden rows or columns. These can be misleading, especially if you use Google Sheets to calculate the sum of the collected data.
Other times, you’ll want to copy only the visible rows and leave the hidden ones. The reasons for doing so vary depending on the project. However, the standard copy and paste methods still bring them.
You can copy and paste everything and delete hidden rows later, but this is time-consuming and you may accidentally miss some. Then your results could be biased until you find the culprit.
Fortunately, there is a solution that allows objects to be copied without hidden rows interfering. The process involves specifically copying only the visible rows. This is how you do it:
- Launch Google Sheets in your browser.
- Go to your project.
- Hold down the Ctrl key.
- Click all the visible cells you want to copy.
- When you have selected them all, copy them using Ctrl + C or right-clicking.
- Paste the rows to a different location or another file.
- Repeat if necessary.
There used to be a different solution that involved pasting special values, but it was deprecated with Google Sheets updates.
Users tried to copy and paste all the rows into Excel and then move the values from Excel to Google Sheets. However, this other method doesn’t work as the hidden rows will still be copied.
Some suggest a third solution: paste to a new tab and paste again. But this is also ineffective. Currently, the only way to copy and paste without hidden values appearing is to select inpidual 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 copying and pasting everything. PC and mobile users can collaborate on the same document, although iPad users won’t have the same user interface. However, it is still possible to copy and paste without seeing duplicate hidden rows.
Unless you plan to use a mouse and keyboard with your iPad, you’ll be stuck with the touchscreen controls. Of course, they are more than enough for the job. Here’s how to copy and paste without much trouble:
- On your iPad, tap and launch your Google Sheets app.
- Upload your project.
- Tap and select all the rows you want to copy.
- Paste the values into a new page or file.
- They should be glued without hidden rows and you can repeat the process if necessary.
The “solutions” we mentioned before don’t work, so this is the only method you can currently use. Pasting special values, pasting them to another location, re-pasting them, and copying them from a new page are obsolete or ineffective. This applies regardless of whether you’re using touchscreen controls or mouse and keyboard.
Delete filtered rows
The reason some of the rows in your spreadsheet are hidden is because you have turned on a filter. These filters cause the user to only see values that belong to a specific group. Therefore, anything that doesn’t match is hidden from view.
However, that does not mean that the values have disappeared. If you use these values in an equation or formula, they are still taken into account, even if you can see them.
If you want to delete them for any reason, you can do so by selecting the appropriate options in Google Sheets. Here are the steps:
- Launch Google Sheets.
- Open your spreadsheet.
- Click the filter button on the row you want 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 filtered rows.
- Right click anywhere in the area.
- Click “Delete Selected Rows.”
- Unhide hidden rows by checking them again or clicking “Select All.”
- Click “OK” to confirm
The instructions for iPad or other mobile devices are similar:
- On your iPad or other mobile device, launch the Google Sheets app.
- Open the spreadsheet you want to modify.
- Tap the filter button on the columns with filtered rows.
- Uncheck anything you want to prevent from being deleted.
- Check only the criteria you want to delete.
- Select all filtered rows with your fingers.
- Touch the area.
- Select “Delete Rows.”
- Open the filter menu again and show all hidden rows.
- Confirm your choice.
If any hidden row bothers you, you can delete it permanently before copying and pasting the data. That way there is no risk of copying hidden rows because they no longer exist.
No additional numbers needed
As Google Sheets updates, previous solutions to the copying hidden rows issue have become useless. Nowadays, you only have one way to avoid copying them.
