How to Find and Fill Missing Values in Excel (Without Guessing)

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.

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.

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."

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.