Google Sheets enables us to hide cells at our convenience, either manually or by using filters. Making values invisible is quite helpful in numerous situations. And so knowing how to copy and paste only visible values in Google Sheets.
We can hide certain values to reduce clusters in our worksheet, or to keep redundant content out of view. Other times we might simply want to focus on specific values and copy only visible cells in Google Sheets to another sheet so that we do not alter the original data.
In this article we’ll learn how to copy only visible cells in Google Sheets using different methods with examples.
Hiding a row and copy-pasting the values
Let’s use the following data sets to learn how to copy only visible cells and paste the values in another sheet. We have a table containing data for customers of an online marketplace. Let’s assume we need to hide Row 10, the entry for Luke Mayor, in the table below. We can easily do this.
To hide a row in Google Sheets is as easy as the following steps:
- Right-click on the row number.
- Go to Hide row.
However, there is a certain problem with hiding values in Google Sheets this way and it becomes apparent when you want to copy data from your table to somewhere else.
Using our previous table, after hiding row 10, let us try to copy the new table to another location in our worksheet.
- Press Ctrl + C to copy the highlighted table.
- Press Ctrl + V at your desired location.
On pasting the data in a new location, you would expect to see a table without row 10.
Below is the result of this operation which clearly includes the cells we have hidden, row 10. Ideally you’d want to copy only the visible cells in Google Sheets. However in this case the hidden values are copied as well.
The problem is, Google Sheets will copy the hidden data because it still sees that as part of the table–hidden or not. This can be problematic for various reasons. For example, we might only be interested in working with the visible data even though we have no intentions of deleting the hidden values because they are useful for other operations.
So, how exactly can we mitigate this lapse? How can we copy and paste only the visible cells?
It is quite easy. We will examine three ways to copy and paste only visible values in Google Sheets without needing special formulas or add-ons.
How to copy and paste only visible values in Google Sheets
In the following examples, we will examine 3 different ways to easily copy and paste only visible values in Google Sheets using our customer data table.
You can copy the spreadsheet used in this tutorial here – copy the sheet
Example 1: Copy and paste visible values in Google Sheets using Ctrl + drag
The easiest way to copy only the visible cells in Google Sheets is by dragging across the data range (the visible portions) with your mouse while holding down the Ctrl/Command key.
Step 1: Hold the Ctrl key and select the visible data
It is important to carefully drag your mouse over the visible data while avoiding the parts that contain hidden data. To do this, don’t drag over the entire data range but over individual parts of the visible data as shown below.
Step 2: Copy the selected data
Simply use the keyboard shortcut Ctrl + C (Command+C for Mac) to copy the selected visible cells or left-click on the selection and select Copy. Because you selected the data while holding down the Ctrl key (or Command), only the visible cells are copied.
Step 3: Paste the copied data
Move over to where you want to paste the copied data and press Ctrl + V (or Command+V, for Mac).
As you can see in the above image, the pasted data, unlike in the previous case, doesn’t contain row 10 which is hidden.
While the above method is simple, picture a scenario where you want to copy hundreds of data rows with hidden rows randomly placed in between them. As you must have realized, manually copying portions of the data set as above can become very tedious really fast. So let’s consider some more viable alternatives to copy only visible cells in Google Sheets.
Example 2: Using a filter to hide data instead to copy only visible cells
Another way to hide cells in Google Sheets is by using the filter menu. The filter feature is useful for sorting data. We are going to look at how we can utilize it to avoid separately copying and pasting data like in the previous example. By using a filter we can easily isolate our data range of interest and copy only the visible cells in Google Sheets.
To filter rows, follow these steps.
- Select the data range.
- Click on Data from the tab ➡ Create a filter.
- Click on the filter icon in the column you want to use in the filter. In our case, the Customer column. This will open the filter menu.
- There are several options available in the menu. We will filter by values.
- We will select “Luke Mayor” in the drop-down menu to indicate that we want to hide this row.
- Apply the filter by clicking OK.
Now we can easily copy only the visible cells and paste the data from the above operation to any location using the keyboard shortcuts Ctrl + C and Ctrl + V (if you use a Mac, Command instead of Ctrl). Only the cells visible in the table will be pasted as you can see below.
Note: The missing row is not deleted. You can easily remove the filter by clicking on the Data tab with the entire data selected then ➡ Remove filter.
Example 3: By grouping cells
Suppose you decide that the two options above aren’t for you—though we can hardly see why—then you can use this method. You can easily group the cells you want to copy, i.e the visible cells, while leaving the hidden parts out.
To create groups:
- Select all cells or columns of visible data
- Select View ➡ Group
Groups can be copied and pasted individually as shown below using the keyboard shortcuts.
- Expand a Group.
- Right-click on the highlighted data.
- Choose Copy from the menu.
- Copy and paste each group individually to form one table.
Conclusion
The major concern with copying and pasting only visible data is the method used in hiding the data. If you use the normal method or groups then you have to select and copy individual sets, but using filters makes it much easier to copy and paste only visible values in Google Sheets.
Frequently asked questions
How to delete hidden rows?
Hidden rows, though invisible, are still considered part of the table by Google Sheets. To totally delete them, simply unhide the rows ➡ click on the row number ➡ Delete row.
How do I paste visible cells only?
To paste only visible cells, hold down the Ctrl key while selecting the cells you want to copy, then paste the data into a new location.
What is the shortcut to copy only visible cells?
The shortcut to copy only visible cells is the same for copying other things–visible or invisible–which is Ctrl + C (Command + C for Mac).
Remember that if you don’t hide the cells using the filter function, then the hidden values will be copied as well. So make sure to filter the unwanted values before you copy.
See Also
Check out our blog for an exhaustive list of tutorials on Google Sheets and the various things you can use Google Sheets for.
Some articles you may find useful:
How to Count Non-empty Cells in Google Sheets