Skip to content
Tutorials Center
My learning

Topics

Lesson 52 of 93 ยท Charts and Visualization

Formatting and Improving Charts

Improve Excel charts: axis scale and honest axes, gridlines, colors, labels, secondary axis, trendlines, templates and alt text, with runnable stand-ins.

A default Excel chart is a starting point. It tends to have a placeholder title, heavy gridlines, a legend you do not need and an axis scale that Excel picked for you. A few well-chosen edits turn it into something a reader understands in five seconds.

This lesson walks through the formatting choices that matter most: axes (and how they can mislead), gridlines, colors, labels, a secondary axis, trendlines, reusable templates and accessibility. Excel's charts cannot run here, so the menu steps are described in text. Two JavaScript stand-ins show the calculations behind axis scales and trendlines. Menu names can vary a little by version and platform.

Make it clear

Remove clutter Fewer lines, no 3-D, no shadows.
Direct labels Label points instead of a big legend.
Real title Say what the chart proves.
1 / 2

Open the formatting pane

Select any part of the chart and press Ctrl + 1, or right-click and choose Format ... (for example Format Axis). The Format pane opens on the right with options for the selected element. Select the chart, then use the + button (Chart Elements) to switch parts on and off, and the brush (Chart Styles) for quick color sets. The Chart Design tab has Add Chart Element, Quick Layout and Change Colors.

A useful habit: change one thing at a time and look at the result. A chart improves mostly by removing things.

Axis scale and honest axes

Click the value axis and open Format Axis > Axis Options. You can set:

  • Minimum and Maximum: Auto by default. Typing numbers fixes them.
  • Major unit: the distance between labeled ticks (for example every 50).
  • Display units: show thousands or millions so labels stay short, plus a note in the axis title.
  • Logarithmic scale: each gridline is ten times the previous one. Use it only for data that spans several orders of magnitude, and tell the reader.
  • Number format under Number: for example #,##0 or 0%.

Bar and column charts must start at zero. Their message is the length of the bar. If you start the axis at 90 and the values are 95 and 100, a 5% difference looks like a 100% difference. A line chart may use a tighter range because the message is the shape of the change, but check that the scale does not turn small noise into a dramatic cliff. When you do truncate an axis, say so on the chart.

Other honesty rules: do not use 3-D effects that distort sizes, do not mix scales without labels, and keep the same scale when you compare charts side by side.

Gridlines, colors and fonts

  • Gridlines: keep light horizontal ones or remove them when you add data labels. Remove vertical gridlines unless they help.
  • Color: use one color for a single series, and highlight the one bar that matters in a strong color with the rest in gray. For several series use distinct, consistent colors. Never rely on color alone: red and green are indistinguishable for many readers, so add labels or different line styles or markers.
  • Contrast: dark text on a light background, at least 10-12 pt in a printed or projected chart.
  • Theme colors: pick them from the Page Layout > Colors menu so every chart in the workbook matches. Corporate templates usually define them.
  • Fonts: use the workbook font, and one size for axis labels and a slightly larger one for the title.

To color a single bar, click the series once to select all bars, then click the bar again to select only that one, and set Fill. To recolor negative values, tick Invert if negative in the fill options.

Labels

  • Data labels: Chart Elements > Data Labels. Choose position (outside end, inside end, center), content (value, category name, percentage) and number format. Show them only where the exact value matters.
  • Labels from cells: in the data label options choose Value From Cells and select a range. This lets you label points with any text, such as a product name on a scatter chart.
  • Axis titles: include units: "Revenue (USD thousands)".
  • Direct labeling: for line charts, label the end of each line with the series name and delete the legend. Readers do not have to match colors back and forth.
  • Titles: link a title to a cell with a formula so it updates itself, for example a cell with ="Sales, "&TEXT(MAX(A2:A13),"mmm yyyy").

Secondary axis

When two series have very different scales (revenue in thousands, margin in percent), right-click the smaller series and choose Format Data Series > Secondary Axis. Then change that series' type to a line with Change Series Chart Type. Give both vertical axes titles and match each axis' color to its series so there is no confusion. If you can avoid the second axis by drawing two small charts, do.

Trendlines

A trendline is a fitted line through a series. Select the series, click + > Trendline, and choose the type:

  • Linear: a straight line. Good for steady growth.
  • Exponential, Logarithmic, Power, Polynomial: curves for other patterns. Do not choose a high-order polynomial just because it fits.
  • Moving average: smooths a noisy time series over Period points.

In the options you can show the equation and R-squared on the chart, and forecast forward or backward by a number of periods. R-squared close to 1 means the line explains the data well; it does not prove cause and effect. The same numbers are available as formulas (SLOPE, INTERCEPT, RSQ, FORECAST.LINEAR), explained in Correlation, Regression and Forecasting.

Stand-in 1: axis ticks and the truncated-axis trick

Excel picks "nice" tick values like 0, 50, 100. The stand-in below uses a simple version of such an algorithm (not Excel's exact one) and then measures how much a truncated axis exaggerates a difference.

JS Nice axis ticks and the lie factor of a cut axis

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Stand-in 2: what a linear trendline calculates

A linear trendline uses least squares: the line that minimizes the squared vertical distances to the points. The code computes the slope, intercept and R-squared from first principles, the same numbers Excel shows as "y = mx + b" and "Rยฒ", plus a forecast. It is our model of the idea.

JS Linear trendline, equation, R-squared and forecast

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Templates and reuse

When you have a chart you like, right-click the chart > Save as Template. Excel stores a .crtx file. Later, choose Insert Chart > All Charts > Templates to apply it, or use Change Chart Type on an existing chart. The template keeps the formatting (colors, fonts, gridlines, labels), not the data. To reuse a chart with different data, copy it and use Select Data to point its series to new cells. You can also copy a chart into PowerPoint or Word and choose Keep Source Formatting & Link Data if it should keep following the workbook.

Accessibility and alt text

Charts are pictures to screen readers, so describe them:

  1. Right-click the chart and choose Edit Alt Text (or Format Chart Area > Size & Properties > Alt Text).
  2. Write the conclusion first, then the facts: "Column chart: sales rose from 120 in January to 162 in May, with a dip in March."
  3. Do not start with "Chart of", since the reader already knows it is a chart.

Also check contrast, do not use color as the only signal, and place the key numbers in a nearby table or the title so everyone gets them. Use Review > Check Accessibility to find charts missing alt text.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why should a column chart's value axis start at zero?

    Readers compare bar lengths. If the axis starts higher, small differences look huge.
  2. 2. What does a trendline's R-squared value tell you?

    R-squared measures fit. A high value does not prove cause and effect.
  3. 3. You have revenue in thousands and margin in percent on one chart. What is a good approach?

    A secondary axis lets each series use its own scale. Label both axes so readers know which is which.
  4. 4. Which alt text is best for a chart?

    Good alt text states the main conclusion and key facts without opening with the words "chart of".
  5. 5. What does Save as Template store?

    A .crtx chart template keeps the look (colors, fonts, labels), not the numbers.

Summary

  • Format one element at a time with Ctrl + 1 and the Format pane, and improve charts mostly by removing clutter.
  • Set axis minimum, maximum, major unit and number format; bar and column charts start at zero, and any truncated axis should be disclosed.
  • Use color with purpose, highlight one series, and never use color alone to carry meaning.
  • Use labels, direct labeling and linked titles; use a secondary axis only with clear axis titles.
  • Trendlines show a fitted line with equation and R-squared, and forecasts need caution.
  • Save a finished chart as a template, and write alt text that states the conclusion.

Next: Sparklines, Data Bars and Icon Sets.