Fill Blank Cells in a CSV

Filling blanks replaces the empty cells in a column with something usable. Carry the value above down, carry the one below up, write a fixed value or a zero, use the column mean, median or most common value, or draw a straight line between the readings on either side of the gap. Every method reports what it filled, per column. It runs in this tab.

Want the empty rows and columns gone instead of filled? Remove them.

The method is the whole decision

Filling blanks is not one operation with a few flavors. It is eight different claims about what the missing value meant, and picking the wrong one puts numbers into a file that were never measured.

The value above is right when the column was only written when it changed. That is exactly what a merged spreadsheet cell becomes on export: a region name in row 1 and four blanks under it. Forward fill is the default because this is the most common shape of blank in a real CSV.
The value below is the same idea for a file written bottom up, and it is also the way to fill a gap at the very top that forward fill cannot reach.
Zero is right for a count of something that did not happen. It is wrong for a measurement that was not taken, and the two look identical in the file.
A fixed value such as unknown is right when you want the gap to stay visible downstream rather than being smoothed away.
Mean and median are right for a measurement and nonsense for an identifier. Median is the safer of the two on anything with outliers in it, since one enormous value drags a mean and leaves a median alone.
The most common value is the categorical equivalent: a status column with nine active and one blank probably wants active.
A straight line between neighbours is right for a reading taken at intervals: a sensor, a daily balance, a meter. It is the only method here that produces a different value in each filled cell.

Worked example: two methods on one file

A four-row extract, regions.csv:

region,value,note
East,10,a
,,b
West,30,c
,,d

Leave the method on the value above and every column ticked:

region,value,note
East,10,a
East,10,b
West,30,c
West,30,d

Now tick only value and switch the method to the column mean:

region,value,note
East,10,a
,20,b
West,30,c
,20,d

4 rows · mean fill · 1 column covered · 2 blanks filled · no blanks left
value: 2 filled (filled with mean 20)

The mean of 10 and 30 is 20, and the report says which number it used rather than leaving you to work it out. Try the same method on region and nothing happens, with the reason printed: this column is not numeric, so mean and median have no answer for it. Refusing is the right behavior. Filling a text column with the mean of nothing is how a file ends up with NaN in it.

Interpolation, and the gaps it deliberately leaves

A straight line between neighbours is the method to use on anything sampled over time. Take a meter reading of 10, then a gap, then 40. Forward fill writes 10 into the gap, which claims the meter did not move and then jumped. Interpolation writes 25, which claims it moved steadily. For a meter, the second claim is closer to true.

A run of several blanks is spread evenly across the distance, so 10, blank, blank, 40 gives 20 and 30. What the method will not do is fill a gap at the very start or the very end of a column, because those have only one neighbour and filling them would be extrapolation rather than interpolation. Guessing what happened before the first reading is a modeling decision, not a cleaning one. The report says how many cells were left for that reason.

Two practical notes. Interpolation follows the row order in the file, not a date column, so sort the file first if the rows are not already in time order. And Round to is worth setting: without it, a line drawn between 10 and 41 across three gaps produces values with a lot of decimal places on them.

What counts as blank

By default a cell holding nothing but spaces counts as blank, and this catches more files than it sounds like it should. A cell that a person cleared by pressing the space bar, a fixed-width extract where an empty field is padded to its column width, an export that writes a single space where a null was. All of them look empty and none of them are, and a fill that only recognizes the truly empty string skips every one.

Switch A cell of spaces to Counts as a value when the padding is meaningful, which is rare outside fixed-width work.

What this page does not treat as blank is a placeholder: NA, N/A, NULL, -. Those are values somebody wrote deliberately, and turning them into something else silently would be a bigger decision than this page should make on its own. If you want them gone, run a find and replace with whole-cell matching first, and then fill. The empty row and column remover does recognize them, because dropping a column that is 100% placeholders is a much smaller claim than inventing values for it.

The per-column report

A single "filled 412 cells" tells you almost nothing about a file with fifteen columns. So the report is per column, and it carries three pieces of information for each one: how many blanks were filled, how many are still there, and a note when the method could not do what you asked.

1,204 rows · forward fill · 6 columns covered · 388 blanks filled · 24 blanks still empty
region: 210 filled
customer: 0 filled (the first row had nothing above it to carry down)
amount: 178 filled
notes: 0 filled

The interesting lines are the zeros. A column that filled nothing either had no blanks, which is fine, or hit one of the conditions that stops a method: nothing above the first row to carry down, nothing below the last row to carry up, an empty fill box, a non-numeric column under a numeric method, or a column that is blank all the way down and therefore has no most common value. Each of those prints its own reason, and the ones that refused outright are repeated in the warning line so they are hard to miss.

Before you fill anything

Filling blanks is a lossy operation. Once a gap has a number in it, nothing downstream can tell the difference between a value that was measured and a value this page invented. That is fine when everyone involved knows it happened and a problem when they do not.

Two habits make it safer. Fill one column at a time rather than the whole file, since the right method almost never matches across every column of a real export: a region wants forward fill, a count wants zero, a sensor reading wants interpolation, and a customer name wants nothing at all. And run the profiler first, which tells you how many blanks each column actually has before you decide any of this. A column that is 80% empty is not a column with gaps; it is a field nobody ever populated, and filling it manufactures a dataset.

One thing the page cannot help with: a row where every single cell was empty is dropped when the file is read, before any of this runs, and the count is reported at the top of the warnings. Those rows are the trailing commas Excel leaves behind rather than data with gaps in it.

Frequently Asked Questions

Which fill method should I use?

The value above for a column that was only written when it changed, which is what a merged spreadsheet cell becomes on export. Zero for a count of something that did not happen. Mean or median for a measurement. The most common value for a category. A straight line between neighbours for anything sampled over time. A fixed value such as unknown when you want the gap to stay visible.

Why did nothing happen when I picked mean?

Because the column is not numeric, and the report says so in as many words. Mean and median refuse to run on a column that holds text, rather than producing NaN or zero. The most common value is the method that works on a text column.

Does forward fill work across the whole file or one column?

Whichever you choose. Leave every checkbox unticked and every column is covered; tick one and only that one is filled. Filling one column at a time is usually right, because the correct method rarely matches across every column of a real export.

What happens to a blank at the very top of the column?

Forward fill cannot reach it, since there is nothing above it to carry down, and the report says so rather than leaving you to notice. Use the value below instead, or run both in turn: fill down first, then fill up to catch the leading gap.

Why does interpolation leave the first and last gaps alone?

Because those have only one neighbour, so filling them would be extrapolation rather than interpolation. Guessing what happened before the first reading is a modeling decision rather than a cleaning one, and the count of cells left for that reason is printed.

Does a cell containing only spaces count as blank?

Yes, by default, and it catches more files than you would expect: a cell cleared with the space bar, a fixed-width field padded to its width, an export that writes a space where a null was. Switch A cell of spaces to Counts as a value when the padding is meaningful.

What about cells holding NA or NULL?

Those are left alone. They are values somebody wrote deliberately, and quietly turning them into something else is a bigger decision than this page should make. Replace them with a whole-cell find and replace first if you want them filled.

Is the file uploaded anywhere?

No. There is no upload endpoint on this page. The file is read, filled and rewritten by JavaScript in your own tab, and nothing is kept between visits.

Fill the gaps, on purpose

Free, no account, no upload. Pick the column, pick the method, read the per-column report, take the CSV.

Back to the blank filler