Stop an AI trend chart from connecting missing months
A smooth line can hide a month nobody measured. Tell AI what missing means before it draws a chart or explains a trend.
Example output
A chart that preserves the missing month
Fictional monthly values: 12, 0, unavailable, 18 and 16. A blank observation stays a gap.
The March observation is absent; February is a genuine observed zero. A line must not bridge that missing observation.
Read the prompt
Chart this supplied monthly dataset in chronological order.
Unit: [actual unit]. A blank means [source meaning]; zero is a valid
observed value. Show missing periods explicitly and break the line
at unavailable observations. Do not replace blanks with zero, join
across them or interpolate unless I explicitly request a labelled model.
Explain observed values and limits without inventing missing months,
causes or a continuous trend. Include the source table and actual file/source.What you'll make
a monthly trend chart that shows a gap where data is missing, keeps real zeros at zero, and comes with an explanation that doesn’t invent the missing month.
What you'll need
your monthly data with its unit, what a blank means in that data (not measured, not yet reported, system down) and the question the chart should answer. Use Excel or Google Sheets for the chart itself, or ChatGPT or Claude with a file upload. Allow about 10 minutes.
1. Set the empty-cell option if you’re charting in a spreadsheet
Spreadsheets decide gap, zero or connected line with one setting, and it’s the quickest fix:
- Excel: select the chart, choose Chart Design › Select Data › Hidden and Empty Cells, and set Show empty cells as to Gaps. This works for line, scatter and radar charts.
- Google Sheets: blank cells show as gaps by default. In the chart editor, check that Customize › Chart style › Plot null values is off; turning it on draws the line across the gap.
Leave missing months blank. Don’t type 0, a dash or “n/a” unless you know how the chart treats it.
Why this works: the chart can only show a gap if the cell is empty and the setting says so. A typed zero is a measurement, and the chart will plot it as one.
What you should see: a line that stops before the missing month and starts again after it, with real zeros sitting on the axis.
2. Tell AI what blank and zero mean
If you’re asking ChatGPT or Claude to chart an uploaded file, or Copilot or Gemini to chart the sheet, say what the blank means before anything is drawn:
Chart this monthly data in date order. The unit is [unit]. A blank
means [what blank means]; zero is a real measured value. Show every
month on the axis, including missing ones, and break the line at
missing months. Don't replace blanks with zero, join across them or
estimate them. Describe what was measured without inventing the
missing month or a cause for the changes.[unit] is what’s counted, such as “support requests”. [what blank means] is your source’s rule, such as “not recorded because the system was down”.
Why this works: chat tools that chart by writing code are where blanks most often turn into zeros: an empty text value converted to a number becomes 0. Saying so up front makes the model handle it on purpose.
What you should see: a chart with a visible gap, and an explanation that names the missing month rather than smoothing over it.
3. Ask to see the rows behind the chart
Before you trust the picture, check what the tool actually plotted:
Show me the rows you used for the chart: month, value and whether each
value was measured, zero or missing. Explain how you handled blanks.
If you made any assumption about a blank or a date, list it as a
question for me.Check the first, middle and last months, then the missing month and any real zero.
Why this works: the chart can look right while the table behind it has a zero where the blank was. The rows show the decision the picture hides.
What you should see: a table where the missing month is marked missing, not 0, and every month is present.
Check the result
- Every month appears on the axis, including the missing one.
- The line breaks at the missing month instead of dropping to zero or joining its neighbours.
- A real zero is plotted at zero and described as a measured zero.
- The explanation doesn’t describe a trend, recovery or cause across the gap.
The usual failure: the chart joins the months either side of the gap, or drops to zero there. Both look plausible. Spot it by finding the missing month on the chart: if there’s a point or a straight line where nobody measured, it’s wrong.
The chart [joins across / sets to zero] the missing [month]. That month
wasn't measured. Keep it missing, show it on the axis and break the
line there. Keep the real zero and every other value unchanged. Correct
any sentence that implies a measured value or a trend across the gap.Worked example: February zero, March missing
Fictional example. A team tracks monthly requests to its internal help desk. The logging system was down in March, so March was never recorded.
Month, Requests
January 2026, 12
February 2026, 0
March 2026,
April 2026, 18
May 2026, 16The first chart, made in a chat by uploading the file, drew a smooth line from 0 in February to 18 in April. Its summary said: “Requests recovered steadily through March after a quiet February.”
| Month | First chart | After the repair |
|---|---|---|
| February | 0, plotted | 0, plotted |
| March | about 9, on the joining line | gap, labelled “not recorded” |
| April | 18 | 18 |
- February is a real zero; March is not. The data says so, but nothing told the chart. The step 2 prompt states the rule up front.
- “Recovered steadily through March” was invented. Nobody measured March. The repair prompt, filled in with “joins across” and “March”, produced a gap and the summary “March wasn’t recorded; April was 18”.
- The rows showed it first. Asking for the rows behind the chart revealed March had been dropped and its neighbours joined.
The same check matters for any monthly business metric, such as sign-ups, cancellations or tickets. Counts alone can’t give you a conversion or retention rate: you also need the number of people who could have converted or stayed.
Task kit for this job: Download (ZIP) · Reusable instructions
Go deeper
Check the exported file as well as the preview
A chart can show a gap on screen while the exported table quietly fills it with zero. Open the exported data file, or the chart in the slide or report it ends up in, and find the missing month again. Keep the note about what blanks mean beside the chart.
Check percentage changes from zero
You can’t calculate percentage growth from a zero baseline: 18 compared with 0 isn’t a percentage at all. If the model reports “+∞%”, “0%” or a large made-up figure, ask it to report the change as a count (“up 18 requests”) or to leave the percentage out.
Test the method on awkward rows
Before you reuse a charting prompt every month, run it on a small file with a real zero, a blank and a missing row. The known-answer guide shows how to write the expected answers first and check the method against them.