How to Fill Blank Cells with Value Above in Excel (4 Easy Methods)

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.

Excel Fill Blank Cells With Values Above: Sample Dataset

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.

Step 1:

  • Select the range of data where you want to fill the blank cells.

Selecting dataset to apply go to special

Step 2:

  • Go to the Home Tab > Editing group > Find & Select drop-down menu > Go To Special command.

Follow the picture below.

Fill Blank Cells with Value Above in Excel Using Go To Special (F5) and Formula

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.

Step 3: 

  • Choose Blanks from the Go To Special box > click OK.

Choosing blanks from Go To Special dialog box

As a result, you will find that the blank cells are selected accordingly.

Showing that the blanks are selected

Step 4:

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

Writing cell reference formula in the active cell

Step 5:

  • Afterward, press CTRL+ENTER.

You can see the result below.

Getting result by using formula

However, the result contains a copy of the formula. You have to convert them into values.

Step 6:

  • Select the range of data again and select Copy from the Context menu.

Selecting copy to copy the range of data

  • Clicking on the Copy will show the dotted line across the selected boundary.

Showing dotted lined boundary after copying

Step 7:

  • Next, you have to right-click again and select the arrow icon beside Paste Special.

A Drop-down menu will appear.

Choosing Paste Special to paste the values

Step 8:

  • Select Paste Values(V) as shown below.

Selecting paste only values pasting

Finally, the result will look like the following picture.

Final result of applying go to special

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


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.

Step 1:

  • Select the range of data.
  • Go to the Home tab > Editing group > Find & Select drop-down menu > Select the Find command.

Selecting Find from the editing option of the home tab

Step 2:

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

Finding out all blanks using Find All

Step 3:

  • Press CTRL+A from the keyboard. This will select all the blanks.
  • After that, click on Close.

Fill Blank Cells with Value Above Using Find & Replace and Formula

Step 4:

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

 Writing simple cell reference formula in the active cell

Step 5:

  • Press CTRL+ENTER from the keyboard.

Thus, you will find the result as shown.

Final result of using Find & Replace

Read More: How to Fill Blank Cells with Value from Left in Excel (4 Suitable Ways)


Similar Readings


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:

Step 1:

  • Select the whole data set.

Selecting data range for using nested lookup formula

Step 2:

  • Choose Table from the Insert tab.

You can also press the keyboard shortcut CTRL+T after selecting the whole data set.

Finding table from insert tab

Step 3:

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.

Checking the data range and headers

Your dataset will look like a table with headers having arrow icons as shown below.

Formed table using insert

Step 4:

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

Result for Product ID using nested LOOKUP formula

Step 5:

  • Repeat the process for column C using the following formula.
=LOOKUP(ROW(C4:C14), IF(LEN(C4:C14), ROW(C4:C14)), C4:C14)

Combine LOOKUP, ROW, IF & LEN Functions to Fill in Blank Cells with Above Value in Excel

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.

Step 6:

  • Change the format by selecting Short Date instead of General.

Follow the picture to find where to change.

Changing the format of the result of Date of Sales

Hence, we have produced the output with the exact values of the dataset.

Result of date of sales after changing the formatting

Step 7:

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

Final result of using nested LOOKUP formula for the whole selected data

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.

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


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.

Step 1:

  • Select the range of data and right-click on the name of the sheet.
  • Click on View Code from the Context menu.

Selected data and opening VBA window

As a result, The VBA window will open showing the General Window on it.

VBA window with General window being opened

Step 2:

  • 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

Use of VBA Macros to Fill in Blank Cells with Above Value in Excel

Step 3:

  • To run the code, you can press F5 from the keyboard.

Or, click on the green arrow in the tab of the VBA window.

Process of running the code

Consequently, the code will run, and you can see the result in the worksheet.

Result of using VBA Macro

Read More: How to Fill Blank Cells with Value Above in Excel VBA (3 Easy Methods)


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.


Conclusion

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.


Related Readings

Syeda Fahima Nazreen

Syeda Fahima Nazreen

Hello People! This is Syeda Fahima Nazreen. I have completed my Bachelors in Science in Electrical and Electronic Engineering. I love to do research and work anything related to technology which includes research and development. I feel great to share my knowledge with you people and your thoughts and opinions about my writing is highly appreciated by me.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo