Video: Paste one or multiple values to visible cells only in Excel

Copy a value and paste it to visible cells

Let's assume you have a column of numbers from 1 to 8, and the rows containing numbers 3-6 are hidden. Now you want to replace the unhidden numbers with the number 10. Here's how to do it:

Step 1: Select and copy the value

Select the cell that contains the number 10. Then press Ctrl + C to copy the value.

Step 2: Select visible cells only

1. Select the range where you want to paste the value.
2. Press Alt + ; to select the visible cells only within that range.
3. 

Step 3: Paste the copied value to visible cells only

With the visible cells selected, press Ctrl + V to paste the copied value.

Result

Unhide the rows, and you'll see the pasted value appears only in the previously visible cells (marked in light yellow) as shown below.

Copy multiple values and paste them to visible cells

When it comes to pasting multiple values into visible cells only, it gets a bit trickier. Please read on to learn about the following methods for accomplishing this:

• With a fill color (Simple, but disrupts the original order)
• With a helper column (Versatile workaround, though slightly intricate)
• With Kutools for Excel (Efficiency meets ease, allows varied pasting with or without formatting)
• With Fill functionality (User-friendly for row-aligned data, limited to formatted pasting)

Paste values to visible cells with a fill color

In this section, we will update the stock count for the visible fruits as shown below by pasting new figures from cells A10:A12 over the original values as an example.

Step 1: Apply a fill color to the right of the dataset

Select the column to the right of the visible data, and apply a fill color (e.g., yellow in our case).

Step 2: Remove the filter and sort cells with fill color on top

1. On the Data tab, select Clear to clear the applied filter.
2. Right-click on one of the colored cells, and select Sort > Put Selected Cell Color On Top.
3. 

The previously visible rows that were scattered across the table are now collectively located at the top.

Step 3: Copy and paste the values

Simply copy the updated stock counts from cells A10:A12 and paste them to replace the ones in cells C2:C4.

Paste values to visible cells with a helper column

In this section, we will update the stock count for oranges, pineapples and bananas without disrupting the original data order.

Step 1: Add a helper column

Before filtering rows, add a helper column to the right of your data and fill it with sequential numbers.

Step 2: Filter your data

1. Click any single cell inside the data set, and then click Filter on the Data tab, in the Sort & Filter group.
2. Click the arrow next to Fruit, and select Bananas, Oranges and Pineapples to filter the related rows.
3. 

Step 3: Color the background of visible cells in Helper column

Select the visible cells in the helper column, and apply a fill color (e.g., yellow in our case).

Step 4: Remove the filter and sort cells with fill color on top

1. On the Data tab, select Clear to clear the applied filter.
2. Right-click on one of the colored cells, and select Sort > Put Selected Cell Color On Top.
3. 

The previously visible rows that were scattered across the table are now collectively located at the top.

Step 5: Copy and paste the values

Simply copy the updated stock counts from cells A10:A12 and paste them to replace the ones in cells C2:C4.

Step 6: Restore data to its original order

Select the arrow next to Helper, and then select Sort Smallest to Largest to restore the data to its original order.

Result

As you can see, the stock count for oranges, pineapples and bananas (next to the yellow cells) are updated. You can delete the Helper column if it's no longer needed.

Paste values and formatting to visible cells with Kutools in 2 clicks

Kutools for Excel’s Paste to Visible feature allows you to conveniently paste copied data into visible cells only, bypassing filtered-out or hidden cells. This feature supports pasting values only or both values and formatting, thus saving you considerable time and effort.

Consider a scenario where the rows containing numbers 3-6 are hidden, and you want to paste values from cells A12:A15 along with formatting to the visible cells in the range A2:A9 only. Once you have Kutools for Excel installed, just follow these steps:

1. Select the cells (A12:A15) containing values to be copied.
2. Navigate to the Kutools tab and select Paste to Visible.
3. A dialog box will appear. Select the top cell (A2) where you will paste the values to, and click OK.
4. 

Result

Upon unhiding the rows, you can see that the values are pasted only to the cells that were visible initially.

Paste values to visible cells on the same row with Fill functionality

In this example, I will update the stock count for the visible fruits as shown below with the new stocks marked in red using the Fill functionality.

Step 1: Select destination cells and values to be pasted

1. Select the destination cells where to paste values. For this example, the destination cells would be the visible cells in the Stock Count column.
2. While pressing and holding the Ctrl key, select the values you want to copy. For this example, select the cells in the Stock Update column.
3. 

Step 2: Apply the Fill functionality

On the Home tab, in the Editing group, select Fill > Left to fill the values from right to left.

Result

Once I clear the filter, you'll see that only the stock counts previously visible in column C are updated.

Above is all the relevant content related to pasting to visible cells in Excel. I hope you find the tutorial helpful. If you're looking to explore more Excel tips and tricks, please click here to access our extensive collection of over thousands of tutorials.

Related articles

• How to paste values to visible/filtered cells only in Excel?
• In Excel, if you have filtered data or hidden some cells, and when you paste values into the filtered or hidden range, the hidden cells will be also filled with pasted values. Here I will tell you some tricks to paste values to visible cells only in Excel.
• How to copy only visible cells in Excel?
• When you set some rows and columns to be hidden in a worksheet and want to copy the only visible cells in Excel, can you just directly copy and paste it in Excel? No, there are some tricky things you should know for doing so. Please go through the following steps to know how you could only copy the visible cells in Excel.