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
Get FREE Advanced Excel Exercises with Solutions!