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

Let’s say we have a dataset with some empty or blank cells. Using a dataset with blank cells for calculations is not practical. We must, therefore, fill them with equivalent values. Moreover, there are various helpful ways in Microsoft Excel to enter the above value into blank cells. In this article, I will show you four easy methods to fill blank cells with value below in Excel.

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

In this article, I have discussed four simple approaches to filling blank cells with value below in Excel. It includes combining the Find and Replace command, merging the Go To Special command with a formula, using Power Query, and applying VBA. For the purpose of demonstration, I have used the following sample dataset.

Method 1: Combining Find and Replace Command

First and foremost, we can also use the Find option to find empty cells and have the value from the cell below automatically fill them in. Moreover, we can use the Find option to locate the cells in a dataset that have the same value. Either blank cells or cells with the same type of value can be found. So, let’s go through the steps to perform this method.

Steps:

• Firstly, select the data range.

• Secondly, press CTRL+F to open the Find and Replace dialog box.
• Now, keep the Find what: box blank and click on Find All.

• Press CTRL+A from the keyboard. However, this will select all the blanks.
• After that, click on the Close button.

• Now, blank cells in our dataset will remain selected.

• Here, on your keyboard press the = (equal to sign). However, this action will enter an “equal to” sign to the active cell only by default.

• After that, press the “down-arrow” key from the keyboard and this action selects just below the active cell.

• After that, hold the Ctrl key and press Enter.
• Finally, the above actions will insert the same formula for all the selected cells. So, you can find the blank cells are now filled with the value of the below cell.

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

Method 2: Merging Go To Special Command with a Formula

Moreover, you can use a formula and the “Go To Special” option in Excel to automatically fill up empty cells with the value below. with the ‘Go To Special‘ feature. In Microsoft Excel, you can quickly choose cells that meet a set of requirements. However, follow the steps below to know how this process works.

Steps:

• First, select the range of data where you want to fill the blank cells.

• Secondly, from the Home tab, Click on the “Find and Select” option from the Editing section of the ribbon.
• Thirdly, from the drop-down menu select the option “Go To Special”. Similarly, you can avoid this by pressing F5 directly from the keyboard.

• Then, check the option Blanks from the dialogue box and click on OK.

• The above-mentioned command will select all the blank cells.

• From the keyboard, press “=” and you will notice an equal sign in the active cell.
• After that, tap the “down-arrow” key from the keyboard which selects immediately below the active cell.

• Similarly, hold the Ctrl key and press Enter.
• Lastly, you can see that the blank cells are now filled with the value of the below cell.

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

Other than the two methods, Excel Power Query gives the opportunity to fill in the blanks from the cell below very easily. It is one of the quickest methods. To use this method, you must convert the dataset into a table. However, I will use the same dataset here. Follow the steps below.

Steps:

• Firstly, select the complete dataset.

• Secondly, go to the Insert tab and select Table.

• A Create Table window will open.
• Now, check the ‘My table has headers’ and click OK to proceed.

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

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

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

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

• Lastly, select Fill and Up respectively from the drop-down menu.

• Again, select Close & Load.

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

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

The last method includes applying VBA Macros. You can use VBA Macros to fill the blank cells with the value below in Excel. Though it might take a few minutes to run the code, this method works pretty well for lengthy datasets. Hence, follow the steps below in order to complete the operation.

Steps:

• Similarly, select the whole dataset.

• In the first place, right-click on the worksheet and select View Code.

• Now, write down the following code to the VBA window and press the run button twice.

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

• Finally, it will fill in the blanks like below.

Using VBA to Fill Blank Cells with Value Above in Excel

Additionally, Excel VBA is another option to fill down empty cells with the value above. VBA stands for Visual Basic for Applications. Similarly, you can use not only VBA but also all other 3 methods to fill blank cells with the values above in Excel. For the purpose of demonstration, I have used the same dataset after making some adjustments.

Steps:

• Initially, select the complete dataset.

• Secondly, right-click on the worksheet and select View Code.

• Thirdly, write the following code to 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

• Finally, it will fill in the blanks like below.

Download Practice Workbook

You can download the workbook used for the demonstration from the download link below.

Conclusion

These are all the steps you can follow to fill blank cells with value in Excel. Hopefully, you can now easily create the needed adjustments. I sincerely hope you learned something and enjoyed this guide. Please let us know in the comments section below if you have any queries or recommendations.

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 Fill Blank Cells | Blank Cells in Excel | Excel Cells | Learn Excel