This guide will explain how to paste to visible cells only in Excel.
By default, selecting a range will still select any hidden cells within it. We can use the Go To tool to exclude these cells when copying and pasting.
Let’s look at a scenario where we might need to paste to visible cells only in Excel.
Suppose you have a list of tasks in a tracker. Some of these entries have been hidden by the spreadsheet owner.
You need to modify the status of all the currently visible tasks to ‘PENDING’. However, if you try to paste into the entire Status column, you will overwrite the statuses of the hidden entries as well. How can we avoid this in Microsoft Excel?
We can use the Go To Special tool to select visible cells only when pasting values. This article will explain how we can use this tool to copy and paste values in Excel.
Now that we have a grasp on when to use the Go To Special feature, let’s learn how to use it and work on an actual sample spreadsheet.
A Real Example of Pasting Visible Cells Only in Excel
The following section provides several examples of how to use this function. We will also go into detail about the formulas and tools used in these examples.
In the spreadsheet below, we have a table of monthly sales for a particular shop.
The column indicating the number of shops is hidden by the user since it’s irrelevant to the current report.
If the user tries to copy the range, the hidden cell still appears in the pasted result. We can avoid this by using the Go To Special tool.
Similarly, if we try to paste data into a range with hidden rows or columns, you may notice that some of the data is invisible. In the example below, the projected sales for January and February are not visible since they were placed in the hidden cells.
Do you want to take a closer look at our examples? You can make your own copy of the spreadsheet above using the link attached below.
If you’re ready to try out the Go To Special tool yourself, head over to the next section to read our step-by-step guide on how to use it when pasting values!
How to Paste Visible Cells Only in Excel
This section will guide you through each step needed to copy and paste values on visible cells only in Excel. First, we’ll look into how to copy values from visible cells only using the Go To tool. We’ll use the Go To tool again to select visible cells to paste our values into.
Follow these steps to start using Excel’s Go To feature to select visible cells when copying and pasting values:
- Let’s start with copying values from visible cells only. In the example below, we have a table with a single hidden column. First, select the entire range using your cursor.
- Enter the keyboard shortcut Ctrl + G to access the Go To tool dialog box. Click on the Special… option.
- Under the Go To Special options, select the option labeled ‘Visible cells only’. Click on OK to finalize your selection.
- The Go To Special tool will exclude hidden rows and columns from your selection automatically. Type the Ctrl + C shortcut to copy the new selection.
- When the user pastes their selection, all hidden cells are excluded in the final result.
- Now, let’s take a look at how we can use the Go To tool to paste a selection into visible cells only. First, select the cell you want to copy. In this example, we’ll copy cell E22, and we’ll try to paste these values into the range B22:B25. We want the hidden cells B23 and B24 to remain unmodified.
- Select the destination cell range for your copied values. In this example, we’ll try to paste the string ‘NEW_VALUE’ into the cell range B22:B25. Even though rows 23 and 24 are hidden, our selection still contains cells B23 and B24.
- Type the keyboard shortcut Ctrl + G to access the Go To Special feature. Under the Select options, click on the option labeled ‘Visible cells only’. The Go To Special tool will exclude hidden cells that were originally part of our current selection. Click OK to apply the new selection.
- The user may now paste their values using the keyboard shortcut Ctrl + V. In our example below, we pasted the string ‘NEW_VALUE’ on each cell in our target range.
- When we reveal the hidden rows, we can see that they have not been overwritten by our paste action.
These are all the steps needed to copy from and paste to visible cells only in Excel.
This step-by-step guide should provide you with all the information you need to paste values to visible cells only in Excel.
This function is just one example of the many Excel functions that you can also use in your spreadsheets. Overall, our website offers hundreds of other functions and methods to help you get more out of Microsoft Excel.
For example, you may want to read our guide on how to fix issues faced when copying merged cells in Excel.
With so many other Excel functions available, you can find one that is appropriate for your use case.
Subscribe to our newsletter, and you’ll get exclusive access to the latest spreadsheet guides and tutorials from us.