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

Get FREE Advanced Excel Exercises with Solutions!

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 fill blank cells in Excel with the Go To Special tool and formula.

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.

Excel Fill Blank Cells with Value Above

Note: 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“.

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

Excel Fill Blank Cells with Value Above

You can check that the cell has converted the formula (that you have applied) into value.

Excel Fill Blank Cells with Value Above


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 to use the Find & Replace command to fill blank cells.

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.

Excel Fill Blank Cells with Value Above

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.


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.

Excel Fill Blank Cells with Value Above

Note: You can also press the keyboard shortcut CTRL+T after selecting the whole data set.
  • 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.

Excel Fill Blank Cells with Value Above

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

Excel Fill Blank Cells with Value Above

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

Excel Fill Blank Cells with Value Above

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.

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.

Excel Fill Blank Cells with Value Above


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


<< 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.
Syeda Fahima Nazreen
Syeda Fahima Nazreen

SYEDA FAHIMA NAZREEN is an electrical & electronics engineer who loves exploring Excel and VBA programming. To her, programming is a time-saving tool for dealing with data, files, and the internet. She's skilled in Proteus, MATLAB, Multisim, AutoCAD, Jupiter Notebook, and MS Office, going beyond the basics. With a B.Sc in Electrical & Electronic Engineering from American International University, Bangladesh, she's shifted gears and now works as a content developer. In this role, she creates techy content exclusively... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo