Fix a Line Graph That Doesn’t Match Your Data

If your line graph is not representing data properly, start with the plotted points—not the line’s color or style. Pick three observations from the spreadsheet and check their x positions and y values on the chart. This separates incorrect data selection from misleading presentation.

Before sharing the graph, work through these checks in order: source range, value types, horizontal spacing, missing values and vertical scale.

1. Check which cells the chart actually uses

Compare the chart’s source range with the table you intend to publish. Check that:

• The first and last observations are included, including any newly added rows.
• The date or x-value column supplies the horizontal positions or labels, rather than becoming an extra plotted series.
• Each series uses the correct value column.
• Subtotals and grand totals are excluded unless they are deliberately part of the analysis.
• Chart filters have not hidden observations you meant to show.

Excel desktop for Windows: Right-click the chart and choose Select Data. Under Legend Entries (Series), select a series and choose Edit to inspect its source. Excel also has chart filters that can show or hide series and categories. Microsoft’s data-series guide documents both controls.

Google Sheets on a computer: Double-click the chart, open Setup, and inspect Data range. Use the grid control to select the intended cells. Check Chart type and the header settings in the same panel. Google’s chart-editing guide explains these settings.

2. Check that values are numbers, not numeric-looking text

An imported cell can look like a number without being stored as one. Inspect suspicious cells for apostrophes, spaces, unit labels or decimal conventions that differ from the spreadsheet’s locale.

In Excel, if a numeric cell has a “number stored as text” warning, select the affected cells, open the error indicator and choose Convert to Number. Microsoft also documents using a helper column with =VALUE(B2) to convert recognizable numeric text. See the conversion instructions.

Review the converted values before replacing the originals. Convert measurement columns, not identifiers such as postal codes whose leading zeros matter. Keep units in headers—for example, elapsed_minutes—rather than appending “minutes” to every value.

3. Check whether the x-axis preserves real spacing

A graph can look plausible while misrepresenting the intervals between observations.

Illustrative dataset—not measured results:

elapsed_minutes	reading	status
0	20	observed
1	22	observed
10		not recorded
11	26	observed

A category-based line chart can put the four x-axis categories at equal horizontal intervals. That makes the nine-minute interval from 1 to 10 look the same as the one-minute interval from 0 to 1.

For numeric x-values where distance matters, use an XY scatter chart with a linear horizontal scale. Minute 10 should appear nine times farther from minute 1 than minute 1 is from minute 0. Add connecting lines only if connecting successive observations is meaningful.

Excel distinguishes a scatter chart’s two numeric axes from a line chart’s category axis. A line chart can also use a date axis, which supports chronological spacing in days, months or years. Don’t assume date-looking text has produced a date axis. Microsoft explains the axis differences.

For time-series data, sort the whole table chronologically, not just the date column. For unordered categories such as departments, consider a bar chart instead; use the chart-type decision guide to match the display to the question.

4. Check what blanks mean on the graph

In the sample, minute 10 has no recorded reading. It is not zero.

Excel normally displays empty cells as gaps and excludes hidden worksheet rows and columns. Its Hidden and Empty Cells settings can instead plot blanks as zero or connect points across them. Microsoft documents these options.

For an unrecorded observation, preserve the missing value and explain it. A connecting segment across the gap is not a measurement at minute 10. If you estimate a missing value, label it as estimated and retain the original missing-value status in the published table.

5. Check the y-axis and publish the evidence

A change from 100 to 110 is a 10% increase, regardless of how dramatic it looks. Inspect the y-axis minimum, maximum, units and whether the scale is linear or logarithmic. If there is a second axis, check which series uses it.

Choose a scale that makes the size of the change understandable. If you narrow the displayed range to highlight small changes, make the axis limits clear. Label units; use separate charts when different measures become difficult to compare honestly.

Before publishing, compare the first, last and one middle observation against the chart again. Share the underlying table with a description of the row unit, source, date coverage and missing-value convention. Remove sensitive information before making any dataset public. The spreadsheet-cleaning checklist covers preparation for public sharing.