Replace values in filtered ranges using Copy and Paste trick in #Excel

Situation:

In a filtered range of data, we made few changes on the side. Then we want to replace the original values with the updated values.

What action appear on top of your mind? Copy and Paste of course.

But… if you had tried, you knew it… a regular Copy and Paste does NOT work. 😓

Did you see the problem?

At the first glance, It looks like that only 2 values were replaced correctly. Indeed the result was a total mess (only the first cell will be pasted with the value as expected).

Let’s clear the filter and examine what’s actually been pasted (assuming we spotted something wrong and checked immediately… you know what I am talking about).

The value in C9 got pasted “correctly” just by luck !!

😨😨😨 OMG… What happened?

When we copy from a filtered range, only “Visible cells” are copied. It is denoted by the “marching ants” as shown below:

In this case, four values (cells) are copied to clipboard. Excel simply ignores all the cells (values) that are filtered out.

When we paste this to C6, Excel pastes these four values as a continuous range starting at C6. In other words, the four values will be pasted to C6, C7, C8 and C9 respectively. That’s why the copy and paste in filtered range is a hassle.

We have a problem. Do we have a solution?

Not really a solution, but workaround for situation like this.

The secret sauce is “Skip Blank”.

Let’s follow the steps below:

You download a sample file to follow along.

1. Clear all filters before copying

2. Copy the whole column (D)

3. Paste Special… to the destination column (C)

4. Paste “Values” and most importantly check “Skip blanks”

OK! 🌭🌭

Let’s watch it in action. (Note: targeted cells are highlighted for easy reference)

As simple as this. 😉

Watch it in action here:

Related blogposts:

Hidden Rows vs. Filtered Rows

Using Skip blanks in Paste Special

About MF

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Excel Tips and tagged , , . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.