A Practical Guide to Handling Missing Values in Small Business Spreadsheets

Most guides on handling missing data are written for statisticians working with research datasets, full of terms like "missing completely at random" that don't map cleanly onto the kind of spreadsheet a small business actually runs on — a sales log with some blank phone numbers, a customer list where half the "Company Size" column never got filled in, an inventory sheet with gaps from a system migration. This guide skips the academic framing and focuses on the actual decisions you need to make when you open a real spreadsheet and see blank cells staring back at you.

Not all blanks mean the same thing

The first mistake is treating every blank cell as the same problem. In practice, a blank cell in your data usually falls into one of a few distinct categories, and the right fix depends entirely on which one you're looking at.

Genuinely not collected. The field simply wasn't asked, or wasn't required, at the time this row was created. A "Referral Source" column added to your CRM last month will be blank for every customer who signed up before that column existed — not because the data doesn't exist for them, but because it was never possible to collect it.

Not applicable. Some rows legitimately have no value for a given column. A "Cancellation Date" column is blank for every customer who hasn't cancelled — that blank isn't missing data, it's meaningful information (this customer is still active), and treating it as a gap to be filled in would actively corrupt the dataset.

Should exist but doesn't. A required field — an order total, a customer email — that's blank because of a data entry error, a failed form submission, or an integration hiccup between two systems. This is the category that actually represents a data quality problem worth fixing.

Deliberately withheld. A customer chose not to provide their phone number on an optional form field. This is closer to "not applicable" than to an error — filling it in with a guess would be actively misleading.

Before deciding how to handle any blank cell, the real first question is which of these four categories it falls into — and that almost always requires knowing something about where the data came from, not just looking at the spreadsheet itself.

The three real options: delete, fill, or flag and leave alone

Option 1: Delete the row. This is the right call when a required field is missing and the row is unusable without it — an order with no total, a transaction with no date. It's the wrong call when applied broadly to any row with any blank anywhere, which is a surprisingly common mistake: dropping every row with a blank "Referral Source" from an otherwise complete sales analysis throws away real revenue data over a field that isn't even relevant to the question being asked. Delete based on whether this specific row is unusable for this specific analysis — not as a blanket rule applied to the whole sheet.

Option 2: Fill the blank (imputation). Appropriate when a reasonable default exists and leaving the row in with an estimated value is more useful than losing the row entirely. Common approaches, roughly in order of how much they distort your data:

• A fixed placeholder ("Unknown", 0, "N/A") — safest option, since it's honest about the value being missing rather than disguising a guess as real data. Works well for categorical fields.
• The column mean or median — reasonable for numeric fields where a rough estimate is better than a gap, such as filling a few missing "Order Amount" values with the average order amount, but be aware this artificially reduces the apparent variance in your data and can quietly bias any statistics computed afterward.
• Forward-fill or a related field — carrying forward the last known value (common in time-series data) or deriving the missing value from another column (estimating "Company Size" from "Industry" if you have a reasonable mapping). More accurate when it's valid, but easy to misuse on data where the assumption doesn't actually hold.

Option 3: Flag it and leave it blank. Often the most honest option, and the most underused. Add a companion column — Order_Amount_Was_Missing — with a 1/0 flag marking which rows had the value imputed or left blank, rather than silently filling gaps and losing the information that a fill happened at all. This matters more than it sounds like it should: a report built on data with hidden, silent imputation looks exactly as confident as a report built on complete real data, even though the underlying certainty is genuinely different. A flagged column at least preserves that distinction for anyone using the data downstream.

A decision process that actually works

1. Figure out why the value is missing before deciding what to do about it. This is the step almost everyone skips, and it's the one that actually determines the right answer. "Not applicable" blanks should usually stay blank. "Should exist but doesn't" blanks are the ones worth fixing.
2. Check what fraction of the column is missing. A column that's 2% blank is a cleanup task. A column that's 60% blank is a signal that the field probably wasn't reliably collected in the first place, and no amount of imputation will make it trustworthy — you may be better off excluding that column from analysis entirely rather than filling most of it with guesses.
3. Match the fix to the downstream use. A field going into a customer-facing report needs a very different standard than a field feeding a rough internal trend chart. Imputing a plausible average order amount for an internal revenue trend line is low-risk; imputing a customer's missing email address to send them an invoice is not something you should do at all.
4. Document what you did. Whether it's a flag column, a note in a README tab, or just a comment on the cell, record which values were real and which were filled. Six months from now, "why does this customer have an order amount that doesn't match their invoice" is a much easier question to answer if the imputation is documented rather than silently baked in.

Doing this at scale without doing it by hand

For a small file, working through blanks column by column is manageable. For a spreadsheet with dozens of columns and thousands of rows, manually deciding a fill strategy per column gets tedious fast, and it's easy to lose track of which columns you've already handled. DataForge's missing value tools handle this at the column level — showing you the percentage of missing values per column before you decide anything, offering fixed-placeholder, mean, and other imputation strategies per column, and optionally generating the "was this imputed" indicator flags described above automatically, so the audit trail exists without you having to build it manually.

The core principle

A blank cell is not automatically a problem to be solved — it's a question to be answered: does this represent missing information, or does it represent the accurate absence of something that was never supposed to be there? Getting that question right, on a column-by-column basis, matters far more than which specific imputation method you eventually reach for. Get the "why" right first, and the "how" almost always becomes obvious.