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 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:
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.
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.
If we clear the filter, we can see exactly that behaviour. Our five selected cells have been pasted into the interim rows.
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.
And voila, unlike the previous attempt, we are seeing all five desired values.
And just to be sure, let’s clear the filter condition to make sure nothing has been copied into the hidden rows.
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.
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!