Line Charts and Time Series
A Line chart spaces its x-values evenly along a category axis, so readings taken on Jan 1, Jan 2 and Mar 15 land three equal steps apart. When Excel recognises those labels as genuine date values it can switch the axis to a Date axis, which spaces the points by elapsed time and lays out the empty days in between — but that depends on the cells holding real dates rather than text that merely looks like dates. When the x-values are measured numbers instead of dates, such as dose or distance or load, no axis setting rescues it, because a Line chart never reads the first column as numbers at all; that case belongs to an XY Scatter with straight lines, and picking Line where Scatter belongs is the most common wrong-chart mistake in Excel. Blank cells are the other trap: by default a gap in the source breaks the line in two, and Select Data > Hidden and Empty Cells decides whether that gap stays, reads as zero, or is quietly bridged.
The formula
A flat average line, as a second series:
C2: =AVERAGE($B$2:$B$13) fill down through C13
The absolute reference makes every row return the same number, so
the series draws as a horizontal line. Then:
Select Data > Add > Series values: =Sheet1!$C$2:$C$13
Chart Design > Change Chart Type > set that series to LineA worked example
A2:A13 holds the first day of each month of 2025 as real dates, B2:B13 holds monthly active users (4,120, then 4,310, then 4,180, rising to 7,640 in December), and August was never recorded, so B9 is empty.
Select A1:B13 and Insert > Line. Right-click the horizontal axis > Format Axis and compare Axis Type set to Text axis against Date axis, then open Select Data > Hidden and Empty Cells.
On a Text axis the twelve labels sit at equal intervals and August is simply absent, leaving a break. On a Date axis Excel lays out the whole calendar year, so the same break now occupies the right amount of horizontal space and the reader can see that a month is missing rather than assuming an unbroken run. Setting Hidden and Empty Cells to "Connect data points with line" closes the break, which looks tidier and quietly invents a reading nobody ever took.
Which one do I need?
| If you want to… | Use |
|---|---|
| Your x-values are real dates and the intervals between them are uneven | A line chart with Axis Type set to Date axis, so horizontal distance matches elapsed time |
| Your x-values are measured numbers rather than dates or category names | An XY Scatter with Straight Lines — a line chart would space them evenly and distort the shape |
| You are comparing a handful of unordered categories, not a sequence | A bar or column chart; joining unordered categories with a line implies a progression that does not exist |
| Several series share the chart and one of them lives on a different scale | Move that series to a secondary axis instead of rescaling the underlying numbers |
| You want a fitted straight line and its equation rather than the raw path | Right-click the series > Add Trendline > Linear, and tick Display Equation on chart |
| You need a flat reference line at the average, a target or a threshold | A helper column repeating the constant, added as a second series and switched to Line |
| The source data has blanks in the middle of the series | Select Data > Hidden and Empty Cells — gap, zero, or connect across; each one tells the reader a different story |
| The line belongs inside one cell, beside the number it summarises | A sparkline, which lives in the cell and needs no chart object |
| The series should accumulate, so the top line is the running total | A Stacked Line chart — worth labelling clearly, because the upper lines look like independent series but are not |
Frequently asked questions
Why does my line chart ignore the actual dates on the x-axis?
Because a Line chart uses a category axis, which places every label one equal step from the last regardless of the interval it represents. Right-click the horizontal axis, choose Format Axis, and set Axis Type to Date axis. If that option produces nothing sensible, the dates are stored as text: select the column and run Data > Text to Columns, finishing with the Date column format, then rebuild the axis.
Should I use a line chart or a scatter chart for my data?
Use a line chart when the x-values are labels in a sequence, such as month names or quarters, and you want them evenly spaced. Use an XY scatter when both columns are real measurements and their numeric values must position the points, which is the case for anything sampled at irregular intervals or plotted against a physical quantity. Scatter with Straight Lines looks identical to a line chart while treating the horizontal axis as numbers.
How do I add an average line to an existing line chart?
Add a helper column beside the data with =AVERAGE($B$2:$B$13) locked by absolute references and filled down, so every row holds the same value. Add that column to the chart through Select Data > Add, and it draws as a flat horizontal line at the average. This is more reliable than a trendline set to Moving Average, which follows the data instead of holding a single constant.
Why does my line break where data is missing?
A blank cell in the plotted range creates a gap by default, which is honest: nothing was measured there. Select Data > Hidden and Empty Cells offers two other treatments — show blanks as zero, which drags the line down to the axis and invents a real reading of nothing, or connect the points across the gap, which draws a straight segment nobody measured. A formula returning "" counts as text, not a blank, and breaks the line whichever option you pick.
New guides and tools, once a month
DE + EN · double opt-in · no spam