Almost every real export has gaps: a customer with no phone number, a survey question someone skipped, a region left out of a sales report. The gaps themselves are normal. The trouble is that a CSV can express “no value” in half a dozen ways, and different tools count them differently.
This guide shows how missing values appear in CSV files, why your spreadsheet and your colleague’s script may report different numbers, and how to decide whether to leave, fill or drop them.
The many ways a CSV says “nothing here”
Here is a small file with five rows and at least five kinds of missing value:
id,region,revenue,manager
1,North,1200,Kim
2,,950,
3,South, ,N/A
4,null,-,Lee
5,"",0,None| In the file | Usually means | How tools often read it |
|---|---|---|
Nothing between commas, ,, | No value | Blank or null almost everywhere |
"" | An explicitly empty value | Usually the same as blank; some databases treat it as an empty string, not null |
| Spaces only | Usually an accident | Text in most spreadsheets, so not counted as blank |
N/A, NA, n/a | Not applicable, or not available | Text in spreadsheets; missing in many analysis libraries |
null, NULL, None | A null exported from a database or app | Text in spreadsheets; sometimes missing elsewhere |
- or — | A human placeholder | Text almost everywhere, which can stop a numeric column being read as numbers |
0 | A real zero | A number. Not missing, and should not be treated as missing |
Why tools disagree about what is missing
CSV has no concept of null. The format only defines fields and the text inside them, so every tool applies its own rules when it reads a file. A few well-known examples:
- In Excel,
COUNTBLANKcounts empty cells but not cells that contain a space, andN/Atyped as text is just text. - The Python library pandas treats a list of strings as missing by default when reading a CSV, including
N/A,NA,nullandNULL, but not-. - R’s
read.csvtreatsNAas missing by default. - PostgreSQL’s CSV import treats an unquoted empty field as null and a quoted empty field
""as an empty string, unless told otherwise.
In the sample above, Excel’s COUNTBLANK would typically find 2 blanks in the region column, while pandas would report 3 missing values because it also treats null as missing. Neither is wrong; they follow different rules.
Placeholders can collide with real data. NA is the two-letter country code for Namibia, Na is the chemical symbol for sodium, and None can be a genuine survey answer. Treating them as missing everywhere would silently delete real values.
How to count missing values per column
Count per column, not for the whole file. A column with 2% blanks and a column with 60% blanks need different decisions.
In a spreadsheet
Truly empty cells:
=COUNTBLANK(B2:B1001)
Empty or spaces only:
=SUMPRODUCT(--(TRIM(B2:B1001)=""))
A specific placeholder (case-insensitive):
=COUNTIF(B2:B1001,"N/A")To find placeholders you did not know about, list the distinct values of a column with a filter dropdown or the UNIQUE function and look for anything that means “nothing”.
With a profiler
The free CSV validator profiles each column with a blank count and an inferred type. If a column you expect to be numeric comes back as text, look for -, N/A or similar placeholders mixed in with the numbers.
Leave, fill, fill down or drop
There is no single right answer. Choose per column, based on what the gap means.
| Strategy | Good fit | Trade-off |
|---|---|---|
| Leave blank | The gap is informative, or the next tool handles missing values well | Functions like AVERAGE skip blanks silently, so results cover fewer rows than you think |
| Fill with a fixed value | Category columns in reports, for example Unknown or Unassigned | Filling numbers with 0 changes averages and totals, and looks like real data |
| Fill down | Grouped exports where a value is only written on the first row of each group | Wrong if the blank genuinely means missing; depends entirely on row order |
| Drop the row | The row is useless without the value, such as a mailing list entry with no email | Loses the rest of the row; can skew results if gaps are not random |
Fill down is the fix for reports that look like this:
region,rep,deals
North,Kim,4
,Lee,7
,Ana,2
South,Ben,5
,Joe,3Filling region down gives Lee and Ana the value North and Joe the value South, which is what the report meant. Sort the file first and the same operation produces nonsense, so fill down before any re-sorting.
In Excel you can do this by selecting the column, choosing Home → Find & Select → Go To Special → Blanks, typing = followed by the cell above, and pressing Ctrl+Enter. Then paste the column as values so the formulas do not change later.
Step by step: handling missing values
- Keep the original file.
- Trim whitespace so cells that contain only spaces become truly blank.
- Count blanks per column and list the placeholder values each column uses.
- Decide what each placeholder means. Where
N/Ameans “not applicable” it may be worth keeping; where it means “unknown”, consider replacing it with a blank. In Excel or Google Sheets, use Find and Replace with Match entire cell contents turned on so you do not change values that merely contain the text. - Remove rows that are completely empty.
- Fill with a fixed value or fill down, only in columns where that is justified.
- Drop rows that are unusable without a key value, if your purpose allows it.
- Recount and write down what you did, so whoever uses the file knows which values were filled.
Fill blank cells in your file
The free Fill Blank Cells tool fills blanks in the columns you choose, either with a fixed value or with the value from the row above. Pair it with Remove Empty Rows, which can also drop columns that are entirely blank. Both run in your browser, so the file is never uploaded.
The CleanYourDataset workspace shows a missing count for every column. It counts only blank and whitespace-only cells as missing and treats N/A as data, so the decision about placeholders stays with you. You can fill blanks in a column with a value, see how many cells will change first, and undo if needed. Read the guide or create a free account to try it.