Excel Paste Special has an option called “Skip blanks.”
The skip blanks allows users to copy entire sections of data (even with blank columns) and paste the data within another area without overwriting the existing data with blanks.
See the picture above for a visual explanation.
Let’s say we have data for each month of the current year. However, someone made a mistake during data entry and now we have imported new data for 3 of the months. These months are not consecutive, so there are blank columns. If we simply copied and pasted all of the columns, the blank column would overwrite the existing data. Or, we could copy each column (or consecutive columns), paste, and then repeat with the other consecutive columns. If you have large amounts of data and many columns, you can see how this would be time consuming.
Instead, let’s do this:
Highlight all of the new data (including the blank columns of data).
Next, click “Copy” or use the CTRL + C. (or right-click and choose “copy”)
Move the cursor to the insertion point, right-click and choose “Paste Special.”
On the Paste Special window, choose “Values” and “Skip blanks.”
Notice the data pastes over only those fields in which there was data to be pasted. The blank columns did not overwrite the existing data.