You don't need code to handle missing values. You need a decision process. Every missing value has exactly four treatment options: delete the row, fill with a default, fill with a derived value, or flag it and leave it. The right choice depends on WHY the value is missing — not on what's mathematically convenient.
The Decision Framework
Before deciding how to treat a missing value, answer this question: Why is it missing?
There are three distinct causes, and they require different treatments:
1. Never collected — The field was added to your system after this record was created. You simply don't have this information. You can't invent it; you can only flag its absence or accept the null.
2. Lost in export — A system or ETL bug dropped the value. The real value exists; you just need to retrieve it from the source. Fix the source problem; don't patch the symptom.
See exactly what's wrong with your data — try Sohovi free — try Sohovi free.
3. Genuinely N/A — The field doesn't apply to this record type. A "billing address" for a non-paying user, a "graduation date" for a dropout. A null here is correct — filling it would be wrong.
The Four Treatment Options
Option 1: Delete the Row
When to use: The row is useless without this value. An order record with no customer ID. A lead record with no contact method at all.
When NOT to use: When the missing value is just one of many fields, and the rest of the row is valuable. Deleting rows introduces bias if the missingness is non-random — if only certain types of customers are missing a field, deleting them changes your dataset's composition.
Sohovi profiles every column in your dataset for completeness and flags the exact rows where values are missing — free to try.
In Excel: Filter on blanks → select all blank rows → delete. Or: =IF(ISBLANK(A2), "DELETE", "KEEP") helper column → filter on DELETE → remove.
Option 2: Fill With a Default Value
When to use: There's a sensible, factually justified default. Country = "IN" when you know the dataset is all Indian customers. Status = "Active" when new records start as active by default.
When NOT to use: When the default is arbitrary or would create a false impression. Don't fill missing revenue with $0 — that turns "unknown revenue" into "zero revenue" and will wreck every revenue analysis downstream.
The silent damage example:
Imagine a 100-customer dataset where 30 customers have missing revenue values. Their actual average revenue is $800. If you fill the 30 missing values with $0 and compute the total average:
- True average: $800 (if all 70 known customers also average $800)
- After filling with $0: (70 × $800 + 30 × $0) / 100 = $560
You've artificially depressed the average by 30%, and every report built on this will be wrong. This is one of the most common data quality errors in business analytics.
In Excel: =IF(ISBLANK(A2), "your_default", A2) — applies a default only to blank cells.
Option 3: Fill With a Derived Value
When to use: You can confidently calculate or look up the missing value from other data.
- Missing city → derive from ZIP code using a ZIP code reference table
- Missing country code → derive from phone number prefix
- Missing full name → derive from first_name + last_name columns
- Missing age → calculate from date_of_birth
In Excel: VLOOKUP or INDEX/MATCH against a reference table. For ZIP-to-city: =IFERROR(VLOOKUP(A2, zip_reference_table, 2, FALSE), "Unknown").
Caution: Only derive when the derivation is reliable. Deriving city from ZIP is reliable (ZIPs map to specific cities). "Inferring" someone's income from their ZIP code is not — it's inaccurate and potentially discriminatory. Know what your derivation actually tells you.
Option 4: Flag and Leave
When to use: The value is genuinely unknown, any fill would be misleading, and you don't want to delete the row. This is often the right answer.
Add a boolean column: is_address_missing, is_revenue_unknown. Now downstream systems can filter on "records with known revenue" rather than working from a contaminated average.
In Excel: =IF(ISBLANK(A2), TRUE, FALSE) → creates a flag column. Or in your report: use filters that explicitly exclude blanks rather than filling them.
Why this is underused: It requires discipline in downstream processes to respect the flag. But it's the honest approach — it preserves the fact of missingness rather than hiding it.
Excel Techniques for Finding and Handling Blanks
Find all blank cells in a column:
- Select the column
- Ctrl+G (Go To) → Special → Blanks → OK
- All blank cells are selected — you can delete, fill, or format them
Count blanks: =COUNTBLANK(A2:A1000) — how many blanks in the range.
Percentage missing: =COUNTBLANK(A2:A1000)/COUNTA(A2:A1000) — blank rate as a decimal (multiply by 100 for percentage).
Fill down from previous value (for sorted data):
- Select the column with gaps
- Ctrl+G → Special → Blanks → OK
- Type
=A1(reference the cell above the first blank) → Ctrl+Enter - This fills ALL selected blank cells with the value of the cell above
Useful when data was exported without repeating the group header — fill-down restores the grouped value to all rows in the group.
Seeing the Full Completeness Picture
Handling missing values column by column is how you fix them. But understanding your dataset's overall completeness picture — which columns are 20% null, which are 100% complete, which are only null for certain customer types — requires a profile, not a formula.
Sohovi's completeness check gives you a column-by-column null rate report in under a minute, with the ability to drill into which rows are missing values in multiple columns simultaneously (the compound null problem that's hardest to spot in Excel).
Frequently Asked Questions
Q: Is it always wrong to fill missing values with 0? Not always — it's wrong for numeric metrics where 0 has a real meaning (revenue, quantity, age). For boolean/binary columns where null means "we don't know" and 0 means "no", consider whether 0 or null is the right representation for your downstream system. For count columns where null means "not yet counted", 0 might be the correct fill. The rule is: understand what 0 means in context before filling.
Q: What's the difference between NULL, empty string, and 0 in a database context? NULL means "no value / unknown". Empty string ("") means "the value was set and it's blank". 0 means "the value is zero". These are semantically different — don't use them interchangeably. In spreadsheets, empty cells are usually treated as NULL-equivalent, but check how your downstream system interprets them.
Q: How do I handle missing values in a column I'm using for deduplication? Carefully. If you're deduplicating on email and some rows have no email, those rows can't be matched on that criterion. Options: use a different column combination for dedup (name + company), exclude no-email rows from the dedup step and handle them separately, or investigate why emails are missing before deduplicating.
Q: Should I fill missing dates with a placeholder date (like 1900-01-01)? No. This is one of the most common bad practices in data management. Placeholder dates look like real dates to every system downstream and will corrupt date-based analysis, age calculations, and retention metrics. Use NULL for unknown dates.
See exactly what's missing in your file — column-by-column completeness scores in Sohovi, free, in your browser. Upload your dataset and know your null rates before you start deciding how to fill them.