Syeda Fahima Nazreen
Microsoft Excel has several useful methods to fill blank cells with the value above. Today we will describe 4 instrumental methods to fill blank cells with the value above in Excel with examples and proper explanations.
Let’s consider the following dataset. The dataset contains the list of product IDs, sales dates, and sales numbers.
You can notice that the dataset has some blank cells. And we want to fill the blank cells with the value above the cell.
In the next four sections, we will demonstrate the use of four common Excel tools Go To Special or Find from the Editing option, nested LOOKUP formula, and the VBA Code to perform this task.
1. Using Excel Go To Special Tool to Fill Blank Cells with Value Above
You can use Go To Special and a simple formula to fill the blank cells with the value above them. Follow the steps below to know how this process works.
Steps:
- First of all, select the range of data where you want to fill the blank cells.
- Next, go to the Home Tab > Editing group > Find & Select drop-down menu > Go To Special command.
A dialog box named Go To Special appears.
- Choose Blanks from the Go To Special box > click OK.
- As a result, you will find that the blank cells are selected accordingly.
- From the keyboard, press “=” and you will notice an equal sign in the active cell.
- Write the formula as “=D5“.
=D5
Here, D5 is the reference of the cell above, with whose value you want to fill in the blank cells.
- Afterward, press CTRL+ENTER and you can see the result below.
However, the result contains a copy of the formula. You have to convert them into values. To do so:
- Copy the range of data by pressing CTRL+C.
- Next, you have to right-click again and select Paste Values(V) as shown below.
- Finally, the result will look like the following picture.
You can check that the cell has converted the formula (that you have applied) into value.
Similar Readings
2. Applying Find & Replace Command to Fill Blank Cells
Another is available for serving your purpose. You can use the Find & Replace option from the Home tab along with a similar formula as we’ve used in the previous method.
You need to follow the steps below for this.
Steps:
- First of all, select the range of data.
- Then, go to the Home tab > Editing group > Find & Select drop-down menu > Select the Find command.
- Here, a box will come up. Keep the Find what: box blank and click on Find All.
- Here, shift your cursor from the dialog box > place it on the worksheet, and press CTRL+A from the keyboard. This will select all the blanks.
- After that, click on Close to close the dialog box.
This will show the list of blanks in the selected range. For this dataset, the number of blanks found is 11.
- Now, press “=” from the keyboard, and an equal sign will automatically show up in the active cell.
- Then write the formula “=D13” in the active cell.
=D13
- Press CTRL+ENTER from the keyboard.
Thus, you will find the result as shown.
Read More: How to Find and Replace Blank Cells in Excel
3. Applying Nested LOOKUP Formula in Excel to Fill Blank Cells
Furthermore, you can use the Table command from the Insert tab and the nested LOOKUP formula to fill blank cells with the above value.
Steps:
- Firstly, select the whole data set > choose Table from the Insert tab.
- Then, the Create Table dialog box will open up and show the selected range of data.
- Here, mark My table has headers checkbox if not marked automatically and click OK.
Your dataset will look like a table with headers having arrow icons as shown below.
- Select a random column F and write the following nested formula for column B.
=LOOKUP(ROW(B4:B14), IF(LEN(B4:B14), ROW(B4:B14)), B4:B14)
The result will show the data of column B along with filling up the blanks with the above value.
- Repeat the process for column C using the following formula.
=LOOKUP(ROW(C4:C14), IF(LEN(C4:C14), ROW(C4:C14)), C4:C14)
Here, the values of Dates of Sales are different from the original dataset. It is because the Number Format is General by default. So we are definitely going to convert this into a suitable format.
- Change the format by selecting Short Date instead of General.
Follow the picture to find where to change.
Hence, we have produced the output with the exact values of the dataset.
- Repeating the formula for column D using the following formula.
=LOOKUP( ROW(D4:D14), IF(LEN(D4:D14),ROW(D4:D14)), D4:D14)
This will give the following result:
This method helps to have the original dataset and forms a new table to get the desired result.
Formula Breakdown
- Here, lookup_value takes the data we want to find out. Since we have multiple rows in our data set the ROW function is working here which takes the range of the column.
- lookup_vector is using the IF function nested with the LEN function and the ROW function. Both take the range of columns to create a vector form.
- result_vector is the result values taken in the form of a vector to get the desired result.
Similar Readings
4. Using Excel VBA to Fill Blank Cells with Value Above
You can use VBA Macros to fill the blank cells with the above value. Though it might take a few minutes to run the code, this method works pretty well for lengthy datasets.
Follow the steps below to execute VBA Macro to fill the blank cells with the value above.
Steps:
- Here, select the range of data and right-click on the name of the sheet.
- Next, click on View Code from the Context menu.
As a result, The VBA window will open showing the General Window on it.
- Write the following code in the General Window.
Code:
Sub FillCellFromAbove()
For Each cell In Selection
If cell.Value = "" Then
cell.Value = cell.Offset(-1, 0).Value
End If
Next cell
End Sub
- To run the code, you can press F5 from the keyboard.
Or, click on the green arrow in the tab of the VBA window.
- Consequently, the code will run, and you can see the result in the worksheet.
Things to Remember
- Select the range of data at the beginning before applying any of the above-mentioned methods.
- While filling all the blank rows using Method 1 or 2, don’t forget to press the CTRL key to enter the formula.
Download Practice Workbook
You can download the workbook from here.
Conclusion
The article explains four methods to fill blank cells with the value above in Excel. The methods use either a simple formula along with the Editing options in the Home tab or the nested LOOKUP formula. However, it also shows the use of VBA Macros to fill blanks with the above value for lengthy datasets. I hope the article has helped you to get the solution you wanted. Nevertheless, if you have any further questions related to the topic, feel free to leave a comment in the comment section.
Related Readings
- How to Fill Blank Cells with Value Above in Excel VBA
- How to Fill Blank Cells with Value Below in Excel
- How to Fill Empty Cells with Last Value in Excel
- Fill Blank Cells with Text in Excel
- Fill Blank Cells with Dash in Excel
- How to Fill Blank Cells with N/A in Excel
- How to Fill Blank Cells with Color in Excel
- How to Fill Blank Cells with Value from Left in Excel
