Avoiding Misleading Axes in Excel Charts Built From Data

Every number in the chart can be correct and the chart can still lie. The axis does it: a vertical scale that starts at 94 instead of 0 turns a 2% dip into a cliff, and a second axis lets revenue appear to move in lockstep with the weather. Excel's automatic axis settings contribute, because they pick bounds for readability on the data, not for honesty about it. This article gives the handful of rules that keep an axis honest, how to set them in Excel and from code, and the cases where bending a rule is fine. It is one of the checks in charts from data pulled out of databases and follows choosing Excel chart types for query results.

The problem in one example

Monthly orders: 9,400, 9,450, 9,380, 9,500, 9,420, 9,470. The spread is about 1.2%. Plot them as bars with the axis running from 9,300 to 9,550 and the tallest bar looks four times the height of the shortest. Plot them from zero and the bars are nearly identical, which is the truth. Both charts use the same numbers. Only one tells a reader what happened.

Excel often picks the first version on its own. With a default axis ("Auto" for minimum), Excel chooses a minimum greater than zero when the data is clustered, because it wants to use the plot area well.

Rule one: bars start at zero

A bar's length encodes its value. Cut off the bottom of the bar and the visible length no longer measures the quantity, and the ratio between two bars no longer matches the ratio between the numbers. This applies to column charts, bar charts and stacked versions, and to area charts, where the filled area encodes the quantity.

In Excel: click the vertical axis, Format Axis, Bounds, set Minimum to 0. From code:

chart.set_y_axis({"min": 0})                     # XlsxWriter

chart.y_axis.scaling.min = 0                     # openpyxl

Set the maximum too if you want to control headroom, or leave it automatic; zero at the bottom is the part that matters. If differences are so small that bars at zero look identical, that is the finding. If you must show small differences, plot the difference or the percentage change as a separate chart, or use a dot plot, where position rather than length carries the value, and a non-zero axis is acceptable.

Rule two: lines may break the baseline, with care

A line chart encodes values by position and trends by slope. A reader judges whether the line goes up or down and by how much relative to the axis. Starting at zero is often unhelpful: a series of exchange rates between 1.08 and 1.12 drawn from zero is a flat line. A non-zero baseline is legitimate when the changes are meaningful within a narrow range.

The honesty conditions are these: the axis labels show the real numbers so the reader sees the range, the scale does not exaggerate noise into a story (a range of 1.0% stretched to fill the whole plot makes any wobble look dramatic) and the chart says what it is showing. When a line is used to argue "this is rising sharply", ask whether the same data on a scale starting at zero would still support the claim. If not, the claim lives in the axis.

Consider a log scale for series that grow by multiples, so equal percentage changes look equal. Label it as a log scale; most readers assume linear.

Rule three: avoid dual axes

A secondary axis lets two series with different units share a chart. Revenue in millions on the left, headcount on the right. The author can then choose each scale independently, and by choosing, create any relationship: stretch one axis until the lines overlap, and the reader sees correlation. Even with no intent to mislead, the visual association is arbitrary.

Better options:

• Two charts, aligned. Stack them vertically with the same time axis, so the reader compares timing without any scale being shared.

• Index to 100. Divide each series by its value in a base period and multiply by 100. Both series now start at 100 and show relative growth on one axis. State the base period.

• A scatter plot of one measure against the other, if the question is correlation.

When a dual axis is unavoidable, color-code each axis to its series, label the units on both, and say why in the caption.

Rule four: same scale for charts that are compared

Five regional charts, each with its automatic axis, make the smallest region look as important as the largest. If readers will compare charts side by side, give them the same bounds. Compute the global maximum in code and set it on every chart:

ymax = df[value_columns].to_numpy().max() * 1.1
for chart in charts:
    chart.set_y_axis({"min": 0, "max": ymax})

Small multiples depend on this. If the point is that each region's shape differs, independent scales are acceptable, but then say so ("each panel has its own scale") and avoid comparisons of height.

Rule five: do not clip silently

Axis maximums set below the data cut off the tallest bars or lines. Sometimes that is deliberate, to stop one outlier from flattening the rest. If you clip, show it: break the axis visibly, or annotate the clipped bar with its value ("4,200, off scale"). A reader who does not know that the bar is cut will believe the top of the plot is the top of the data.

Equally, check that a hard-coded maximum has not been outgrown. A fixed bound written last year may now cut off this year's peak. Compute bounds from the data, or add a check that fails when any value exceeds the axis.

Rule six: label what the axis measures

Every axis needs a title with a unit, and the chart needs a period. "Revenue" is vague; "Revenue (USD thousands), monthly" is not. If you divide values for readability, say so, and format tick labels to match: #,##0, shows thousands in Excel number formats. A date axis should show the granularity: "Mar 26" for a month, a full date for days.

Check the tick labels for crowding and unusual intervals. An axis with ticks at 0, 3, 7, 12 invites misreading; round major units to 1, 2, 5 or 10 times a power of ten: chart.set_y_axis({"major_unit": 5000}).

Other ways an axis misleads

Category order. A bar chart sorted alphabetically hides the ranking. Sort by value.

Time gaps. Plotting irregular dates on a text axis spaces them equally. Use a date axis ("date_axis": True) so that the gaps look like gaps.

Reversed axes. A vertical axis that runs downward, as in a chart where lower is better, confuses readers unless it is labeled. If you must reverse, say so.

Different baselines across a stacked bar. The segments above the first have no common baseline, so compare only the bottom segment directly.

Area under truncated lines. Shading beneath a line implies a quantity from zero. Do not shade a chart whose axis does not start at zero.

Percent axes beyond 100. Percent of what? Label the base.

Setting the axes from code

With XlsxWriter:

chart.set_y_axis({
    "name": "Revenue (USD thousands)",
    "min": 0, "max": 500, "major_unit": 100,
    "num_format": "#,##0",
    "major_gridlines": {"visible": True, "line": {"color": "#DDDDDD"}},
})
chart.set_x_axis({"name": "Month", "date_axis": True, "num_format": "mmm yy"})

With openpyxl, set chart.y_axis.scaling.min, .max, chart.y_axis.majorUnit, chart.y_axis.title and chart.y_axis.number_format. If axes disappear in new Excel versions, set chart.x_axis.delete = False and chart.y_axis.delete = False (adding charts to an Excel sheet from Python).

A short review before sending

Look at the chart as a stranger. Cover the title and ask what it seems to say. Does the vertical axis start at zero for bars? Could the same data on a different scale say something else? Do two charts that will be compared share a scale? Are units and period stated? Is anything clipped? If you can answer all of those, the axes are honest. The Edward Tufte tradition calls the gap between the effect in the data and the effect in the graphic the "lie factor", and the rules above are ways to keep it near one.

When a non-zero axis is fine

A non-zero axis is fine on a line chart of a quantity that cannot meaningfully reach zero, such as body temperature, a stock index or an exchange rate, provided the axis is labeled with real values. It is fine on dot plots and range charts. It is fine for a chart whose purpose is to show small changes in a stable system, such as tolerances in manufacturing, when the audience knows the context and the axis says what it is. It is not fine on bars, areas or anything where the reader will compare lengths. The openpyxl chart documentation shows the axis settings for that library, and the XlsxWriter axis options list the settings for code.