How to Fill Blank Cells with Value Below in Excel (4 Easy Ways)

We’ll use the following sample dataset, with empty cells that will be filled by copying the first non-empty value from below.

Method 1 – Using the Find Command

Steps:

• Select the data range.

• Press Ctrl + F to open the Find and Replace dialog box.
• Keep the Find what: box blank and click on Find All.

• Press Ctrl + A from the keyboard. This will select all the blanks.
• Click on the Close button.

• Blank cells in our dataset will remain selected.

• Press the = (equal to sign).

• Press the down arrow key from the keyboard.

• Press Ctrl + Enter.
• This fills blank cells with the value in the cell below it.

Read More: How to Find and Replace Blank Cells in Excel

Method 2 – Merging the Go To Special Command with a Formula

Steps:

• Select the dataset.

• From the Home tab, click on the Find and Select option from the Editing section.
• From the drop-down menu, select the option Go To Special. You can also use the F5 keyboard shortcut.

• Check the option Blanks in the dialog box and click on OK.

• This will select all blank cells.

• Insert an equals sign (=) and press the down arrow key.

• Hold the Ctrl key and press Enter.

Method 3 – Using Power Query to Fill Blank Cells with the Value Below in Excel

Steps:

• Select the complete dataset.

• Go to the Insert tab and select Table.

• A Create Table window will open.
• Check My table has headers and click OK.

• The dataset will turn into a table like the one below.

• Select all cells from the table.
• From the Data tab, select From Table/Range.

• After that, a Power Query Editor window will appear.

• Right-click the header of the columns that contain blank cells.

• Select Fill and Up from the drop-down menu.

• Select Close & Load.

• A new sheet will appear containing a table like the one below with the changes made in the Power Query Editor.

Method 4 – Applying VBA to Fill Blank Cells with the Value Below in Excel

Steps:

• Select the whole dataset.

• Right-click on the worksheet name and select View Code.

• Insert the following code to the VBA window and press the Run button.

Sub Fill_Blank_Cells_From_Below()
'ExcelDemy Publications
For Each Cabin In Selection
'Applying If loop
    If Cabin.Value = "" Then
        Cabin.Value = Cabin.Offset(1, 0).Value
'Exiting If loop
    End If
Next Cabin
End Sub

• This will fill in the blanks.

Using VBA to Fill Blank Cells with the Value Above in Excel

We have used the same dataset after making some adjustments.

Steps:

• Select the complete dataset.

• Right-click on the worksheet and select View Code.

• Insert the following code in the VBA window and press the Run button.

Sub Fill_Blank_Cells_with_Value_Above()
'ExcelDemy Publications
For Each Cabin In Selection
If Cabin.Value = "" Then
Cabin.Value = Cabin.Offset(-1, 0).Value
End If
Next Cabin
End Sub

• This will fill in the blanks.

Download the Practice Workbook

Related Articles

• How to Fill Blank Cells with 0 in Excel
• Fill Blank Cells with Dash in Excel
• How to Fill Blank Cells with Color in Excel
• How to Fill Blank Cells with Value from Left in Excel

<< Go Back to Blank Cells in Excel | Excel Cells | Learn Excel