# How to fill blank cells with the value above using a formula in Excel

In a helper column, take the value when there is one and the cell above otherwise: =IF(A2="",D1,A2), filled down.

**Excel:** `=IF(A2="",D1,A2)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Region | Rep | Sales | Region (filled) |
| East | Ana | 4200 |  |
|  | Ben | 3100 |  |
|  | Cara | 5650 |  |
| West | Dev | 2900 |  |
|  | Eli | 4475 |  |
| North | Fay | 3820 |  |
|  | Gus | 2610 |  |

Formula in D2, filled to D8: `=IF(A2="",D1,A2)`

Result: East; East; East; West; West; North; North

## How it works

Reports often show a group name only on its first row. When A2 has a value, the formula returns it; when it's blank, it returns D1, the result one row up in the helper column itself. Filled down, each blank picks up the last region above it.

## Other ways

- `=IF(A2<>"",A2,D1)`: The same test written the other way round.

## Common mistakes

- The first data row must have a value, or the formula picks up the header.
- Paste the helper column as values before you sort, or the references to the row above point at the wrong rows.
- A cell holding a space isn't blank. Test `TRIM(A2)=""` for imported data.

## Questions

**Can I fill the blanks in place, without a helper column?**

Select the column, press F5 > Special > Blanks, type = and press the up arrow, then Ctrl+Enter. Paste as values afterwards.

**Is there a single formula for the whole column in Excel 365?**

SCAN carries the last value down: `=SCAN("",A2:A8,LAMBDA(a,v,IF(v="",a,v)))`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/fill-blank-cells-with-value-above