How to Find and Fill Missing Values in Excel (Without Guessing)
Locate blank cells fast, decide whether to fill, flag, or leave them, and use formulas or AI to complete missing data in Excel — with an audit trail of what changed.
Missing values are the quietest way a spreadsheet lies to you. A blank cell in a cost column doesn't just lose one number — it silently shrinks every AVERAGE, skews every pivot, and turns profit calculations into errors.
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.
Compute from other columns. If cost is missing but revenue and profit exist:
=IF(B2="", C2-D2, B2)
Look it up from another sheet:
=IF(B2="", XLOOKUP(A2, Ref!A:A, Ref!B:B, "no match"), B2)
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.