Skip to main content

Paste one or multiple values to visible cells only in Excel

Author: Amanda Li Last Modified: 2023-08-30

Having difficulty pasting values into visible Excel cells, whether from a filtered range or hidden rows? You're not alone! From basic to advanced, this tutorial is tailored to guide you through the process of pasting data into visible cells only, effectively dealing with both filtered and hidden data scenarios. Buckle up and let's take this journey together!


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.
Tip: In case you forget the keyboard shortcuts, you can also select visible cells only by clicking Find & Select > Go to Special on the Home tab in the Editing group, and then selecting Visible cells only.

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:


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.

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.

Notes:

  • This method addresses scenarios with diverse data in visible cells. If your data share related information, such as oranges, you can directly group all data related to oranges together and then proceed with copying and pasting.
  • Be aware that this method disrupts the original data sequence. You will need to manually restore them if you have to. If you wish to preserve the original order, consider the following approach with a helper column.

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.

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.

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.

Note: This method addresses scenarios with diverse data in visible cells. If your data share related information, such as oranges, you can directly group all data related to oranges together and then proceed with copying and pasting.


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.

Result

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

Notes:

  • To enable this feature, please download and install Kutools for Excel. The professional Excel add-in offers a 30-day free trial with no limitations.
  • To copy and paste values only (excluding formatting and formulas), please click the arrow next to Paste to Visible and select Only Paste Values.

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

Note: This method works when pasting to cells made visible by a filter. If your data has rows that were manually hidden, the Fill functionality will not paste values to visible cells only.

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.

Note: You can select non-adjacent columns, but ensure the values you're copying and pasting are aligned in rows.

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.

Notes: The Fill functionality pastes both values and formatting into the cells. It does not allow you to paste only the values, excluding the formatting.

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.

Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations