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

 

Let’s consider the following dataset. It contains the list of product IDs, sales dates, and sales numbers. Some of the cells have blank values, which we will fill in a few different ways.


Method 1 – Using Excel Go To Special Tool to Fill Blank Cells with the Value Above

Steps:

  • Select the range of data where you want to fill the blank cells.
  • Go to the Home tab and the Editing group, select the Find & Select drop-down menu and choose Go To Special.

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 and click OK.

  • This selects all blank cells.

  • Write the formula into the cell as “=D5“.

=D5

Here, D5 is the reference of the cell above, with whose value you want to fill in the blank cells.

  • Press Ctrl + Enter, which fills the rest of the cells with a similar formula, using the previous cell’s reference.

Let’s convert the formulas into values.

  • Select the entire dataset.
  • Copy the entire dataset by pressing Ctrl + C.

  • Right-click and select Paste Values (V).

  • Finally, the result will look like the following picture.

Excel Fill Blank Cells with Value Above

The cells have been converted from having a formula (that you have applied) into a static value.

Excel Fill Blank Cells with Value Above


Method 2 – Applying the Find & Replace Command to Fill Blank Cells

Steps:

  • Select the entire dataset.
  • Go to the Home tab and the Editing group, select the Find & Select drop-down menu, and choose Find.

  • A box will come up. Keep the Find what: box blank and click on Find All.

  • Shift your cursor from the dialog box and place it on the worksheet, then press CTRL+A from the keyboard. This will select all the blanks.
  • 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.

  • Press “=” from the keyboard, and an equal sign will automatically show up in the active cell.
  • Write “D13” in the active cell.

=D13

  • Press Ctrl+Enter.

Thus, you will find the result as shown.


Method 3 – Applying a Nested LOOKUP Formula in Excel to Fill Blank Cells

Steps:

  • 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.
  • The Create Table dialog box will open up and show the selected range of data.
  • Mark My table has headers checkbox and click OK.

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

  • Select a random column F and copy the following formula:

=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 a value above that cell.

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)

  • The values of Dates of Sales are different from the original dataset because the Number Format is General by default.

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.

  • Copy this formula into column H:

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

Method 4 – Using Excel VBA to Fill Blank Cells with a Value Above

Steps:

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

  • The VBA window will open showing the General Window on it.
  • Copy the following code into it:

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

  • Press F5 from the keyboard to run the code or click on the green arrow in the ribbon.

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

Excel Fill Blank Cells with Value Above


Download Practice Workbook

You can download the workbook from here.


Related Readings


<< Go Back to Fill Blank Cells | Excel Cells | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
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