Taking the explanation from "Excel Outside the Box", by Bob Umlas - Microsoft Excel MVP, Bill looks at Pasting Data while managing to skip Blank Cells. Episode #1559 shows the technique that Bob covers - so check out today's episode and then check out Bob Umals' book, "Excel Outside the Box: Unbelievable Excel Techniques"!
Think that you know everything that there is to know about Microsoft Excel? Guess again. If you're ready for the next level of Excel, then "Excel Outside the Box" is for you! 'Outside the Box' is an in-depth look at 54 amazing techniques from Excel MVP Bob Umlas. Excel Outside the Box
Think that you know everything that there is to know about Microsoft Excel? Guess again. If you're ready for the next level of Excel, then "Excel Outside the Box" is for you! 'Outside the Box' is an in-depth look at 54 amazing techniques from Excel MVP Bob Umlas. Excel Outside the Box
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast episode 1559 – “Paste special skip blanks”.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today I want to talk about the skip blanks feature in paste special.
Now the only guy I've seen who can actually explain this is Bob Umlas.
Bob has written a great book “Excel outside the box” available on MrExcel.com, all kinds of great cool tips and tricks.
If you search for, for this paste special skip blanks you get a lot of people telling you that it does a lot of things that it doesn't do.
And they say: “Oh, it will copy only the values from here and paste them to a nice small ranch.” No, not true.
Bob pointed out and and really the only place that I can think of that it's useful if you have a price list here are some products here's the prices and your manager comes along and being a manager right, he just marks the ones that are going to change.
He doesn't mark the other ones, he says now: “Hey, go update those.” Oh geez, what are you going to do, you're gonna copy and paste each individual one.
No, we can use this trick.
So I select the whole range, it has to be the same size and shape as where I'm pasting to, I Ctrl C to copy and then I select the range where I'm going to update.
Now we paste, Paste Special and when I choose Skip Blanks what is really saying is if there was a blank in the original range do not overwrite that same corresponding cell in the target range.
So we click OK and the 2 2 2 2 2 2 3 3 3 are the only ones that are updated.
Everything else stays there.
So this is not a way to take a bunch of data with blanks and and consolidate it down, doesn't do that.
So, since I stole this trick from Bob, plug for his book “Excel outside the box” all kinds of excel guru types of things.
Well I want to thank Bob.
See you next time at another netcast, MrExcel.
Learn Excel from MrExcel podcast episode 1559 – “Paste special skip blanks”.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today I want to talk about the skip blanks feature in paste special.
Now the only guy I've seen who can actually explain this is Bob Umlas.
Bob has written a great book “Excel outside the box” available on MrExcel.com, all kinds of great cool tips and tricks.
If you search for, for this paste special skip blanks you get a lot of people telling you that it does a lot of things that it doesn't do.
And they say: “Oh, it will copy only the values from here and paste them to a nice small ranch.” No, not true.
Bob pointed out and and really the only place that I can think of that it's useful if you have a price list here are some products here's the prices and your manager comes along and being a manager right, he just marks the ones that are going to change.
He doesn't mark the other ones, he says now: “Hey, go update those.” Oh geez, what are you going to do, you're gonna copy and paste each individual one.
No, we can use this trick.
So I select the whole range, it has to be the same size and shape as where I'm pasting to, I Ctrl C to copy and then I select the range where I'm going to update.
Now we paste, Paste Special and when I choose Skip Blanks what is really saying is if there was a blank in the original range do not overwrite that same corresponding cell in the target range.
So we click OK and the 2 2 2 2 2 2 3 3 3 are the only ones that are updated.
Everything else stays there.
So this is not a way to take a bunch of data with blanks and and consolidate it down, doesn't do that.
So, since I stole this trick from Bob, plug for his book “Excel outside the box” all kinds of excel guru types of things.
Well I want to thank Bob.
See you next time at another netcast, MrExcel.
