Today, I want to show you how to use the Skip Blanks option in the Paste Special dialog box. You've all probably seen it there, but you've never used it.
Here it is in the expertly placed square box.
I know why you've never used it. Skip blanks sounds dangerous. It sounds like you're entire spreadsheet will shift to the left or some other nonsense. Nooooo thank you.
But I'm here to say, it's actually a useful little feature that can save some time. Here's an example.
I've got this dataset that shows Actual and Forecast (B3:I6). My manager wants to see the difference of the two in the Forecast columns.
So I've entered some formulas starting in C8. The formula bar shows that I'm simply calculating the difference.
At this point, a lesser Excel user would copy each set of numbers individually and do a paste-special-values into each column.
But I'm far too an important person to inundate myself with all that copying and pasting. So I'll copy the entire range here.
Then I'll call up the Paste Special dialog box and click Values. I'll also click the Skip Blanks option.
Booyah. Excel pastes over only the areas where there was data in the copied range. In other words, it ignored the blanks in the copied range.
There you have it – Skip Blanks. Another feature demystified.
I think I'll reward myself with a disgusting breakfast sandwich from McDonalds.
It's easy if you remember that "Skip Blanks" applied to the source range, not the target where it will be pasted.
ps- I recommend Bacon Egg & Cheese McGriddles. Yum!
Sweet!
I've never used this mystified feature before. and now here it is: Another feature demystified.