Reports often list a group label once, leaving the rows beneath blank. Fill each blank with the value above so the column is complete and usable for sorting, filtering, and PivotTables.
The example
Region label fills down its rows.
| A | B | |
|---|---|---|
| 1 | Raw | Filled |
| 2 | West | West |
| 3 | (blank) | West |
The formula
The formula:
How it works
How it works:
- In a helper column,
IF(A2="", B1, A2)— if blank, pull the filled value above; else keep the cell. - Reference the helper column above (B1) so filled values cascade down a run of blanks.
- Then paste the helper as values over the original to lock it in.
- The no-formula route: select the column, Go To Special → Blanks, type
=up-arrow, Ctrl+Enter.
Go To Special is the one-shot manual version. Select the column, Home → Find & Select → Go To Special → Blanks, then type =, press the up arrow, and confirm with Ctrl+Enter — every blank fills from the cell above at once. The formula version is better when the fill must update as data changes.
Try it: interactive demo
A column with gaps (one value per line, blank = repeat).
Variations
Trim before testing
Spaces count as filled:
Blank if truly empty
Use ISBLANK:
Group counter
Number each group:
Pitfalls & errors
Reference the helper above. Point B1 at the filled column, not the gappy original, so fills cascade.
Paste as values. Lock the result before deleting the original column.
Spaces aren’t blank. A cell with a space fails ="" — wrap in TRIM if needed.
Practice workbook
Frequently asked questions
How do I fill blank cells with the value above in Excel?
Is there a no-formula way?
Why does my fill stop after one blank?
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