Excel – Paste into Visible Cells Only

Microsoft Excel

 

Sometimes Microsoft Excel is just too helpful.  Like American shop assistants to an English shopper (I’m not being xenophobic, I’m just not used to lots of people asking if I want help finding things (try shopping in Reading on a Saturday afternoon).

Anyone who has tried to paste data in a filtered Excel spreadsheet knows this.  Excel will also paste the data into the hidden (filtered out) cells.  It obviously thinks it’s being helpful but it’s really not.

There have been many suggested workarounds but it comes down to using Paste into Excel Visible Fields or a Paste into Excel Visible fields only with code.

There are two solutions that we use.  If you’re running Excel 2013 or above, you can utilise Flashfill.  For earlier versions, you might be able to use the Fill function..

Let’s look at Fill first.  Here is our example sheet:

Excel Paste into Visible cells only

 

A nice simple table with numbers in column B, whether they are odd or even in column A and the square of the number in column C.  What I would like to do is filter on odd numbers (because I am a little odd), copy the square and paste those into the new column D.

Let’s try to do that in the most obvious way and see what happens.

Filter the table to show only odd numbers.  Select all the squares in column C and copy.

Excel Paste into Visible cells only

 

Click in cell D2, right-click and select Paste Values.  But wait!  Only half of the values are shown.  That’s because Excel is being over-helpful and pasting into the hidden, filtered-out rows as well as the visible rows.  It would be lovely if there was a ‘Paste Values into Visible Cells’ option but you’ve already spent an hour searching the internet to discover there just isn’t.

 

Excel Paste into Visible cells only

 

If we clear the filter, we can see exactly that behaviour.  Our five selected cells have been pasted into the interim rows.

Excel Paste into Visible cells only

 

 

 

 

 

 

Using Fill to Successfully Paste into Visible Cells Only

Let’s go back to our filtered table.  However this time select the cells and the column next to it.

Excel Paste into Visible cells only

Now go up to the ribbon (Home tab) and click Fill and Fill Right.  Obviously if your destination column is to the left then feel free to hit Fill Left instead.

Excel Paste into Visible cells only

 

And voila, unlike the previous attempt, we are seeing all five desired values.

 

Excel Paste into Visible cells only

 

And just to be sure, let’s clear the filter condition to make sure nothing has been copied into the hidden rows.

 

Excel Paste into Visible cells only

 

Bingo.  We have our desired outcome.  Obviously this only works in the same sheet and if your columns are adjacent left or right to the cells you wish to copy.  If there are columns in between, you can hide those columns and this method will still work; Excel doesn’t paste into hidden columns in the same way it pastes into hidden rows.  In the screenshot below, I moved column A between the source column and the destination.  I filtered on Odd numbers in the same way, then hid column C.  Select Columns B and D and use the Fill Right method and as the screenshot works, once I unhide column C and clear the filter, everything still works out ok.

 

Excel Paste into Visible cells only

 

Flashfill Will Only Update Visible Cells

In Excel 2013, we have the lovely Flashfill feature which we blogged about previously.  Flashfill will also help but it’s not relevant for Excel versions earlier than 2013 (or Office 365 ProPlus if you ‘re in the cloud).

You can filter on odd numbers, type 1 in the first cell of the destination column, type 9 in the next cell down, hit Enter and then CTRL + E to force Flashfill to take over.  All the desired cells will be copied and if you clear the filter condition, you’ll see that the hidden rows haven’t been touched.  This is why we love Flashfill!

If you’d like more hints and tips, subscribe to our newsletter or better still, sign-up for one of our courses.