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

Get FREE Advanced Excel Exercises with Solutions!

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.

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 Find and Replace Blank Cells in Excel


Method 2: Merging Go To Special Command with a Formula

Moreover, you can use a formula and theGo 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.

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.


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

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


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.


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


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


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mehedi Hasan
Mehedi Hasan

Mehedi Hasan, a BSc graduate in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, plays a pivotal role as an Excel & VBA Content Developer at ExcelDemy. Fueled by a profound passion for research and innovation, he actively engages with Excel. In his capacity, Mehedi not only adeptly tackles intricate challenges but also showcases enthusiasm and expertise in navigating tough situations with finesse, underscoring his unwavering dedication to consistently delivering exceptional and high-quality content. He... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo