Module 4 lab: making charts people can read

Work through this at your own pace. Each step ends with a check – something you should be able to see on your screen before moving on. If a check fails, that is the point to put your hand up rather than pushing on.

Module 4 covers the same ground in two tools. Excel builds a chart by clicking on a range; ggplot builds one by naming which column goes on which axis. This lab does the bar chart in Excel and the line chart in R, then swaps, so you see each tool doing the job the other one did.

Both tools use the same two files today, which is deliberate: when the same data gives you a different chart in the two programs, the difference is yours, not the data’s.

Plan on about an hour for the nine steps, and half an hour more for the Practice boxes. It covers 10  Graphing best practices, 11  Graphing in Excel and 12  Graphing in R. If you run out of time, Step 7 onwards is the part to finish at home.

Step 1: Set up

You need both tools open today.

Open Positron and open your AREC_261 folder, the one from the Module 2 lab. Every read_csv("data/...") below is relative to the folder Positron has open, so opening the script on its own makes the reads fail with a “does not exist” error.

Download these two files into data/:

Make a new R script in code/ called module4_lab.R, with a header:

# ---
# Title: Module 4 lab
# Author: Your Name
# Date: 2026-09-28
# Description:
#   Bar charts, line charts and saving figures.
# ---

# Load packages
library(tidyverse)
library(janitor)

Read the barley file and look at it:

# One row per barley variety: 2025 acres and average yield
barley_2025 <- read_csv("data/sask_barley_2025.csv") |>
  clean_names()          # column names to lowercase

glimpse(barley_2025)

Check: glimpse() reports 32 rows and 3 columns, named variety, acres and yield_bu_ac. If your column names still have capitals, the clean_names() line did not run.

Now open the same CSV in Excel. Double-clicking is safe here – there are no identifier columns to mangle, which is why this file and not elevator_tickets.csv. Save it as module4_lab.xlsx so you are not working in a CSV.

Step 2: A bar chart in Excel

Thirty-two varieties is too many bars. Before charting anything, cut the table down.

In the sheet, sort the data by Acres, largest first: click a cell in the column, then Data > Sort Z to A. Then look at where the acres fall away.

Check: SYNERGY AAC is at the top with 411,620 acres. Fourteen varieties are above 10,000 acres and the remaining eighteen are below.

Delete the rows below 10,000 acres, or copy the top fourteen to a fresh sheet. Charting a small summary table rather than the full data is the normal way round – Managing the data for graphing in Excel has the reasoning.

Select the Variety and Acres columns, including the headers, and choose Insert > Bar Chart > Clustered Bar – the horizontal one, not Column.

Horizontal bars because the variety names are long. On a column chart Excel rotates them to 45 degrees or hides half of them; on a bar chart they sit flat and readable.

Now fix the order. Excel plots the first row of your table at the bottom of a bar chart, so a table sorted largest-first gives a chart with the biggest bar at the bottom. Either sort the table A to Z instead, or right-click the vertical axis, choose Format Axis, and tick Categories in reverse order.

Add the labels. Click the chart title and type Barley acres by variety, Saskatchewan 2025. Then Chart Design > Add Chart Element > Axis Titles > Primary Horizontal and type Acres.

Check: the longest bar is at the top, it is SYNERGY AAC, and the horizontal axis is labelled. Every variety name is readable without tilting your head.

NoteThe same thing in R

Three lines:

barley_2025 |>
  filter(acres > 10000) |>
  ggplot(aes(x = acres, y = fct_reorder(variety, acres))) +
  geom_col()

fct_reorder(variety, acres) is the reverse-order tickbox. The difference is that the R version re-sorts itself when the data changes, and the Excel one has to be re-ticked.

Practice

Make a second copy of the chart and change the horizontal axis to start at 100,000 instead of zero (Format Axis > Bounds > Minimum). Put the two side by side. Which varieties look roughly equal on one chart and clearly different on the other?

Everything below about 150,000 acres collapses toward the left edge, so the eight smallest varieties in the chart look near-identical when they range from about 10,000 to 60,000 acres – a sixfold difference.

Truncating the axis on a bar chart is the case where it is always wrong, because a bar says “this much” by its length. A line chart is different: there the reader is looking at the shape, and a truncated axis is often the honest choice. General principles has both.

Step 3: The same bar chart in R

Build it up one layer at a time so you see what each line does. Type this into your script and run it:

# Varieties over 10,000 acres
barley_2025 |>
  filter(acres > 10000) |>
  ggplot(aes(x = acres, y = variety)) +
  geom_col()

Check: a horizontal bar chart of 14 varieties, in alphabetical order from the bottom up.

Alphabetical is ggplot’s default for text, and it is almost never what you want. Add the reorder:

barley_2025 |>
  filter(acres > 10000) |>
  ggplot(aes(x = acres, y = fct_reorder(variety, acres))) +
  geom_col()

Check: SYNERGY AAC is now the longest bar, at the top.

Now the labels. fct_reorder(variety, acres) is an ugly axis title, and the default acres is not much better:

barley_2025 |>
  filter(acres > 10000) |>
  ggplot(aes(x = acres, y = fct_reorder(variety, acres))) +
  geom_col() +
  labs(
    title = "Barley acres by variety, Saskatchewan 2025",
    x = "Acres",
    y = NULL                      # the varieties label themselves
  ) +
  scale_x_continuous(labels = scales::comma) +
  theme_classic()

y = NULL removes the axis title. The variety names are already on the axis, so a label saying “Variety” adds nothing. scales::comma turns 4e+05 into 400,000.

Check: the chart has a title, a comma-formatted axis, no grey background, and no label on the vertical axis.

Count the lines it took in each tool. The Excel chart was maybe eight clicks and the R chart is nine lines, which is roughly a draw. The difference is that when the data changes, the R chart is rebuilt by running the script again.

Step 4: Save the figure

A chart is only useful once it is out of the tool. In R:

ggsave("output/barley_acres.png", width = 7, height = 4.5, dpi = 300)

ggsave() with no plot named saves the last chart drawn. The width and height are inches, and they set the proportions of everything – make the figure smaller and the text gets relatively bigger.

Check: output/barley_acres.png exists. Open it. The text should be legible at the size it will actually be used.

If you get Error: Cannot find directory output, the folder does not exist. Make it with dir.create("output").

In Excel: right-click the chart border and choose Save as Picture, or copy the chart and paste into Word with Paste Special > Picture. Pasting a chart normally embeds a live link to the workbook, which breaks when you move either file.

Step 5: A line chart in R

Lines are for things measured over time. Read the second file:

# Risk-zone records for all crops, varieties and years
variety_yields <- read_csv(
  "data/sask_variety_yields.csv",
  show_col_types = FALSE
) |>
  clean_names()

glimpse(variety_yields)

Check: 14,700 rows and 6 columns: risk_zone, crop, variety, year, acres, yield.

That is far too much to plot. Cut it to one variety in one risk zone:

variety_yields |>
  filter(variety == "SYNERGY AAC", risk_zone == 1) |>
  ggplot(aes(x = year, y = acres)) +
  geom_line()

Check: a line rising from about 4,300 acres in 2021 to about 9,200 in 2025, with a dip in 2024.

Five points, so show them:

variety_yields |>
  filter(variety == "SYNERGY AAC", risk_zone == 1) |>
  ggplot(aes(x = year, y = acres)) +
  geom_line() +
  geom_point() +
  labs(
    title = "Synergy AAC barley acres, risk zone 1",
    x = NULL,
    y = "Acres"
  ) +
  theme_classic()

geom_point() after geom_line() draws the dots on top of the line. Layers stack in the order you write them.

Check: five visible points on the line.

With this few observations the points matter. A bare line between five yearly values suggests there is something between them, and there is not – no measurement was taken in June 2023.

Practice

Plot yield instead of acres for the same variety and zone. Then try a variety that is not in the data, for example filter(variety == "SYNERGY"). What do you get?

The yield line runs 74.9, 87.2, 72.5, 83.2, 89.1 – up and down with the weather, with no trend. Acres trend upward and yields do not, which is what you would expect: growers decide acres, and weather decides yield.

variety == "SYNERGY" matches nothing, because the name in the file is SYNERGY AAC and == is exact. You get an empty plot: axes, no line, no error. ggplot draws whatever it is given, and zero rows is a legal thing to be given. An empty chart nearly always means the filter, not the chart.

Step 6: Missing values, which do not announce themselves

This file has gaps. Count them:

sum(is.na(variety_yields$acres))

Check: 4,582 of 14,700 rows have no acres value – just under a third.

Those are variety-zone-year combinations that were not grown or not reported. Plot a series with a hole in the middle of it:

variety_yields |>
  filter(variety == "ADVANTAGE AB", risk_zone == 21) |>
  ggplot(aes(x = year, y = acres)) +
  geom_line() +
  geom_point()

Check: the line breaks between 2021 and 2023 – there is no 2022 value – and there are four points, not five. The console warns that one row containing a missing value was removed.

That is the well-behaved case. The break is visible and R told you about it. Now the dangerous one:

variety_yields |>
  filter(variety == "GOLDSTAR", risk_zone == 16) |>
  ggplot(aes(x = year, y = acres)) +
  geom_line() +
  geom_point()

Check: a line from 2021 to 2023 that simply stops. The horizontal axis still runs to 2025, and the console warns about two removed rows.

Compare the two charts. The first announces its gap – you can see the break. The second looks like a complete series that happens to end in 2023, and nothing on it says whether Goldstar stopped being grown in zone 16, was grown and not reported, or was folded into another category. The warning is the same sentence in both cases, so it does not distinguish them either.

Excel behaves the same way and does not warn at all.

NoteThe same thing in Excel

Select Data > Hidden and Empty Cells offers three choices for a gap: leave it, treat it as zero, or bridge it with a straight line. Bridging is the default in some chart types and it is a claim about data you do not have. Zero is worse – it draws a real line down to the floor and back, which looks like a crop failure.

Step 7: A line chart in Excel, from a PivotTable

Charting 14,700 rows directly does not work in either tool. In Excel the summarising step is a PivotTable.

Open sask_variety_yields.csv in Excel and save it into your workbook as a new sheet. Select any cell in the data and choose Insert > PivotTable.

Set it up:

  • Rows: Year
  • Columns: Crop
  • Values: Yield, set to Average (click the field, Value Field Settings > Average)
  • Filters: Risk_Zone, set to 1

Check: a five-row table, 2021 to 2025, with a column per crop and average yields in the cells.

Select the PivotTable and choose Insert > Line Chart > Line with Markers. This gives a PivotChart, which stays tied to the PivotTable – change the filter and the chart redraws.

Twenty-seven crops is far too many lines. In the Crop field list, untick everything except Barley, Wheat - Hard Red Spring and Canola/Rapeseed.

Check: three lines, five points each, and a legend naming the three crops.

Practice: change the Risk_Zone filter from 1 to 8 and watch the chart redraw. That link between filter and chart is the thing PivotCharts give you that a plain chart does not.

Step 8: Small multiples, which Excel cannot do

Three lines on one chart is fine. Twelve is spaghetti. The alternative is one small panel per group:

variety_yields |>
  filter(
    crop %in% c("Barley", "Wheat - Hard Red Spring", "Canola/Rapeseed"),
    risk_zone %in% c(1, 8, 14)
  ) |>
  group_by(crop, risk_zone, year) |>
  summarise(mean_yield = mean(yield, na.rm = TRUE), .groups = "drop") |>
  ggplot(aes(x = year, y = mean_yield)) +
  geom_line() +
  geom_point() +
  facet_grid(crop ~ risk_zone, scales = "free_y") +
  labs(
    title = "Average yield by crop and risk zone",
    x = NULL,
    y = "Yield (bu/ac)"
  ) +
  theme_classic()

Check: a three-by-three grid of panels, crops down the side and risk zones across the top.

facet_grid() is one line and there is no Excel equivalent – you would build nine charts and line them up by hand.

Note scales = "free_y". Each row gets its own vertical scale, because canola yields around 40 bu/ac and barley around 80, and a shared axis would flatten the canola panels. The cost is that you can no longer compare panel heights across rows, only shapes within a row. Take it out and see which version answers the question you have.

Check: with scales = "free_y" removed, the canola row is squashed into the bottom of its panels.

Practice

Swap facet_grid(crop ~ risk_zone) for facet_wrap(~ crop) and drop the risk-zone filter, so all 23 zones go into each crop panel. What happens, and why is it not useful?

You get three panels, each holding 23 overlapping lines, because nothing tells ggplot to separate the zones – it draws one line per panel through all the points. geom_line(aes(group = risk_zone)) separates them, and then you have three panels of spaghetti instead of one.

This is the point at which a chart stops being the right answer. Twenty-three zones over five years is a table, or a map, or a summary of the range. General principles opens with deciding whether a chart helps at all.

Step 9: Read a chart critically

Open the chart you saved in Step 4 and go through it as a reader rather than its author:

  1. Can you tell what it is about without being told? The title should do that.
  2. Is every axis labelled, with units?
  3. Does the axis start at zero, and should it?
  4. Is anything on it that carries no information – a legend for one series, gridlines nobody reads, a border?
  5. Would a sentence be better? Two numbers usually are.

Check: you found at least one thing to change. Change it, re-run, re-save.

The last question is the one people skip. 10  Graphing best practices opens with a chart of two values that should have been a sentence, and that mistake is more common than any of the formatting ones.

Practice on your own

The test bank for this module is here, and it is the best use of the remaining time.

Two other things if you want a break from the bank.

Rebuild the Step 8 faceted chart in Excel and time yourself. Nine PivotCharts, matched axes, laid out in a grid. It is worth doing once – partly because you will sometimes have to, and partly because it tells you which tool to reach for when someone asks for twelve versions of the same chart.

Then take the barley bar chart and make it as bad as you can without changing any numbers: truncate the axis, add a 3-D effect (Chart Design > Change Chart Type > 3-D Bar), put the varieties in random order, delete the axis title, add a shadow. Then write down what each change did to the reader. Every one of them is a default in some version of Excel, and the 3-D bar is the one that makes the front bars look longer than they are.

Before you leave

Save both files. You should have an R script that reads two datasets and builds four charts, one of them saved to output/, and a workbook with a bar chart, a PivotTable and a PivotChart.

Step 9 is the one to keep doing. Run through those five questions on every chart before you hand it to anyone.