This guide covers the decision between the two chart types, the date-axis settings, and how to handle gaps honestly. The reference points come from Microsoft Support documentation. The CASRAI guide published on 7 November 2026 is the starting point, and a few of its claims are stronger than Microsoft's documentation supports. Those are flagged below.
Line chart or scatter chart?
Microsoft's guidance says the main difference is how each chart plots the horizontal axis. In a line chart, the horizontal axis is a category axis. It can be a text axis, which spaces labels evenly, or a date axis. A date axis puts dates in chronological order at set intervals, such as days, months, or years. It does this even if the worksheet dates are not in order or share the same base units.
A scatter chart works differently. It has two value axes, so the x-axis can show numbers or date values stored as numbers, such as days or hours. Points can land at uneven horizontal intervals, and the x-axis scale can be adjusted more flexibly.
In practice:
- Line chart with a date axis: use it for a calendar time scale, such as daily, monthly or yearly observations. A date axis still spaces irregular dates proportionally.
- Scatter with straight lines and markers: use it when you want full control over x-axis scaling, or when timestamps are better treated as plain numbers. This often applies to irregular or sub-daily records.
- Not a ranking: scatter is not automatically better. Microsoft says a line chart is ideal for a time scale or evenly spaced categories, while scatter suits numeric x-values.
Setting up the worksheet
Put dates or timestamps in one column and each measurement in an adjacent column. Give each column a header with units. CASRAI suggests putting dates in column A. That is sensible, but it is a convention rather than an Excel requirement. For a scatter chart, Microsoft says to place the x-values in one row or column and the matching y-values in the adjacent rows or columns.
Confirm that dates are real dates and not text. CASRAI says valid dates right-align by default, which is a useful hint. Right-alignment alone does not prove the data is clean, so check the axis behaviour as well.
Building and configuring the chart
- Select the data range.
- On the Insert tab, choose Insert Line or Area Chart and then Line with Markers. For scatter, choose Insert Scatter (X, Y) or Bubble Chart and then a straight-line option.
- For a line chart, right-click the horizontal axis and choose Format Axis. Microsoft's steps are to open Axis Options, confirm Date axis under Axis Type, then choose Days, Months, or Years next to Base.
- Set the major unit to control tick spacing. CASRAI's example is Base set to Months with a major unit of 3 for quarterly ticks. Treat that as one illustration, not a rule.
- For multiple series, use Chart Design > Select Data and check that each series has the intended X and Y ranges.
Two limits apply to date axes:
- Excel normally picks the base unit from the smallest gap between any two dates in your data, and you can override it.
- A date axis is not possible if the dates appear in the legend. The dates must sit on the category axis. Date axes are also unavailable on PivotChart reports.
Handling missing observations
Missing data is where a research chart can mislead most. By default, empty cells and null values appear as gaps. For line, scatter and radar charts, you can instead show empty cells as zero or span the gap with a line.
To change the setting:
- Select the chart.
- Go to Chart Design > Select Data (Microsoft's text refers to Chart Tools > Design).
- Choose Hidden and Empty Cells.
- Under Show empty cells as, pick Gaps, Zero or Connect data points with line.
Some cautions:
- Zero plots a data point of zero where nothing was measured. Use it only if zero is a real, verified value.
- Connect data points with line draws an interpolated line. That line implies continuity and linear change that nobody measured. If you use it in a publication, say so in the caption.
- On a scatter chart that shows only markers, with no connecting lines, you can show empty cells as gaps or zero only.
- #N/A is not the same as blank. Microsoft's page describes a Show #N/A as an empty cell option. The Microsoft source packet reports that this option is currently limited to Microsoft 365 Insiders and is off by default. Do not assume it exists in your build. A forum answer on Microsoft Q&A suggests returning NA() instead of "" in formulas so that a line chart treats the cells as empty. That is community advice, not official documentation.
Troubleshooting
Irregular dates look evenly spaced, or order looks wrong. Check whether the horizontal axis is set to Text axis instead of Date axis. If the Date axis option is unavailable, the cells may hold text instead of true dates. CASRAI recommends converting them with DATEVALUE or Text to Columns. That is a reasonable approach, but Microsoft's pages reviewed here do not document those steps. Test on a copy of your data.
Hourly or minute-level data looks wrong on a line chart. CASRAI says a line-chart date axis cannot go below one day and that sub-daily data must use scatter. The Microsoft pages reviewed do not confirm that as a hard limit, and Microsoft's date-axis page lists only days, months and years as base units. For finer time resolution, use a scatter chart, where Excel treats date-times as numbers. Whichever chart type you use, check that the spacing of the plotted points matches your timestamps in your own Excel version.
Lines drop to zero. Look at the Hidden and Empty Cells setting. Also check whether formulas return "" or 0 where a true blank or NA() was intended.
Reading the output
- Horizontal distance should reflect elapsed time. A visible gap in sampling should look like a gap, not a continuous line.
- Label axes with units. Document the sampling protocol alongside the figure.
- Do not read short-term wiggles as trends. Whether they are noise or seasonal variation takes analysis that a chart alone cannot give you.
Access and alternatives
CASRAI says Excel reaches researchers through institutional Microsoft 365 licences and perpetual editions such as Office 2024, 2021 and 2019. It also says the web app lacks the fine control that desktop Excel offers over date axes and the Hidden and Empty Cells dialog. Those access and web-parity claims are not verified by the Microsoft pages reviewed here. Check your own deployment.
CASRAI also lists R, Python with pandas and matplotlib, LibreOffice Calc and Google Sheets as free options. Scripted tools like R and Python make a stronger audit trail when analysis must be reproducible.
Bottom line
Use a line chart with a date axis when you want a calendar scale. Use scatter when the horizontal values are numeric or finely timed. Leave empty cells as gaps unless you have a reason to show otherwise, and say plainly when a line connects points that were never measured.