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

The sample data

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. 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 values pasted along with the hidden row

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.

Selecting only visible cells manually

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.

Copying only visible cells in Google Sheets

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

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 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.
Creating a filter in Google Sheets to hide values in Google Sheets
  • 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.
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 clicking OK.
Filtered data in Google Sheets to hide rows

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.

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 copy and paste only visible cells

To create groups:

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

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


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?

How do I paste visible cells only?

What is the shortcut to copy only visible cells?

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

How to Exclude a Cell From a Range in Google Sheets

Count cells by color in Google Sheets

Leave a Reply