Fill Blank Cells With the Value Above

All versionsIF

Reports often leave a category blank after the first row of each group. To make the data usable for filtering or pivots, you need to fill those blanks down — with a formula, or a slick Go To Special trick.

Quick formula: in a helper column, for the value in A2:

=IF(A2="", B1, A2)

If A2 is blank, repeat the filled value from the row above (B1, the helper); otherwise keep A2’s own value. Copy down the whole column.

Functions used (tap for the full reference guide):

IF ISBLANK

The example

A region column with gaps, filled down so every row is labeled.

	A	B
1	Raw	Filled
2	East	East
3	(blank)	East
4	West	West
5	(blank)	West

The formula

Carry the last seen value into the blanks:

=IF(A2="", B1, A2) // B holds the filled values; B1 is the row above

How it works

The formula simply repeats the value above when a cell is empty:

In a helper column next to the data, enter =IF(A2="", B1, A2) in the second data row.
If A2 has a value, the formula keeps it; if it’s blank, it copies down the filled value from the cell directly above (B1).
Copy the formula down the whole column — each blank inherits the most recent non-blank value.
Finally, copy the helper column and Paste Special → Values over the original, then delete the helper.

No-formula method: select the column, press F5 → Special → Blanks to select only the empty cells, type = then the up-arrow (points to the cell above), and press Ctrl+Enter. Every blank fills with the value above at once. Convert to values afterward.

Try it: interactive demo

Live demo

Blank lines inherit the value above.

Variations

Go To Special (no formula)

F5 → Special → Blanks, then:

=A2 (the cell above) + Ctrl+Enter

With ISBLANK

More explicit test:

=IF(ISBLANK(A2), B1, A2)

Leave a marker instead

Flag blanks rather than fill:

=IF(A2="", "↑ same", A2)

Pitfalls & errors

Convert to values when done. The fill formula references the row above; if you sort or delete rows while it’s live, the fill breaks. Paste as values first.

Go To Special needs Ctrl+Enter. After selecting blanks and typing = + up-arrow, press Ctrl+Enter (not just Enter) to fill all selected blanks at once.

Truly blank vs empty string. A cell containing "" from a formula isn’t “blank” to Go To Special. Use the IF formula method for those.

Practice workbook

📊

Download the free Fill Blank Cells With the Value Above practice workbook

A fill-down sheet with the IF formula, ISBLANK and marker variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I fill blank cells with the value above in Excel?

Use a helper column with =IF(A2="", B1, A2) and copy it down, then paste as values. Or use F5 → Special → Blanks, type = and the up-arrow, and press Ctrl+Enter.

What's the fastest way to fill blanks down?

Select the column, press F5 → Special → Blanks to select only empty cells, type = then the up-arrow, and press Ctrl+Enter. All blanks fill at once.

Why won't Go To Special find my blank cells?

Cells that contain an empty string ("") from a formula are not truly blank. Use the IF-based formula method instead.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: First & last non-blank · Count blank cells · IF with AND/OR

Function references: IF · ISBLANK