Bacon Bits:

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.

RELATED STUFF

1. Using Arrows to Edit a Named Range
2. Outta My Way Formula Thingy!
3. Creating Subtotals on Many Columns
4. Limiting Movement within Your Worksheet
5. Protect Worksheet Structure with Array Formulas
6. Special Characters are a Pain in the Asterisk
7. Copying VLOOKUPs Across Multiple Columns
8. Getting Rid of Sticky Click
9. Flipping a Range Upside Down
10. Using Percentages with Scroll Bars

• Previous Entry: Clearing Access ImportError Tables
• Next Entry: Egg and Bacon Pie