Spreadsheet Charts: Match the Chart to the Question

Spreadsheet charts: choose the question before the chart

A chart turns a table into a visual claim. Before accepting the spreadsheet’s default, decide whether the question compares categories, follows an ordered sequence or investigates paired numerical measurements. Then verify which cells supply the labels and values. A polished chart with the wrong mapping still answers the wrong question.

The three datasets below are fictional teaching data, unrelated to any real learners. Each belongs on its own worksheet, starting at A1 with headers in row 1. The diagrams are original chart models. Software examples refer to LibreOffice Calc; labels and controls can differ in other applications and versions.

A three-way decision

A data series is the set of related values plotted together. A category label identifies an item or period; it is not automatically a numerical measurement. These examples use one series each, so a long legend is unnecessary. The title identifies the subject, while axis titles identify quantities and units.

Worked case 1: compare category counts

Question: which material category had the greatest number of items issued in a fictional session?

Specification: Select A1:B5. Choose an ordinary, unstacked column chart with series in columns, the first row as the series label and the first column as category labels. Categories are A2:A5; values are B2:B5. Use a vertical count scale from 0 to 20, with ticks every 5. Keep equal-width columns and equal gaps.

Clips has the highest count, 18, which is 6 more than Rulers at 12. Boxes has 9. The supported statement is about this session’s issued items; the table says nothing about stock remaining or which category is most useful. Joining these unrelated categories with a line would suggest an ordering the question does not require.

Worked case 2: follow monthly use

Question: how did a shared display screen’s recorded use change across five consecutive months?

Specification: Select A1:B6. Choose a line chart with points and straight connecting segments. Use months A2:A6 as ordered categories and hours B2:B6 as one series. Keep Jan, Feb, Mar, Apr and May in calendar order, not alphabetical order. The vertical scale is 0–40 hours with ticks every 10.

Use rises from 20 to 28 hours, falls to 24, rises to 36 and falls to 32. The largest adjacent increase is March to April: 36 − 24 = 12 hours. January to May has a net increase of 12 hours too, but that does not mean use increased every month. The line joins the supplied monthly observations; it does not provide daily records.

This chart treats months as equally spaced categories. It compares monthly totals rather than equal numbers of elapsed days. If observations had irregular numerical times and the gaps mattered, an XY chart with numerical time values would preserve those gaps.

Worked case 3: map paired measurements

Question: what pattern is visible between fictional file sizes and recorded import times in one test setup?

Specification: Use an XY scatter chart with points only. Set X-values to A2:A6 and Y-values to B2:B6; use B1 for the series name. Both numerical axes span 0–12, with ticks every 3. The size and time from each row must stay paired. Row 4, for example, is the point (7 MB, 7 s).

The horizontal gap from 3 to 7 MB must be four times the gap from 2 to 3 MB. A category line chart using the five sizes merely as labels could space them equally and conceal that difference. Do not use row numbers 1–5 as the x-values: that would plot observation order instead of file size.

These five observations show that larger sizes accompany longer times. They do not prove that size alone caused the difference or predict an exact time for every future file. Other features of the files and setup are unspecified. Points remain unjoined because no continuous path between these separate observations was measured.

Two repairs that protect the meaning

Repair a wrongly mapped series

A scatter chart shows points (1,3), (2,4), (3,7), (4,9), (5,11). The import-time values are correct, but the x-values are row positions. In Calc’s data-series controls, inspect the X-values role and assign the size range A2:A6. The corrected points must be (2,3), (3,4), (7,7), (11,9) and (12,11), as shown above. Check the first, middle and last point against their source rows.

Repair a truncated column axis

If the category chart starts at 8 instead of 0, the visible bar lengths for Boxes and Clips become 9 − 8 = 1 and 18 − 8 = 10 units. A reader comparing lengths might perceive a tenfold difference even though 18 is only twice 9.

The corrected panel starts at zero and shows the true two-to-one comparison. A labelled truncated scale is still a poor choice when this task asks the reader to compare bar lengths. Keep the full zero baseline rather than trying to repair the impression with a footnote alone.

Three solved choice and checking tasks

Task 1: A worksheet lists four shelf labels and their box counts. Should a line join them? Solution: Choose columns for category comparison. Shelf names do not create a time sequence. Use a count axis beginning at zero and retain the supplied labels.

Task 2: Dataset 2 has only four plotted months because the selected range ends at row 5. What is the repair? Solution: Extend the source range to A1:B6. May, 32 hours, must be included. Changing the title cannot restore a missing observation.

Task 3: For Dataset 3, a learner sorts only column A and leaves column B unchanged. Is the chart still trustworthy? Solution: Not necessarily: the operation can break row pairs. Keep each size attached to its recorded time when reordering. Verify all five pairs against the original table before interpreting the chart.

A final source-to-chart check

Count the plotted observations, confirm the first and last data rows, read both axis titles, and compare one middle value directly with the table. Check that a total row has not accidentally become an extra category. Write one sentence supported by the chart and one limit on what it tells you.

LibreOffice’s primary guides explain chart choice, line category axes, XY value pairs and data-series roles. In a value role, select the data cells rather than including its heading cell.