How to Fill In Missing Months in Excel
Your line chart jumps from January to March and nothing warns you. The data simply has no February row for that product, so every running total, moving average and period-over-period comparison built on it is quietly wrong.
Last updated: October 2026
| Sparse source | Complete grid | |
|---|---|---|
| Bolt · 2026-01 · 10 | → | Bolt · 2026-01 · 10 |
| Bolt · 2026-03 · 30 | → | Bolt · 2026-02 · (blank) |
| Nut · 2026-02 · 20 | → | Bolt · 2026-03 · 30 |
| Nut · 2026-01 … and so on |
Method 1 — Power Query cross-join
- Make one query of distinct products and another of every month.
- Cross-join them to get every combination, then left-merge your data onto it.
- Expand, then replace nulls with 0 if that is what you mean.
Method 2 — a PivotTable option
Grouping the date field and ticking Show items with no data fills the gaps in the display only. The underlying data stays sparse, so anything else reading it is still wrong — and the option only covers the date axis, not product × month.
The 1-click way — Tellsheet
Complete the Grid takes your group column and your period column and writes every combination to a new sheet, with the real values on the rows that existed. Dates and numbers are ordered properly; text labels keep the order you arranged them in. New rows are left blank by default — because an absent month can mean "no sales" or "no data", and filling 0 makes those two indistinguishable and invents measurements that will be charted as real. Choose zero only when you mean it.
Frequently asked questions
Why do my charts skip months?
Because the source has no row for that month. A chart can only draw the periods present in the data.
Should missing months be 0 or blank?
Blank unless you know the period genuinely had none. Zero averages into every statistic as a real measurement, so filling it where data was simply not collected biases the result.
Can I complete more than just months?
Yes — any two columns. Store × week, rep × quarter, category × region all work the same way.
What if a product/month pair appears twice?
The first is kept and the collision is reported, because two rows for one period need summing or deduping first — a decision the tool should not make for you.
Related Excel guides
Complete your grid in one click
Every group against every period — the rows your chart is skipping.
Get Tellsheet free See pricing