Mehedi Hasan
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.
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 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.
- 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.
Read More: How to Fill Blank Cells with Formula in Excel (2 Easy Methods)
Similar Readings
- Fill Blank Cells with Text in Excel (3 Effective Ways)
- How to Fill Empty Cells with Default Value in Excel
- Fill Blank Cells with 0 in Excel (3 Methods)
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 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 below with the changes we have made in the Power Query Editor.
Read More: How to Fill Empty Cells with Last Value in Excel (4 Handy 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.
- 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.
Read More: How to Fill Blank Cells with Value Above in Excel VBA (3 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.
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.
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.
