Replace empty cells with a fallback — “N/A,” a zero, or a value from elsewhere — so reports never show distracting blanks. A quick IF (or a couple of alternatives) does it.
The example
Blanks replaced by a default.
| A | B | |
|---|---|---|
| 1 | Value | Shown |
| 2 | Ann | Ann |
| 3 | (blank) | N/A |
The formula
Fallback for empties:
How it works
Test for blank, supply a default:
A2=""is TRUE for an empty cell (and for a formula returning an empty string).- IF returns the default when blank, the value otherwise.
- For a zero default in math,
=N(A2)or=A2+0treats blank as 0. - To pull a fallback from another cell:
=IF(A2="", B2, A2).
Coalesce down a list of options: nest IFs — =IF(A2<>"", A2, IF(B2<>"", B2, "N/A")) — returns the first non-blank. In 365, =IFS(A2<>"",A2, B2<>"",B2, TRUE,"N/A") is cleaner.
Try it: interactive demo
Type a value (or leave blank).
Variations
Zero for math
Treat blank as 0:
Fallback cell
Use another value:
First non-blank
Coalesce:
Pitfalls & errors
Blank vs empty string. ="" from a formula isn’t truly blank, but A2="" treats it as such — usually what you want.
Spaces aren’t blank. A cell with a space fails A2="" — test TRIM(A2)="" to catch it.
Default type. A text default in a numeric column can break downstream math — use 0 or "" if the column feeds calculations.
Practice workbook
Frequently asked questions
How do I show a default value for blank cells in Excel?
How do I treat a blank as zero in a calculation?
How do I return the first non-blank of several cells?
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