How to Paste Visible Cells Only in Excel

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. 

sample data

 

The column indicating the number of shops is hidden by the user since it’s irrelevant to the current report.

hidden column in sample table

 

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.

copying range includes hidden data

 

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.

pasting data also pastes to hidden rows and columns

 

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:

  1. 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.
    select range you want to copy
  2. Enter the keyboard shortcut Ctrl + G to access the Go To tool dialog box. Click on the Special… option.
    use Go To Special function
  3. Under the Go To Special options, select the option labeled ‘Visible cells only’. Click on OK to finalize your selection.
    copy Visible Cells Only in Excel
  4. 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.
    Paste Visible Cells Only in Excel
  5. When the user pastes their selection, all hidden cells are excluded in the final result.
    Paste Visible Cells Only in Excel
  6. 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.
    copy value you want to paste
  7. 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.
    select range to paste values
  8. 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.
    use Go To Special function to Paste Visible Cells Only in Excel
  9. 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.
    Paste Visible Cells Only in Excel
  10. When we reveal the hidden rows, we can see that they have not been overwritten by our paste action.
    hidden cells are excluded

 

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.

Get emails from us about Excel.

Our goal this year is to create lots of rich, bite-sized tutorials for Excel users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

 

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like