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:
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.
Keep Old Data
In the screen shot, I put red outlines a few locations where there are:
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:
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:
Paste Special Dialog Box
When the Paste Special window opens, follow these steps:
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.
|sample file for pasting with Skip Blanks, to follow along with the video. The zipped file is in xlsx format, and does not contain any macros.
Last updated: December 6, 2023 2:27 PM