How to Skip Blank Cells in Excel
Use the Paste Special – Skip Blanks option to prevent Excel from replacing existing data with empty cells. In this tutorial, we will learn how to skip blank cells in Excel. You can create a formula that ignores empty cells using the IF and ISBLANK functions.
1. Select the range B1:B12.
2. Right click, and then click Copy.
3. Select cell A1.
4. Right click, and then click Paste Special.
5. Check Skip Blanks.
6. Click OK.
Formula that Skips Blank Cells
Create a formula that ignores empty cells using the IF and ISBLANK functions.
1. In this case, cell B1 contains a formula that adds 2 to the value in cell A1.
2. Select cell B1, then hold the fill handle and drag down to B12. The fill handle is the small green square in the bottom-right corner of a selected cell.
Conclusion: Notice the value in cell B5 and B9, the formula puts a value of 2 in these cells. Formulas do not skip blank cells automatically.
3 Change the formula in B1 to =IF(ISBLANK(A1), “”, A1+2). Now, use the fill handle to copy the formula into the other cells in the column.
Explanation: This formula skips blank cells. ISBLANK returns TRUE for blank cells. In this situation, the formula shows an empty value (two double quotes containing nothing). Otherwise, it returns the formula result.
6/12 Completed! Learn much more about ranges ➝
Next Chapter: Formulas and Functions