Skip Blanks

 

Use the 'Paste Special Skip Blanks' option and Excel will not overwrite existing values with blanks. To create a formula that skips blank cells, use IF and ISBLANK.

1. Select the range B1:B12.

2. Right click, and then click Copy.

Right Click Copy

3. Select cell A1.

4. Right click, and then click Paste Special.

5. Check Skip Blanks.

Check Skip Blanks

6. Click OK.

Paste Special Skip Blanks in Excel

You can use IF and ISBLANK to create a formula that skips blank cells.

7. For example, the formula in cell B1 below adds the value 2 to the value in cell A1.

Excel Formula

8. Select cell B1 and drag the fill handle down to cell B12. The fill handle is the little green box at the lower right of a selected cell.

Copy Formula

Conclusion: blanks are not automatically skipped when creating formulas (see cell B5 and cell B9).

9. Select cell B1 and replace the formula =A1+2 with =IF(ISBLANK(A1),"",A1+2). Next, use the fill handle again to quickly copy this new formula down the column.

Excel Formula to Skip Blank Cells

Explanation: this formula skips blank cells. ISBLANK returns TRUE for blank cells. In this case, the formula returns an empty string (two double quotes with nothing in between). Otherwise, it returns the formula result.