Home > Skills > Data Entry > Skip Blanks Skip Blank Cells When Pasting in ExcelIf 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 ExcelUsually, 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 InformationIn 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 DataIn the screen shot below, you can see data that is similar to the two files that I was working with.
|
Keep Old DataIn 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 DataNext, 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 CommandTo 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 BoxWhen the Paste Special window opens, follow these steps:
|
Data Pasted With Skip BlanksAfter you close the Paste Special dialog box, the coloured cells show where the new data was pasted, over the original data.
|
Download the Sample FileSkip Blanks: Get the 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.More Data Entry Tutorials |
Last updated: May 14, 2023 11:53 AM