How to Copy and Paste only Visible Values in Google Sheets

How to copy and paste only visible values in Google Sheets
Reading Time: 6 minutes

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. We can hide data 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. 

This guide is intended to show you how to copy and paste only visible values in Google Sheets.

Hiding a row and copy-pasting the values

For instance, 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 could simply do this. 

The sample data
a

To hide a row in Google Sheets is as easy as the following steps:

  •  Right-click on the row number 
Selecting a row to hide
  • Go to Hide row.
Hiding a row in Google Sheets

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. 

The values pasted along with the hidden row

The problem is, Google Sheets will copy over 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 and paste only the data we have left visible 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 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 

Selecting only visible cells manually

Step 2: Copy the selected data

Simply use the keyboard shortcut Ctrl + C (Command +C for Mac) or left-click on the selection and select copy. Because you selected the data while holding down the Ctrl key  (or Command), the copied data will only contain the parts you selected. 

Copying only visible cells in Google Sheets

Step 3: Paste the copied data 

Move over to where you want to paste your copied data and press Ctrl + V

Pasting only visible rows in Google Sheets

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 alternatives.

Example 2: Using a filter to hide data instead 

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 that to the desired location. 

To filter rows, follow these steps.

  • Select the data range 
  • Click on Data from the tab ➡ Create a filter
Creating a filter in Google Sheets
  • Click on the filter icon in the column you want to use in the filter. In our case, the Customer column. That will lead you to the filter menu.
  • There are several options available in the menu but we will filter by values.
Filter options in Google Sheets
  • We will select Luke Mayor in the drop-down menu to indicate that we want to hide this row.
  • Apply the filter by click OK.
Filtered data in Google Sheets

Now we can easily copy 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 in our filtered data will be pasted as you can see below.

Pasted visible values

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. 

Grouping cells in Google Sheets

To create groups:

  • Select all cells or columns of visible data
  • Select ViewGroup
Creating groups in Google Sheets

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
Copying grouped cells in Google Sheets
  • Copy and paste each group individually to form one table 
Hidden rows omitted in the pasted values

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 to 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. 

See Also

Check out similar articles on our blog 

How to count non-empty cells in Google Sheets

https://blog.tryamigo.com/exclude-a-cell-from-a-range-in-google-sheets/

https://blog.tryamigo.com/count-cells-by-color-in-google-sheets/