This post will show us how to copy only visible cells in Excel & Google Sheets. When working with grouped or hidden rows/columns, you may require to copy only visible cells. If you copy these cells manually, the selection will, by default, include the hidden rows or columns, which may result in unintentionally pasting unwanted or incorrect data elsewhere. However, both Excel and Google Sheets allow you to copy only relevant (visible) information while excluding the hidden data.
Specific features or steps can help you copy only visible cells in both Excel and Google Sheets. Let’s have a look at this in detail.
How to copy only visible cells in Excel
Excel provides a built-in ‘Go To Special – Visible Cells Only‘ feature that selects only the visible cells within a selected data range. Once selected, you may copy-paste these cells wherever you want. Let us see how.
Suppose we have a worksheet containing customer information. The data includes the following details for each customer: Customer ID, Name, Email, Phone Number, City, and Purchase Amount. In the dataset, rows 2 and 3 are grouped, and column D has been hidden.
To copy only the visible cells, select the entire range of data, including the grouped and hidden rows. Then press F5 (or Fn + F5) to open the Go To dialog box. Alternatively, you may press Ctrl + G.
In the Go To Special dialogue, click on Special… and select Visible cells only. Then click OK.
The command will highlight only what can be seen in the selected data range, excluding cells that are not visible at the moment. Now press Ctrl + C to copy the selection and paste it wherever needed.
Tip: To quickly select visible cells only in Excel, select the range of cells you want to copy and press ALT + ;.
How to copy only visible cells in Google Sheets
Google Sheets lacks a native ‘Visible cells only’ option like Excel. Additionally, in Google Sheets, grouped rows/columns behave opposite to filtered rows/columns (we will come to that in a moment). If the grouped rows are collapsed, they are still included in the selection when copying, even though they are hidden. On the other hand, filtered rows are excluded from the selection when hidden. So you may copy only visible cells if the hidden data has been excluded using filters. Let us see how.
Suppose we have the same spreadsheet data as taken in the previous example. To exclude rows 2 and 3, go to the Data menu and select Create a filter. A filter icon will appear in each column header. Click the filter icon in the Name column and uncheck the data you want to hide. For example, in our case, we will uncheck ‘Sangeeta Ghera’ and ‘Laxman Singh’ from the dropdown.
Now, the dataset will display only rows 1, 4, and 5.
Next, you need to highlight the visible rows. In Google Sheets, you may select visible cells by selecting individual ranges. Here’s how:
Hold down the Ctrl key and use the mouse to manually select only the visible cells. For example, in the above example, we will first press and hold the Ctrl key, then click on cell A1 and drag till cell A6. This will select the data range A1:A6. Now keeping the Ctrl key pressed, we will select cell B2 and drag till B6. Similarly, we will select the rest of the visible cells.
Once the data is highlighted, press Ctrl + C to copy the selected cells. You will see a dotted blue border around them. Now paste the data into the desired location using Ctrl + V. When pasting, ensure the destination range is large enough and does not overwrite other critical data.
Note: Always double-check the copied data before pasting to ensure it matches your intended selection, especially if your dataset contains grouped or filtered rows/columns.
That’s it! I hope you find this useful.
Read: Auto Fill options not showing in Excel; How to enable it?
What is the shortcut key for copying visible cells only in Excel?
The keyboard shortcut to copy visible cells only in Excel is ‘Alt + ;‘. To use the shortcut, select the range of cells you want to copy, and press ALT + ;. Now, when you copy (Ctrl + C) and paste (Ctrl + V), only the visible data will be included, preventing hidden data from being pasted unintentionally.
How to autofill only visible cells?
The standard autofill in Excel includes all cells, even hidden ones. To autofill only visible cells in a dataset, select the range of cells that includes both visible and hidden rows and press Alt + ;. Type the value or formula you want to autofill and press Ctrl + Enter to fill only the visible cells.
Read Next: How to add a Checkbox in Google Sheets?
