Fill Blank Cells in Excel with the Value Above or a Lookup
Fill blank Excel cells from the row above or a reference table. Follow copyable formulas and a worked example that preserves zeros and flags missing matches.
To fill blank cells in Excel, use Go To Special → Blanks for repeated group labels, or a lookup in a helper column when another table contains the missing values. Do not fill unknown amounts with zero: AVERAGE ignores empty cells but includes zeros, so the two produce different results.
Here's a disciplined workflow: find every blank, decide what each one means, and fill only the ones that should be filled — with a record of what changed.
Quick answer
First identify whether a cell is truly blank, an empty formula result, whitespace, zero, or “not applicable.” Then fill only values that can be recovered from a trusted rule or source. Put unresolved cases in a status column, preserve the original data, and validate totals after the fill. Never replace every blank with zero or an average by default.
Step 1: Find all missing values
Three quick methods, fastest first:
Go To Special. Select your data range, press F5 → Special… → Blanks → OK. Every blank cell in the range is now selected; give them a fill color so they're visible.
Count them per column:
=COUNTBLANK(B2:B1000)
Filter for them. Add a filter (Ctrl+Shift+L), open a column's dropdown, and check (Blanks) to see exactly which rows are affected.
Also watch for fake blanks: cells containing a space or an empty string "" returned by a formula. COUNTBLANK counts "" but Go To Special → Blanks does not select it. This mismatch is a classic source of confusion:
=SUMPRODUCT(--(TRIM(B2:B1000)=""))
counts both true blanks and whitespace-only cells.
Step 2: Decide what each blank means
This is the step most people skip. A blank can be:
| Meaning | Right action |
|---|---|
| Data exists but wasn't entered | Fill from the source |
| Genuinely zero | Enter 0 explicitly |
| Not applicable | Mark N/A (as text) so it's deliberate |
| Unknown / needs follow-up | Flag it, don't invent a number |
Filling "unknown" with a made-up number is worse than leaving it blank — you've converted visible uncertainty into invisible error.
Step 3: Fill the ones that should be filled
Fill down from above (common for report exports where a category appears once per group): select the range, F5 → Special → Blanks, type = then press the up arrow, and confirm with Ctrl+Enter. Every blank now copies the value above it. Convert to values afterward with Paste Special.
Work on a copy, select only the intended label column, and check that the first blank has a valid label above it. Do not use this method for missing costs or across unrelated groups. Clear filters or explicitly define which rows may change before filling.
Compute from other columns. If B holds cost, C revenue and D profit, put this formula in a new helper column E, not in B2. First verify that revenue and profit are valid numbers:
=IF(B2="", C2-D2, B2)
Look it up from another sheet:
=IF(B2="", XLOOKUP(A2, Ref!$A$2:$A$3, Ref!$B$2:$B$3, "CHECK SOURCE"), B2)
Worked example that keeps real zeros
Create a reference sheet named Ref: A2 is A100, B2 is 12, A3 is A200, and B3 is 99. On your working sheet, enter the following inputs in columns A and B. Enter the lookup formula above in E2 and fill down to E4.
| A — Product key | B — Original cost | E — Expected result |
|---|---|---|
| A100 | blank | 12 |
| A200 | 0 | 0 — preserve the original zero |
| A999 | blank | CHECK SOURCE |
The expected outcome is one filled value, one unchanged zero and one unresolved value, not three completed costs. Check that reference keys are unique and reference costs are not blank before using this formula: XLOOKUP returns the first match, and an empty source cost can appear as zero. Review E before pasting approved values into B.
XLOOKUP is not available in Excel 2016 or 2019. In those versions, use an exact-match lookup in the same helper column:
=IF(B2="", IFNA(VLOOKUP(A2, Ref!$A$2:$B$3, 2, FALSE), "CHECK SOURCE"), B2)
For a larger reconciliation example, see matching Excel columns with AI. If a lookup fails unexpectedly, diagnose it with IFNA and XLOOKUP error handling rather than hiding every error.
Step 4: Keep an audit trail
However you fill blanks, record which cells were changed — a highlight color, a "filled" status column, or a change log. Future-you will need to distinguish original data from reconstructed data.
A useful audit table contains:
| Field | Example |
|---|---|
| Row or record key | Order-1042 |
| Column changed | Cost |
| Original value | blank |
| New value | 42.50 |
| Source or rule | Prices!B:B via SKU |
| Review status | Verified |
For large datasets, count missing values before and after by column. A lower blank count is not enough—the number of unresolved and filled values should reconcile to the original total.
Methods to use—and when
- Fill from above: only when blank cells inherit a group label by design.
- Lookup from a reference table: best when a stable key and authoritative source exist.
- Calculate from other fields: safe when the relationship is an accounting or business identity.
- Statistical imputation: appropriate for analysis models, but usually wrong for operational records unless the method is documented.
- Leave blank and flag: correct when the value is genuinely unknown.
If you cannot explain where a filled value came from, do not write it back as fact.
The one-instruction version
This whole workflow is a single request to an assistant that works inside your workbook. With AI for Excel open in the sidebar:
"Find all missing values in this table. Fill costs from the reference sheet where possible, set true zeros to 0, flag the rest in a new Status column, and tell me what you changed."
The add-in reads the range, applies each fill, writes a status per row, and summarizes the result — like the demo on our home page, where a missing cost is completed and the profit column is written back. Because it snapshots the workbook before writing and verifies what it wrote, "AI filled my data" never has to mean "I lost track of my data."
Related Excel guidance
Missing values are usually one part of a wider cleanup job. Continue with the complete Excel data-cleaning checklist and review Microsoft's top ways to clean data in Excel.
FAQ
How do I highlight all blank cells in Excel?
Select the range, press F5, choose Special → Blanks, then apply a fill color while they're selected. Conditional formatting with the formula =ISBLANK(A2) keeps future blanks highlighted automatically.
Should missing values be zero or blank?
Only enter 0 when the value is genuinely zero. A blank means "no data," and treating it as zero changes averages and ratios. If a value is unknown, flag it as unknown rather than inventing a number.
Can AI fill missing data in Excel automatically?
Yes — but insist on three safeguards: the tool should say where each filled value came from, mark filled cells so they're distinguishable from originals, and back up the sheet before writing. AI for Excel does all three and lets you roll back the entire change if needed.
