Google Sheets is an excellent tool for organizing and presenting your data in a useful way. One of the most common operations on Google Sheets is copying and pasting data. Copying data is not difficult; however, it becomes problematic when copying and pasting visible cells overlapping with the hidden ones. In this article, we will show you how to copy and paste only visible cells in Google Sheets.

Method 1: Using the Filter Function to Copy Visible Cells

The simplest way to copy and paste only visible cells is by using the Filter function. The Filter function allows you to filter out hidden rows and columns to view only visible ones. Here are the steps:

Step 1: Select the data range you want to copy.

Step 2: Click on the Data tab and select Create a Filter.

Step 3: Click on the drop-down arrow in the column you want to filter.

Step 4: In the Filter menu, uncheck the box next to the blank, and click OK. This will hide all the unwanted rows and columns.

Step 5: Once the desired data is filtered, select the entire range, right-click, and select Copy.

Step 6: Go to the intended location, right-click, and select Paste.

Step 7: Click on the Filter button again and select Clear.

Now you have successfully copied and pasted only visible cells in Google Sheets.

Method 2: Using a Script to Copy Visible Cells

If you need to copy and paste visible cells frequently, a script can help you automate this process. Here are the steps:

Step 1: Open the Google Sheets file you want to copy data from, and click on the Tools menu, select Script Editor.

Step 2: In the Script Editor, you can create a new script by clicking on File and selecting New script.

Step 3: Copy the script code below and paste it in the empty script editor.

“`
function copyVisibleCells() {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getActiveRange();
var visibleRange = sheet.getRangeList(sheet.getFilter().getRangeList().getRanges()).getRanges()
.reduce(function(a, b) { return a ? a.union(b) : b });
var data = visibleRange.getValues();
var destination = sheet.getActiveRange();
destination.setValues(data);
return true;
}
“`
Step 4: Save the script with an appropriate name.

Step 5: Go back to the Google Sheets file, select the data range you want to copy and click on the Run menu, and select the copyVisibleCells function.

Step 6: The new Range of visible cells will be pasted to the current active range.

By using this simple script, you can copy and paste only visible cells in Google Sheets automatically and with ease.

Conclusion

Copying and pasting in Google Sheets is a great feature that can simplify your work. However, it becomes challenging when copying visible cells overlapping hidden ones. By using either the filter function or a script, you can achieve copying and pasting visible cells quickly and efficiently. We hope this article helps to improve your efficiency in using Google Sheets.