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.


Download Practice Workbook

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


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

In this article, I have discussed four simple approaches to filling blank cells with value below in Excel. It includes combining Find and Replace command, merging 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.

Sample Dataset for How to Fill Blank Cells with Value Below in Excel


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.

Fill Blank Cells with Value Below in Excel by Combining Find and Replace Command

  • 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.

Find and Replace Dialog Box

  • 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 Fill Blank Cells with Value from Left in Excel (4 Suitable Ways)


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:

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

Merging Go To Special Command with a Formula to Fill Blank Cells with Value Below in Excel

  • 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.

Go to Special Dialog Box

  • 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.

Read More: How to Fill Empty Cells with Last Value in Excel (4 Handy Ways)


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.

How to Fill Blank Cells with Value Below in Excel by Using Power Query

  • 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 below.

Power Query Table to Fill Blank Cells with Value Below in Excel

  • 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.

Power Query Window

  • 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 below with the changes we have made in the Power Query Editor.

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

Read More: Fill Blank Cells with Text in Excel (3 Effective Ways)


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.

Applying VBA to Fill Blank Cells with Value Below in Excel

  • 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

VBA Code to Fill Blank Cells with Value Below in Excel

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

Read More: How to Fill Blank Cells with Formula in Excel (2 Easy Methods)


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.

Sample Dataset for How to Fill Blank Cells with Value Above in Excel by Using VBA

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

VBA Code to Fill Blank Cells with Value Above in Excel

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

Using VBA to Fill Blank Cells with Value Above in Excel

Read More: How to Autofill Blank Cells in Excel with Value Above (5 Easy Ways)


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.

For more information like this, visit Exceldemy.com.


Related Articles

Mehedi Hasan

Mehedi Hasan

Hi, I am Mehedi. I have completed my B.Sc. from Bangladesh University of Engineering and Technology. I have a strong interest in innovation and research in the field of Data Science and Machine Learning. Gradually, I now understand the value of Data Analysis and I am trying to learn everyday.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo