Excel formula to fill in blank cells with value above / below

Here is a quick trick for selecting empty cells and then fill in blank cells with value above or below.

Selecting Empty Cells –

1. Pick the columns or row where you want to fill in blanks.
2. Press Ctrl + G or F5 to display the Go To dialog box.
3. Click on the Special button.
4. Select the Blanks radio button and click OK

Now, only the empty cells from the selected range are highlighted and ready for the next step.

Fill in blank cells with value above / below –

1. Press F2 or just place the cursor in the Formula bar to start entering the formula in the active cell.
2. Enter the equal sign(=) followed by the address of cells above or below you want for blank cells to fill.
3. The formula (=B2) shows that cell B3 will get the value from cell B2.
4. Press Ctrl + Enter to copy the formula to all the selected cells.

Alternatively, IF formula will help to get the same result –

=IF(B2="",D1,B2)