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.

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


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.

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

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

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

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

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

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

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

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.

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

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

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

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

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

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

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

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

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

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

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

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

Fill Down Blank Cells Automatically with Excel Power Query

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

Fill Down Blank Cells Automatically with Excel Power Query

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

Fill Down Blank Cells Automatically with Excel Power Query

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

Fill Down Blank Cells Automatically with Excel Power Query

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

Fill Down Blank Cells Automatically with Excel Power Query

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

Fill Down Blank Cells Automatically with Excel Power Query

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


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.

Autofill Blank Cells in Excel with Value Above Using 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:
=LOOKUP(ROW(B4:B13),IF(LEN(B4:B13),ROW(B4:B13)),B4:B13)

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

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

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

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

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

  • 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:
=LOOKUP(ROW(D4:D13),IF(LEN(D4:D13),ROW(D4:D13)),D4:D13)

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

VBA Code to Autofill Blank Cells with Value Above in Excel

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

STEPS:

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

VBA Code to Autofill Blank Cells with Value Above in Excel

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

VBA Code to Autofill Blank Cells with Value Above in Excel

  • 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

Mukesh Dipto

Mukesh Dipto

Hi there! I am Mukesh Dipto. I graduated with a bachelor's degree in engineering. Currently, I am working as a technical content writer in ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include sports and meeting new people. I also enjoy sports. My favorite sports are football (to watch and play) and Badminton (play).

2 Comments
  1. Good day

    I’m struggling with a spreadsheet that I’m trying to design to help stop students coming late. On the first sheet all the learners’ names are displayed and a button (or input) used to activate the now() function to display the specific date and time, they came late. What I actually wanted is for all the late comers to be displayed in the second sheet with all their dates with times they’ve been coming late in the rows their names are displayed. I was hoping you could help us with this issue. We are a small school with about 350 students, with about 30 late comers. Everything after 07:30 is classified as late

  2. 1. First, use the now() function to extract time from the date in another column: copy the date, press Ctrl+1, select Custom, and then enter h:mm AM/PM in the Type field. To copy the date you have to use the formula:
    = cell reference

    2. After getting the Time column, you need to use the IF function to find the Present and Late column. The formula will be:
    =IF(Time column cell>$absolute reference cell,”Present”,”Late”)
    In this case, 7:30 AM would be the absolute cell reference.

    3. In order to find only the Late comers, you need to use the IF function. The formula will be
    =IF(Present and Late column cell=$absolute reference cell,”Late”, “”)

    4. Using Pivot Table, you can transfer the Present and Late Column to another sheet. Select the Insert tab, select Pivot Table, enter the whole table in the range option and finally select New Worksheet. You must select the rows or columns that you want to display in the pivot table after the PivotTable Fileds pane appears.

Leave a reply

ExcelDemy
Logo