Populating Blank Cells with the Value Above in Excel3 Easy Methods

  |
Written By:
Adiste Mae

Occasionally, in a dataset, you may come across cells that are left blank but should contain the same value as a neighboring cell.

A common example of this is when a date is only recorded once, and all subsequent entries for that date have empty cells in the date column.

While this format may look organized, it can be problematic when trying to use the data for calculations or creating pivot tables.

To address this issue, it is often necessary to fill in the blank cells with the value from the cell above it.

Filling in Blank Cells with the Value Above in Excel

This Excel tutorial will demonstrate three simple methods for populating blank cells with the value above in Excel.

  1. The first method is utilizing Go to Special with formula.
  2. The second approach is using Find and Replace with formula.
  3. The final method is by using Visual Basic for Applications (VBA).

One of the challenges in this process is identifying the blank cells to be filled.

But once these cells have been selected, there are various ways to populate them with the value from the cell above.

Let’s begin!

Filling Cells with the Value Above using Go To Special + Formula

In order to fill blank cells with the value above, the first step is selecting these blank cells. This can be achieved through the use of the ‘Go To Special’ feature in Excel.

For example, if you have a dataset as shown below and you want to fill all the blank cells in column A with the date from the cell above.

Here is the step-by-step process:

  1. First, select the dataset that contains the blank cells.
  2. Press the F5 key on your keyboard (or ⌃ + G for Mac users) to open the ‘Go To’ dialog box.
  3. Select the “Special” button.
  4. In the “Go To Special” dialog box, choose the “Blanks” option.
  5. Then click the OK button. 

The steps above will select all the blank cells in the dataset.

With all the blank cells now selected, the next step is to populate them with the value from the cell above.

 To accomplish this using a formula, follow these steps:

  1. Start by typing = (equal sign) in the active cell, this will enter the equal sign in that cell only.
  2. Then press the up-arrow key to select the cell immediately above the active cell.
  3. Hold the Control key and press the enter key (or Command + Enter for Mac users).

That’s it! The steps above have applied the same formula (referring to the cell above) to all the selected blank cells.

Now that all the blank cells are filled, it’s important to remember to convert the formulas to actual values.

Note:

It’s worth noting that for this method to be effective, the cells need to be completely empty.

If there are space characters present, the ‘Go To’ feature will not identify these as blank cells and they will not be selected.

Filling Cells with the Value Above using Find and Replace + Formula

While the above method (Go To Special) is effective when you only have blank cells to be filled, it may not be suitable for datasets where the cells contain a dash or a specific text such as “NA.” In such cases, you can also use the Find and Replace method.

This method is similar to Go To Special, but with the added advantage of being able to select cells based on their values. It allows you to select blank cells or cells that contain specific text or values.

As an example, let’s consider the scenario where you have a dataset as shown below and you want to fill all the blank cells in column A with the date from the cell above.

The following are the steps to select all the blank cells using the Find and Replace method:

  1. First, select the dataset that contains the blank cells
  2. Press the Control key and the F key (or Command + F on a Mac)
  3. In the Find and Replace dialog box that opens, select the “Find All” button. This will identify all the blank cells and display a list of their addresses.
  4. Press the Control key and the A key (or Command + A on a Mac) to select all the blank cells from the found list.
  5. Close the Find and Replace dialog box.

Upon completion of these steps, all empty cells will be selected. You can now use a formula to retrieve the value from the cell above and fill in the blank cells in Excel.

To do this, follow these steps:

  1. Start by entering an equal sign (=) in the active cell, this will only apply the formula in that cell.
  2. Press the up-arrow key to select the cell directly above the active one.
  3. Hold the Control key and press the enter key (or Command + Enter for Mac users).

One of the benefits of using Find and Replace is that it works when you have blank cells that you want to fill, and you can also use it to find and select cells that have specific values.

For example, suppose you have a dataset as shown below where instead of the blank cells, we have dashes instead.

Here are the steps in doing this:

  1. Start by selecting the dataset.
  2. Hit Control + F or Command + F if you are using Mac.
  3. In the “Find and Replace” dialog box that opens, enter ‘-‘ (dash) in the “Find what” field.
  4. Press “Find All”, this will find all the cells that have a dash in it and display a list of their addresses.
  5. Press Control + A or Command + A to select all the cells that were found.
  6. Close the “Find and Replace” dialog box.

Once you have selected the cells, you can follow the steps for using a formula, previously described, to fill in the cells with the values from above (or from below, right, or left cells).

Filling Cells with Value Above by Using VBA

While the above methods are effective, they can be time-consuming if the task needs to be performed frequently.

To streamline the process, you can use a simple VBA code.

For maximum efficiency, you can add the VBA macro to the Quick Access Toolbar (QAT) and use a single click to fill the selected blank cells with the value from the cell above.

Here is the VBA code that we are using:

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 utilize this code, first, highlight the dataset that you want to populate with values from the above cell. Then execute this VBA code.

The VBA code includes a FOR EACH loop where it iterates through each cell in the selected range.

When it finds an empty cell (as determined by an IF statement), it copies the value from the cell above (using the OFFSET function).

In this specific case, you may observe that.

Note: If you want to save a workbook that contains the macro code, it must be saved in the.XLSM (macro-enabled) format.

The key advantage of using this VBA code is that it eliminates the need to convert formulas into values, providing only static values.

This makes it the most efficient method covered in this tutorial.

However, when using VBA to fill blank cells with the value from the above cell, it is important to keep the following considerations in mind:

  • Once the macro code is executed, it cannot be undone. Therefore, it is essential to create a backup copy before running the VBA code.
  •  If you are working with a large dataset that contains thousands of rows with multiple columns, running the VBA code might cause performance issues. As the code iterates and analyzes every cell, it takes longer than the other two methods. But with a few hundred or even a few thousand data points, the difference in speed is likely to be insignificant.

To use this VBA macro code, you need to place it in a regular module.

With this, you learned three methods to fill the blank cells with data from the cell above it.

I hope this tutorial was helpful to you!