Microsoft Excel has several useful methods to fill blank cells with the value above. Of them, we will describe 4 instrumental methods in this article with examples and proper explanations.
Download Practice Workbook
You can download the workbook from here.
4 Useful Methods to Fill Blank Cells with Value Above in Excel
We are going to use the following sample dataset to demonstrate the four useful methods to fill blank cells with the value above in Excel.
The dataset contains the list of product IDs, dates of sales, and numbers of sales. 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 like Go To Special or Find from the Editing option, nested LOOKUP formula, and the VBA Macros to perform this task.
1. Fill Blank Cells with Value Above in Excel Using Go To Special (F5) and Formula
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.
- Select the range of data where you want to fill the blank cells.
- Go to the Home Tab > Editing group > Find & Select drop-down menu > Go To Special command.
Follow the picture below.
You can avoid this by pressing F5 directly from the keyboard. This will also take you to the Go To Special box.
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“.
Here, D5 is the reference of the cell above, with whose value you want to fill in the blank cells.
- Afterward, press CTRL+ENTER.
You can see the result below.
However, the result contains a copy of the formula. You have to convert them into values.
- Select the range of data again and select Copy from the Context menu.
- Clicking on the Copy will show the dotted line across the selected boundary.
- Next, you have to right-click again and select the arrow icon beside Paste Special.
A Drop-down menu will appear.
- Select Paste Values(V) as shown below.
Finally, the result will look like the following picture.
2. Fill Blank Cells with Value Above Using Find & Replace and Formula
Moreover, 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.
- Select the range of data.
- Go to the Home tab > Editing group > Find & Select drop-down menu > Select the Find command.
- A box will come up. Keep the Find what: box blank and click on Find All.
This will show the list of blanks in the selected range. For this dataset, the number of blanks found is 11.
- Press CTRL+A from the keyboard. This will select all the blanks.
- After that, click on Close.
- Press “=“from the keyboard and an equal sign will show up in the active cell automatically.
- Then write the formula “=D13” in the active cell.
- Press CTRL+ENTER from the keyboard.
Thus, you will find the result as shown.
- Fill Blank Cells with N/A in Excel (3 Easy Methods)
- Data Clean-Up Techniques: Fill Blank Cells in Excel (4 Ways)
- Fill Blank Cells with Text in Excel (3 Effective Ways)
3. Combine LOOKUP, ROW, IF & LEN Functions to Fill in Blank Cells with Above Value in Excel
Furthermore, you can use the Table command from the Insert tab and utilize the nested LOOKUP formula to fill blank cells with the value above.
For this, follow the steps given below:
- Select the whole data set.
- Choose Table from the Insert tab.
You can also press the keyboard shortcut CTRL+T after selecting the whole data set.
The Create Table dialog box will open up and show the selected range of data.
- Check if the data is selected properly.
- Mark My table has headers checkbox if not marked automatically.
- 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.
The Nested Formula Breakdown:
The syntax of the formula:
=LOOKUP(lookup_value, lookup_vector, [result_vector])
- 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.
4. Use of VBA Macros to Fill in Blank Cells with Above Value in Excel
The last method includes the VBA Macros. 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.
- Select the range of data and right-click on the name of the sheet.
- 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.
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
You have to select the range of data at the beginning before applying any of the above-mentioned methods. Evidently, the simple formulas in methods 1 and 2 will vary based on the active cell after selecting blanks.
The article explains four methods to fill blanks 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.
- How to Fill Blank Cells in Excel with Go To Special (With 3 Examples)
- Fill Blank Cells with Color in Excel (5 Methods)
- How to Fill Down Blanks in Excel (4 Quick Methods)
- Fill Blank Cells with Dash in Excel (3 Easy Ways)
- How to Fill Empty Cells with Default Value in Excel
- Remove Blank Cells Using Formula in Excel (7 Methods)
- How to Fill Blank Cells in Excel with Go To Special (With 3 Examples)