MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2020: Skip Blanks While Pasting


July 08, 2020 - by Bill Jelen

Excel Skip Blanks While Pasting. Photo Credit: Hope House Press - Leather Diary Studio at Unsplash.com

A mysterious part of the Paste Special dialog is the Skip Blanks feature. What does it do? Say that you have a list of existing values. In another column, you have updates for some of those values but not all of them. In the next figure, select D2:D10 and Copy.

There are numbers in B2:B10. You want to update four of the numbers, so you type replacement values in D2, D4, D7, and D10. Select the entire range D2:D10 and Ctrl+C to copy.

Select the original values in B2:B10. Do a Paste Special and select Skip Blanks.


In the Paste Special dialog, choose the box for Skip Blanks.

The 87 in D2 overwrites the 92 in B2, but Excel does not clear out the 51 in B3.

The four numbers are updated. But anything next to a blank cell remains untouched.

Thanks to Laura Lewis for suggesting the Skip Blanks trick.

Title Photo: Hope House Press - Leather Diary Studio at Unsplash.com


This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.


Bill Jelen is the author / co-author of
MrExcel 2020 - Seeing Excel Clearly

This is a 4th edition of MrExcel LX. Updates for 2020 include: Ask a question about your data, XLOOKUP, Power Query's Data Profiling tools, How Geography Data Types decide which Madison, A SEQUENCE example for descending 52 weeks, Exchange Rates support in Stock Data Types, How to collapse the Search box, How to leave effective feedback for Microsoft, How to post your worksheet to the MrExcel Board using XL2BB.