MS Excel: Paste, But Skip the Blanks Please

Posted By On July 2, 2010 @ 10:27 AM In MS Excel | Comments Disabled

Have you ever had some data that needs to be pasted in around some already existing information?

Maybe you have a setup something like this:



Then let’s suppose that you need to produce a report where the commission rate is replaced by the actual commission amount. Unfortunately, in the example above, you just happen to have that calculated below the location where your report needs it to be.



If you take a look the two different sets of data could fit together nicely, too bad they’re in separate locations and the commission rate is in the place where we need the amount to go…

Let’s consider our options -

We know that if we just select the entire range of cells with the commission amounts and then paste them into the original area that the blank cells will cause a problem when they replace the sales amounts.

We could choose to select each column of amounts separately and then paste them into the correct locations but that takes way too much time.

Oh yeah, and one more thing to consider we need to remember that if the commission amounts are calculated from formulas (which these are) then a simple copy / paste will cause problems with the cell references so we’re on to a Paste Special to paste values only.

With all that in mind I’d like to suggest that we use the whole Paste Special thing in a way that we don’t normally do – let’s get it to paste the values (so the cell references are not a problem) and let’s get it to paste so that it leaves alone any data that isn’t represented in our copied region.

(If necessary, translate that last bit as “if the copied cell was blank then paste absolutely nothing”.)

Sounds like a good plan to me – one copy/paste with all areas of concern handled giving the results we need.

– Start by selecting and copying the range of cells with the data to be pasted. (The commission amounts in this example.)

– Now, when you go to paste we need the Paste Special – not a regular paste. (In older versions of Excel you’ll find it in the Edit menu – in Excel 2007 it’s in the bottom half of the Paste button on the Home tab of the Ribbon.)



In the Paste Special dialog box select Values (takes care of the formula problem) and then at the bottom select Skip blanks (sets the paste so that copied cells with no data aren’t included in the paste).

Click OK.

Voila! The data replacement for commission occurred without losing the sales totals.

This whole Skip blanks thing – I really like it. Depending upon the situation it allows for a single copy / paste where previously it took repetitious work to get the job done.

~April

Article printed from Worldstart's Tech Tips Newsletter: http://www.worldstart.com

URL to article: http://www.worldstart.com/ms-excel-paste-but-skip-the-blanks-please/

Click here to print.