Interpolate Missing Numeric Values in Excel Data

Interpolation fills a gap by looking at its neighbours, and that makes it either the right tool or a way of inventing data — depending entirely on whether the rows are ordered along an axis where "between" means something. A missing daily temperature between Monday and Wednesday can reasonably be estimated. A missing region name between North and South cannot. This guide covers the methods pandas offers, the arguments that stop it fabricating values past the end of a series, and the flagging that lets a reader tell an estimate from a measurement. It extends Handling Missing Data in Excel Reports.

Prerequisites

pip install pandas openpyxl xlsxwriter

An irregular daily series with interior and trailing gaps:

import numpy as np
import pandas as pd

readings = pd.DataFrame({
    "date": pd.to_datetime([
        "2026-08-01", "2026-08-02", "2026-08-03", "2026-08-04",
        "2026-08-05", "2026-08-12", "2026-08-13", "2026-08-14",
    ]),
    "region": ["North"] * 4 + ["South"] * 4,
    "temperature": [18.2, np.nan, np.nan, 21.6, 15.4, np.nan, 17.9, np.nan],
})

Step 1 — Check that interpolation is even valid

Ask one question first: is there a meaningful order to the rows? If sorting the frame differently would change the filled values, interpolation is only defensible when that order is real.

import pandas as pd

def interpolation_is_sensible(df, order_column):
    """Cheap sanity checks before interpolating."""
    problems = []
    if order_column not in df.columns:
        problems.append(f"no ordering column named {order_column!r}")
        return problems

    ordered = df[order_column]
    if not ordered.is_monotonic_increasing:
        problems.append(f"{order_column} is not sorted — sort before interpolating")
    if ordered.duplicated().any():
        problems.append(f"{order_column} has duplicates — the order is ambiguous")
    return problems

print(interpolation_is_sensible(readings, "date"))

Two situations rule it out entirely. A gap that means zero rather than unknown — no sales because the shop was closed — should be filled with zero, not estimated from the days either side. And a table keyed by category rather than by a continuous axis has no meaningful "between", so interpolating a missing revenue from the alphabetically neighbouring region is nonsense dressed as an estimate. When either applies, the fill strategies in filling missing values with pandas fillna are the right tool instead.

Step 2 — Interpolate, with the arguments that matter

The bare call does more than most people want:

readings["temperature"].interpolate()      # also extends past the last value

Three arguments make it behave:

import pandas as pd

series = readings.set_index("date")["temperature"]

filled = series.interpolate(
    method="time",            # respect the actual date spacing
    limit=3,                  # bridge at most three consecutive gaps
    limit_area="inside",      # only between two real observations
)
print(filled)

limit_area="inside" is the one to set by default. Without it, interpolate happily extends the last observed value forward — which is extrapolation, and it produces a report where the final rows look like measurements and are not.

The difference method="time" makes is easy to underestimate. With linear interpolation the missing value between 5 August and 12 August lands halfway in row terms; with time interpolation it lands proportionally along the seven-day gap:

import pandas as pd

for method in ("linear", "time"):
    result = series.interpolate(method=method, limit_area="inside")
    print(f"{method:<8} {result.round(2).tolist()}")

On an evenly spaced series they agree. On any irregular one — which is every real operational series, because of weekends and outages — they do not.

Step 3 — Interpolate within groups

Interpolating a stacked frame across group boundaries is the most common way to get nonsense: the last North reading and the first South reading are unrelated, and a straight line between them is meaningless.

import pandas as pd

def interpolate_by_group(df, value, group, order, **kwargs):
    """Interpolate a column independently within each group."""
    out = df.sort_values([group, order]).copy()

    out[value] = (
        out.set_index(order)
           .groupby(group)[value]
           .transform(lambda s: s.interpolate(
               method="time", limit_area="inside", **kwargs))
           .to_numpy()
    )
    return out

filled = interpolate_by_group(readings, "temperature", "region", "date", limit=3)
print(filled)

Sorting inside the function is deliberate. interpolate walks the frame in its current row order, so an unsorted frame produces values that depend on how the rows happened to arrive — which is a bug that only appears when the source ordering changes.

Step 4 — Flag what was estimated

An interpolated value in a spreadsheet is indistinguishable from a measured one, and that is a problem the moment somebody acts on it. Capture the mask before filling:

import pandas as pd

def interpolate_and_flag(df, value, order, **kwargs):
    """Interpolate, recording which cells were estimated."""
    out = df.sort_values(order).copy()
    was_missing = out[value].isna()

    out[value] = (
        out.set_index(order)[value]
           .interpolate(method="time", limit_area="inside", **kwargs)
           .to_numpy()
    )

    out[f"{value}_estimated"] = was_missing & out[value].notna()
    out[f"{value}_still_missing"] = out[value].isna()
    return out

result = interpolate_and_flag(readings, "temperature", "date", limit=3)
print(result[["date", "temperature", "temperature_estimated"]])

Then show it in the workbook, so the flag is visible rather than buried in a column nobody reads:

import pandas as pd

def write_with_estimates(df, path, value="temperature", sheet_name="Readings"):
    """Write the series, tinting the cells that were interpolated."""
    flag = f"{value}_estimated"
    visible = df.drop(columns=[c for c in df.columns if c.endswith("_still_missing")])

    with pd.ExcelWriter(path, engine="xlsxwriter",
                        date_format="yyyy-mm-dd") as writer:
        visible.to_excel(writer, sheet_name=sheet_name, index=False)
        book, sheet = writer.book, writer.sheets[sheet_name]

        estimated = book.add_format({
            "bg_color": "#FDEFD8", "italic": True, "num_format": "0.0",
        })
        column = list(visible.columns).index(value)

        for offset, is_estimate in enumerate(df[flag], start=1):
            if is_estimate:
                sheet.write_number(offset, column,
                                   float(df[value].iloc[offset - 1]), estimated)

        sheet.write(len(visible) + 2, 0,
                    "Shaded, italic values were interpolated from neighbouring "
                    "observations and are estimates, not measurements.")
        sheet.set_column("A:A", 13)
        sheet.set_column("B:D", 16)
        sheet.freeze_panes(1, 0)

write_with_estimates(result, "readings.xlsx")

A tinted cell plus one sentence of explanation is the whole intervention, and it is what stops an estimate being quoted back as a fact three meetings later. The conditional-formatting alternative is covered in highlighting cells above a threshold with openpyxl.

Step 5 — Choose a method

method="linear" and method="time" cover most reporting needs. The others are worth knowing about but rarely worth reaching for:

Higher-order methods are where interpolation stops being conservative. A cubic fit through sparse business data can overshoot dramatically between points, producing a "filled" value well outside the observed range — plausible-looking, and wrong. For reporting, linear and time are the honest defaults.

Common pitfalls and fixes

Performance and scale notes

interpolate is vectorised and fast on a single series. The expensive shape is the group-wise version, which runs once per group in Python.

For many groups, that overhead dominates. Two mitigations. Filter out groups with nothing to fill before the loop — most groups in a typical frame are complete:

import pandas as pd

has_gaps = readings.groupby("region")["temperature"].transform(
    lambda s: s.isna().any()
)
subset = readings.loc[has_gaps]

Prefer transform over apply. transform returns an aligned result without constructing an intermediate frame per group, which on thousands of groups is a substantial saving.

There is also a correctness point that matters more than either at scale. Interpolating chunk by chunk does not work: a gap that straddles a chunk boundary has neighbours in two different chunks, so the fill either fails or uses the wrong values. If a file is too large to hold, interpolate per group after partitioning by the group key — never by arbitrary row ranges — so every series stays whole. The chunked reading approach in reading large Excel files in chunks still applies to getting the data in; the interpolation just has to happen after the rows for a given series are together.

Finally, interpolate once at the point the series is assembled, not repeatedly downstream. Each call rewrites the column, and a value interpolated from already-interpolated neighbours compounds the estimate without anything recording that it did.

Conclusion

Interpolation is only valid when the rows sit on a meaningful axis, so check that first and reach for a plain fill when they do not. Use method="time" for anything dated, set limit so an enormous gap is never bridged by a straight line, and always pass limit_area="inside" so the series is never extended past its last real observation. Group before interpolating a stacked frame, sort before either, and record the mask so every estimated cell can be tinted and labelled in the output. A reader who can see which numbers were estimated will trust the rest more, not less.

Frequently asked questions

When is interpolation the wrong choice? Whenever the rows are not ordered along a meaningful axis, or when a gap means the value was genuinely zero rather than unrecorded. Interpolating a category-keyed table invents a value from unrelated neighbours; interpolating a sales series across a closed month invents revenue.

What is the difference between method="linear" and method="time"? Linear treats rows as evenly spaced regardless of their index, so a one-day gap and a one-year gap are filled identically. Time uses the actual datetime index spacing, which is what you want for any irregular series.

Why did interpolate fill values at the end of my series? By default it extends forward past the last observation. Pass limit_area="inside" to fill only gaps that sit between two real values, which is almost always the correct behaviour.

How do I stop it bridging an enormous gap? Set limit to the largest number of consecutive gaps you are willing to fill. Anything longer stays NaN, which keeps the honest gap visible rather than drawing a straight line across half a year.

Should I mark which values were interpolated? Yes. Record the mask before filling and write it into the workbook as a flag column or a cell fill, so a reader can tell an estimate from a measurement.

Related

• Up to the parent: Handling Missing Data in Excel Reports — the wider set of strategies.
• Find and Report Missing Values in an Excel File — the audit that should precede any fill.
• Fill Missing Values in Excel with pandas fillna — the right tool when rows have no meaningful order.
• Group Excel Rows by Month and Quarter with pandas — filling the empty periods a summary would otherwise skip.
• Add a Line Chart to an Excel Report with Python — plotting a series where the gaps matter.