Last Updated on October 31, 2023 by Jake Sheridan
This tutorial will teach you how to copy only visible cells in Google Sheets.
How To Copy Only Visible Cells In Google Sheets
When you are dealing with datasets in Google Sheets, you may find it helpful to hide certain rows or column. This can help to keep your sheet organized and easier to read.
For example, you may want to hide helper columns that are important only for computation purposes. You may also want to hide cells that are no longer relevant to the current task but might want to bring back later on.
While hiding cells is a simple action, you may find it tricky to work with tables with hidden values. Using the typical method of copying a range will result in the hidden values appearing nonetheless.
In this guide, we will show you how to copy only the visible cells in Google Sheets.
How to Ignore Hidden Cells When Copying in Google Sheets
Here’s how to ignore hidden cells when copying data in Google Sheets.
First, identify the parts of your range that are separated by hidden rows and columns.
In this example, values in column C are hidden. This divides our table into two ranges: A1:A18 and D1:F18. We will have to find a way to select these two ranges separately. This is because selecting the entire range A1:F18 will still include the hidden values in column C.
Next, use your cursor to select the first cell range.
In this example, we’ve selected the first two columns of our table.
Next, hold down the Ctrl key and use your cursor to select the next range of visible cells.
Holding down the Ctrl key allows users to select multiple ranges at once.
After all visible cell ranges have been selected, use the Ctrl+C keyboard shortcut to copy the entire selection.
Select an empty cell and use the Ctrl+V shortcut to paste the copied selection.
The resulting table should include only the visible cells from the original selection.
Alternatively, we can use a Google Sheet filter to hide particular values rather than hide the actual row.
For example, instead of hiding all rows with a specific credit_card_type, we can use the Filter tool to hide them from view. The benefit of filters is that copying the filtered range will ignore any values filtered from view.
To start with this method, first select the range you want to add a filter to.
Next, click on the option Data > Create a filter.
Click the Filter icon of the column you want to filter with. Uncheck any values you want to filter out of your current range.
In this example, we’ve decided to hide all rows with a “jcb” credit card type.
Click on OK to proceed.
When the user copies the filtered range, all hidden rows will be filtered out from the selection.
This guide should be everything you need to copy only visible cells in Google Sheets.
You may make a copy of this example spreadsheet to test it out on your own.