4 Charting Spreadsheet Data

Choose a chart for the question

Begin with a clear question: what should the reader notice? Choose a chart type that addresses that question, then format it so the data is easy to interpret. Common chart uses are guidelines, not rigid rules, and a more elaborate chart is not automatically clearer.

• Use a Bar chart or column chart to compare values across categories, such as sales by product. Bar charts can work well when category names are long.

• Use a Line chart to show change over ordered time, such as monthly sales across a year. Keep time periods in chronological order.

• Use a Stacked chart to show parts of a whole, such as departments’ shares of a fixed total. A pie chart may also suit a small number of categories that add up to one meaningful whole.

• Use a Histogram to show a distribution, such as the frequency of test scores within score ranges.

• Use a Scatter chart to examine a relationship between two numeric variables; each point represents a pair of values.

• Use a Combination chart to compare measures with different units or scales, such as revenue as columns and profit margin as a line. A Secondary axis can help, but may make comparisons harder, so label it clearly.

Stacked charts emphasize totals and composition, while grouped bars make individual category values easier to compare.

Build a chart from the data

Arrange the values in a rectangular range with clear row or column headings. Put categories, such as dates or product names, in one column and the values to compare in neighboring columns. Remove unintended blank rows, totals, and unrelated fields from the range you plan to chart.

Select the range, including its headings, and choose a chart from the spreadsheet’s Insert menu. Recommended charts can be useful to preview, but check whether the suggested chart fits your question.

Check the data mapping: categories should be assigned to the category axis, and each numeric column should be a separate Series. If the chart groups the data incorrectly, switch rows and columns or edit the data range.

Refine and verify the chart. Check that all intended categories and values appear, that their order is sensible, and that the chart updates if the source data changes. In Excel, for example, select the data and use Insert > Recommended Charts or choose a chart type directly.

Format charts for clarity

Give the chart a title that states what is being measured and, when useful, the time period or population. Label the axes and include units, such as revenue measured in thousands of dollars. Use a legend when readers need to identify multiple series; remove it when there is only one series and the legend adds no information.

Use a small, consistent color palette. Do not rely on color alone to distinguish important series; labels or different line and marker styles can help. Add data labels when exact values matter, but avoid crowding a large dataset with labels for every point. Keep gridlines subtle and useful.

Check axis scales carefully. Bar and column charts used for magnitude comparisons usually should start at zero, because a Truncated axis can exaggerate differences. A line chart may use a different vertical range to reveal small changes, but make that scale clear.

Avoid unnecessary three-dimensional effects and decorative elements that obscure values. Make text large enough to read, and provide a text description when sharing a chart with people who may not be able to see it. Chart titles, axes, series, legends, colors, and gridlines can generally be edited after the chart is created.

Interpret patterns with care

Read the title and axis labels first; they establish what the chart measures, its units, and its scale. Then look for the pattern that answers the original question: which category is largest, whether a measure is rising or falling, whether there are unusual values, or whether two numeric variables appear related.

A line chart of monthly sales can reveal an upward trend or a seasonal dip. A scatter chart may show that higher advertising spending tends to accompany higher sales, but the chart alone does not prove that advertising caused the increase.

Check the underlying values and the scale before drawing conclusions. Consider whether missing data, unusual observations, or the choice of chart could change the impression.