Home > Skills > Data Entry > Skip Blanks

Skip Blank Cells When Pasting in Excel

If there is missing data in the new data, and you want to keep the existing entries in the old data, use the Excel Skip Blanks feature when you copy and paste.

Skip Blanks When Pasting in Excel

Usually, if we're adding new data in Excel:

• we copy all of the new data, and paste it over the old data, to replace it
• or, we paste the new data directly below the old data, as an addition, rather than a replacement

In this example however, the new data was incomplete, and I didn't want to paste blank cells -- only the cells that contained data.

This 2-minute video shows how to copy and paste, without overwriting the original data with blank cells. There are written steps below the video.

Get Updated Information

In this example, I was working on a client's file, and we wanted to get any new information from an update file, and paste it into the current record.

Both files were set up with the same columns, but the update file only had a few of the fields filled in. We could have used a macro to loop through the fields, grab any new information, and paste it to the current record. However, that would be a slow way to update the records, especially in a large file.

Fortunately, there is a built-in feature in Excel, that will copy a block of cells, and only paste the cells that contain data.

Old and New Data

In the screen shot below, you can see data that is similar to the two files that I was working with.

• Old Records: At the top, you can see the current data, and the orange rows need to be updated.
• New Records: The Update file is shown at the bottom, and the green cells have some new data.

Keep Old Data

In the screen shot, I put red outlines a few locations where there are:

• blanks cells in the Update file
• valid data in the current record.

We DO NOT want to lose that existing data, by pasting in blank cells from the new information.

Overwrite With New Data

Next, I put green outlines in the 2 columns at the right, where there are:

• filled cells in the Update file
• updated numbers in the current record.

We DO want to overwrite that old data, by pasting in the latest numbers from the new information.

Use Paste Special Command

To paste the new data, without overwriting the existing data with blank cells, you can use the Paste Special command.

Follow these steps, to copy the new data, and skip blanks when pasting:

• First, select all of the green cells, where the new data is stored.
• Next, copy the selected green cells
  • Use the Ctrl + C shortcut
  • Or click the Copy command on the Excel Ribbon's Home tab
• Next, select the top left orange cell, where the copied data will be pasted
• On the Excel Ribbon, go to Home tab
• In the Clipboard group, click the arrow, below the Paste button
• At the bottom of the pop-up menu, click on the Paste Special command

Paste Special Dialog Box

When the Paste Special window opens, follow these steps:

• At the top, in the Paste section, leave the default setting - All
• Below that, in the Operation section, leave the default setting - Non
• At the bottom, add a check mark to the "Skip Blanks" option
• Finally, click the OK button, to paste the data, and to close the Paste Special dialog box

Data Pasted With Skip Blanks

After you close the Paste Special dialog box, the coloured cells show where the new data was pasted, over the original data.

• Where there were blank cells in the new data, there are still orange cells in the existing data
  • Those cells were not overwritten by the new data's blank cells
• Where there were filled cells in the new data, there are new green cells in the existing data
  • The old data was overwritten by the new data's filled cells

Download the Sample File

More Data Entry Tutorials

Data Entry Tips

Convert Text to Numbers

Increase Numbers With Paste Special