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

In this tutorial, we will show you how to autofill blank cells in excel with a value above. Suppose we have a dataset that contains blank/empty cells. It’s not convenient to use a dataset with blank cells for calculation. So, we need to fill them with relevant values. In this article, we’ll use the value from the above cells to fill in the blank cells.

Download Practice Workbook

You can download the practice workbook from here.

5 Easy Ways to Autofill Blank Cells in Excel with Value Above

Throughout this tutorial, we will show you 5 easy ways to autofill blank cells in excel with a value above. The most important and crucial part of this entire process is to select blank/empty cells. First, we will select the blank cells. After selecting blank cells we can follow multiple ways to autofill blank cells with a value above.

To make you understand better we will use the same dataset for all the methods. We have provided a screenshot of our dataset that we will use in this article. The dataset contains “Club Name”, “Player Name” playing in particular clubs, and their “Playing Position”. If we notice we will see that the dataset doesn’t repeat any data. We can see the blank cells in the dataset. The blank cells will have the same values as the cell above them.

1. ‘Go To Special’ Option with Formula to Autofill Blank Cells in Excel with Value Above

First and foremost, we will use the “Go To Special” option with a formula to autofill blank cells in excel with a value above. In Microsoft Excel with the ‘Go To Special’ option, we can rapidly select cells that fulfill a set of criteria.

So, let’s see the steps to perform this method.

STEPS:

• Firstly, select the data range (B4:D13).
• Secondly, go to the Home 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”.
• In short: select (B4:D13) > Home > Find and Select > Go To Special

• Now, a new dialogue box will appear.
• Check the option Blanks from the dialogue box and click on OK.

• The above command will select all the blank cells.

NOTE: Also, we can use a keyboard shortcut to perform the above methods of selecting blank cells from a data range.

STEPS:

• Firstly, press Ctrl + G to open the “Go-To” dialogue box.
• Secondly, press Alt + S to open the “Go To Special” dialogue box.
• Thirdly, press Alt + K to check the option Blanks.
• Next, press the = (equal to sign) key on your keyboard.
• By default, this will enter equal to sign to the active cell only.
• Then, tap the “up-arrow” key from the keyboard. This action selects immediately above the active cell.

• After that, hold the Ctrl key and press Enter.
• Lastly, the above command will apply the same formula for all the selected cells. So, we can see that the blank cells are filled now with the value of the above cell.

Read More: How to Fill Blank Cells with 0 in Excel (3 Methods)

2. Use Find Option with Formula to Autofill Blank Cells with Value Above in Excel

Other than using the “Go To Special” option we can use the Find option also to find blank cells and autofill them with the value of the above cell. The Find option allows us to find the cells from a dataset that contains the same value. You can find either blank cells or other cells that have the same type of value.

So, let’s go through the steps to perform this method.

STEPS:

• First, select the data range (B4:D13).
• Next, go to the Home Click on the “Find and Select” option from the Editing section of the ribbon.
• Then, from the drop-down menu select the option Find.
• In short: select (B4:D13) > Home > Find and Select > Find

• Now, a new dialogue box named “Find and Replace” will appear.
• After that, in that box set the following values for mentioned options:

Find what: Keep this box blank.

Within: Select the option Sheet.

Search: Select the option “By Rows”.

Look in: Select the option Values.

• Click on “Find All”.

• So, we can see a list of all blank cells in the range we selected (B4:E9).

• Press Ctrl + A to select all the blank cells.

• Close the “Find and Replace” dialogue box. Blank cells in our dataset will remain selected.

• Here, on your keyboard press the = (equal to sign).
• This action will enter equal to sign to the active cell only by default.

• Press the “up-arrow” key from the keyboard. This action selects just above 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, we can see that the blank cells are filled now with the value of the above cell.

Read More: How to Find and Replace Blank Cells in Excel (4 Methods)

3. Fill Down Blank Cells Automatically with Excel Power Query

Excel “Power Query” comes with a built-in feature that will allow you to autofill blank cells in excel with the value above with a single click. In this example, we will autofill the blank cells of the following dataset with the value above.

Let’s see step-by-step how we can use “Power Query” in this method.

STEPS:

• In the beginning, select the data range (B4:D13).
• Next, go to the Insert tab and select the option Table from the excel ribbon.
• In short: select data range (B4:D13) > Insert > Table

• Then, a new dialogue box will open with the selected range in the input box.
• Check the option “My table has headers” from that box and press OK.

• The above action will convert our selected data range into table format.

• Now, select the table range (B4:D13).
• Go to the Data Select the option “From Table/ Range” from the excel ribbon.

• A new window named “Power Query Editor” will open. We can see our previous table in that editor. If you notice you will observe that the “Power Query Editor” shows null values for blank cells.

• After that, hold the Ctrl key and select the columns that you want to fill down. We are selecting the first column and third column.
• Right-click on any of the columns and select the option Down from the Fill option.

• So, the above command fills all the blank cells with the value above.

• Go to the File tab and select the option “Close & Load”.

• Finally, the above action will return a new table named “Table1”. We can see that all the blank cells are filled in the new table.

Read More: How to Remove Blank Cells from a Range in Excel (9 Methods)

Similar Readings:

• Excel VBA: Find the Next Empty Cell in Range (4 Examples)
• How to Apply Conditional Formatting in Excel If Another Cell Is Blank
• Remove Blanks from List Using Formula in Excel (4 Methods)
• How to Ignore Blank Cells in Range in Excel (8 Ways)
• If Cell is Blank Then Show 0 in Excel (4 Ways)

4. Autofill Blank Cells in Excel with Value Above Using Nested LOOKUP Formula

Furthermore, we can use the nested LOOKUP formula to fill blank cells with the value above. We will fill-up the blank cells of the following dataset using the nested LOOKUP formula.

So, to perform this action do the following steps.

STEPS:

• Firstly, select a random cell F4. Insert the following formula in that cell:

• Now, press Enter.
• The above action will show the data of column B in column F by filling up the blank cells with the above value.

• Secondly, select the data range (F4:F13).
• Then, move the mouse cursor to the bottom right corner of cell F13. We can see a (plus) + sign Fill Handle.

• Thirdly, click the plus (+) sign and drag the Fill Handle down horizontally to H13 to duplicate the formula from column F. To get the same result, we can double-click on the plus (+).

• Free the mouse click.
• Lastly, we get all the blank cells in cell range (B4:D13) filled with the value above in range (F4:H13).
• The formula that it will generate for the third column:

🔎 How Does the Formula Work?

• ROW(B4:B13)),B4:B13): This part converts the range (B4:B13) in the form of a vector to get the desired result.
• IF(LEN(B4:B13): Combination of the IF function and the LEN function take the range (B4:B13).
• ROW(B4:B13): Selects the lookup_value in range (B4:B13).
• LOOKUP(ROW(B4:B13),IF(LEN(B4:B13),ROW(B4:B13)),B4:B13): Returns the value of cell range (B4:B13) in a selected range.

Read More: How to Deal with Blank Cells That Are Not Really Blank in Excel (4 Ways)

5. VBA Code to Autofill Blank Cells with Value Above in Excel

Suppose, you want to autofill blank cells with the value above in excel in a superfast way or just with a single click. We can do this easily using a simple VBA (visual basic for application) code. We will apply VBA code to autofill the blank cells with the above value of the following dataset.

So, let’s look at the steps of doing this method.

STEPS:

• First, select the data range (B4:D13).

• Next, right-click on the active sheet. Select the option “View Code”.

• So, the above command opens a blank VBA.
• Insert the following code in the blank module:

Sub Autofill_Blank_Cells()
For Each cell In Selection
If cell.Value = "" Then
cell.Value = cell.Offset(-1, 0).Value
End If
Next cell
End Sub

• Now click on Run or press F5 to run the code.

• Finally, we can see that all the blank cells in our data range are now filled with the value of the above cells.

Read More: VBA to Count Blank Cells in Range in Excel (3 Methods)

Conclusion

Therefore, this tutorial will show you how to autofill blank cells in excel with a value above. Use the practice worksheet that comes with this article to put your skills to the test. If you have any questions, please leave a comment below. Our team will make every effort to respond to you as quickly as possible. Keep an eye out for more creative Microsoft Excel solutions in the future.

Related Articles

• How to Skip Blank Rows Using Formula in Excel (8 Methods)
• If a Cell Is Blank then Copy Another Cell in Excel (3 Methods)
• How to Make Empty Cells Blank in Excel (3 Methods)
• Formula to Return Blank Cell instead of Zero in Excel (With 5 Alternatives)
• How to Remove Blank Cells Using Formula in Excel (7 Methods)