Spreadsheets

Fill missing months so a time-series chart has no gaps

Months with no transactions vanish from pivots and charts. Build a full month list and SUMIFS against it so zeros show and the time axis stays even.

Editorial illustration for Fill missing months so a time-series chart has no gaps

Months with no transactions disappear from pivots and charts because they have no rows to summarise. To stop it, build a complete month list with =EDATE(previous, 1), then pull each month's total from your data with SUMIFS. Months with nothing return 0, so every month appears, and you can make the chart axis a date axis so spacing stays even.

Why do months go missing?

A pivot or chart can only show what exists in the data. If no transaction falls in June, no June row exists, so depending on how the pivot is grouped, June can be left out and the line jumps from May to July. The same happens if you chart a list of totals built from a pivot.

Make the list of months yourself

Type the first month's start date in A2, for example 1 April 2026. In A3 write =EDATE(A2,1) and fill down for as many months as you need. Format the column as mmm yyyy. In Excel 365 or 2021 you can create the entire list in one cell with =EDATE(A2, SEQUENCE(12, 1, 0, 1)), which spills twelve months.

Join your data to it

In B2 write the total for that month:

=SUMIFS(Data[Amount], Data[Date], ">="&A2, Data[Date], "<"&EDATE(A2,1))

Fill down. The month list is the master and the formula looks up the data, which is what database people call a left join: every month stays, whether or not anything matches. A month with no transactions returns 0 instead of vanishing.

MonthPivot showsMonth list with SUMIFS
Apr 202662,00062,000
May 202671,00071,000
Jun 2026(missing row)0
Jul 202674,00074,000
Aug 202669,00069,000

The figures are invented. The left column shows how a pivot might skip June; the right column keeps it.

Is a zero month really zero?

Be careful. A month with no rows may mean you never uploaded or entered that month's statement, not that you spent nothing. Before charting a zero, check the statement or account history for that month. If it was a missing file, add the data. If the month is not complete yet, use =IF(A2>TODAY(), NA(), SUMIFS(...)) so the line stops rather than falling to zero.

Set the chart axis so spacing stays even

  1. Build the chart from the month list and the totals.
  2. Right-click the horizontal axis and choose Format Axis.
  3. Under Axis Type, choose Date axis, and set Base unit to Months.
  4. Under Select Data, choose Hidden and Empty Cells and decide whether #N/A or empty cells show as gaps or zero.

If your category cells are text such as Apr 2026, Excel uses a text axis and plots only what you listed. If they are real dates, a date axis spaces them by time, so a month left out of the list would show as an empty slot rather than a squeezed line.

Make it grow by itself

  • Make the month list a Table so charts grow with it.
  • Start the list at the first month with data and extend it each month, or generate twelve with SEQUENCE.
  • Use the same list for several totals: spending, income and savings.
  • Add a check column with =COUNTIFS(Data[Date], ">="&A2, Data[Date], "<"&EDATE(A2,1)) to see how many transactions each month has.

A zero in the check column is the cue to look for a missing statement. This is a simple way to keep gaps honest.

When gaps keep appearing

Missing months usually come from imports done irregularly. SpendixAI lets you upload statements for each account as PDF, CSV or Excel and keeps an import history with status, so a month that was never uploaded stands out. For a sheet, the month list and the count column above do the same job.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports