Skipping Blanks in Excel [Video]

Blanks in Excel can cause a lot of issues, but there is one instance where you can skip them and speed up copying and pasting.

The video of this post is shown at the bottom of the post. Examine the image below.

Skip_01

We want to populate column K with the values from columns B, E and H.

Notice that the codes listed are all the same, and in the same order. Also notice that there is one value per code.

You might think this would involve quite a number of copies and pastes to achieve our result.

However there is an option in the Paste Special dialog that can make this an easy task involving just three copies and three pastes.

The first is a normal copy and paste. Copy the range B2:B11 and paste to cell K2.

Then you copy the range E2:E11. Right click cell K2 and select Paste Special.

Skip_02

In the Paste Special Dialog tick the Skip blanks option and click OK.

skip_00

This will only paste cells that contain values. Normally blank cells will overwrite existing entries, but the Skip blanks option stops that from happening. Remember, our three listings only have one entry per code, so any existing entries won’t be overwritten. See the second paste result below.

Skip_03
To finish, copy the range H2:H11 and copy and select cell K2 and use the Paste Special > Skip blanks to complete the range.

Skip_04

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *