2 Clean and Summarize Data in Excel

Data for this chapter

This chapter uses the Ontario forest statistics workbook (on_forest_statistics_1.xlsx). Click the file name to download it directly, then save it in your data/ folder. For the source and details, see the Datasets Used in This Book page.

Chapter goals

By the end of this chapter you will be able to:

Sort and filter large tables to surface unusual records.
Detect duplicate rows on a single column and on a composite key.
Use structured tables (Ctrl+T) to keep formulas connected to the data as it grows.
Compute single-cell summary statistics — SUM, AVERAGE, MEDIAN, MIN, MAX, COUNT, COUNTA, STDEV.
Use relative, absolute, and mixed cell references safely so a copied formula does what you expect.
Apply conditional aggregation with SUMIF, COUNTIF, and AVERAGEIF (and their plural counterparts).
Use conditional logic with IF, nested IF, and IFS to recode values into categories.
Look up values from a reference table with VLOOKUP and XLOOKUP.
Maintain a cleaning log that documents every change you made to the data.

Expected output for this chapter

What you will hand in

The raw workbook on_forest_statistics_1.xlsx placed in your chapter02/data/ folder, unchanged from the Ontario Open Data download.
A working copy you may modify, saved as on_forest_statistics_1_clean.xlsx in your chapter02/outputs/ folder, containing:
- the dataset converted to a structured table with a meaningful name,
- a one-page cleaning log sheet,
- a size_class column built with IFS, and
- a small forest-type lookup sheet wired to the Data sheet with XLOOKUP.
A short group-lab worksheet (or screenshot) submitted to the course site.

This list is the same as the rubric for the chapter assignment. Keep it in view while you work.

2.1 Why a cleaning log matters

Real data arrives messy. Duplicate stems. Inconsistent species labels. Missing measurements. Unit ambiguity. The discipline introduced in this chapter — document every change, never edit data silently — is the foundation of every later chapter.

A cleaning log is a separate sheet (or document) that records every change you made between the raw file and your analysis-ready version:

Date	Step	What I changed	Why
2026-09-15	1	Converted Data sheet into a structured table named `ontario_data`	Self-documenting formulas; auto-extends.
2026-09-15	2	Added a `size_class` column using `IFS`	Group small / medium / large areas for later summaries.
2026-09-15	3	Built a `codes` sheet mapping forest-type codes to plain-English names	Future joins / lookups need a clean reference.
2026-09-15	4	Verified the composite key has no duplicates	Confirms the file is a true cross-classification.

Keep the cleaning log next to your data forever. When someone asks “why is your number different from the official report?” — the answer is in the cleaning log.

The two cardinal sins of data cleaning

Editing raw data in place. Once you change a cell, the original is gone. Always work on a copy of the raw file.
Cleaning without a log. Without documentation, your numbers become impossible to defend.

A cleaning log is your audit trail. It protects you when a manager, reviewer, or collaborator questions your numbers months later.

2.2 The dataset for this chapter

This chapter uses one openly licensed Excel workbook from the Government of Ontario Open Data Catalogue, alongside the BC silviculture file you met in Chapter 1.

Property	Value
File name	`on_forest_statistics_1.xlsx`
Source	Government of Ontario — Forest Resources of Ontario 2021
Catalogue page	https://data.ontario.ca/dataset/forest-resources-of-ontario-2021
Licence	Open Government Licence — Ontario
Rows in this teaching extract	179
Columns	8
Coverage	Region 3E only (teaching subset of the multi-region file)
Time period	Snapshot for 2021

Where the data comes from — and what to do with it

This dataset is published by the Government of Ontario and distributed through the Ontario Open Data Catalogue. Two rules (same as Chapter 1):

Keep the raw downloaded file unchanged. Save the file as on_forest_statistics_1.xlsx exactly as you downloaded it. Do not sort it, edit it, or save over it.
Save any cleaned or modified version separately as on_forest_statistics_1_clean.xlsx in your outputs/ folder.

The teaching extract in this book is Region 3E only. If you need the full multi-region file (all landscape guide regions), download it directly from the catalogue URL above.

2.2.1 Why this dataset matters for forestry

Where the BC silviculture file (Chapter 1) was a time series of provincial totals, this Ontario file is a cross-classification — every row is one combination of region × ownership × seral stage × forest type × age class, with the total area for that combination.

This shape — how the rows and columns are laid out (here, one row per category combination) — is what a working forester sees most often:

The Government of Ontario uses cross-classified tables like this to track stand structure across the landbase.
BC’s Vegetation Resources Inventory (VRI) and the National Forest Inventory both produce summary tables with the same shape.
Any FRPA, FMP, or sustainable-forest-management plan eventually rolls up to a table like this.

Learning to navigate, summarize, and recode a cross-classified file is one of the most transferable skills in forestry data work.

2.2.2 The columns

Column	Type	What it represents
`landscape_guide_region`	text	Ontario landscape guide region (here: 3E only)
`own_group`	text	Ownership group (here: CRN = Crown only)
`LGDS`	text	Seral stage code (P / S / I / M / L)
`SERAL_NAME`	text	Human-readable seral stage name
`PFT`	text	Provincial forest type code (3-letter)
`PFTName`	text	Human-readable forest type name
`AC_10`	number (year)	Age-class midpoint, in 10-year bins
`SumOfTotalHa`	number	Total area in hectares for that combination

A note for Mac users

The Excel chapters are written for Windows, but almost everything is identical on Mac with two routine substitutions: press Cmd wherever the text says Ctrl (e.g. Cmd+T, Cmd+Click), and a few ribbon tabs are named slightly differently (for example Table Design on Windows is the Table tab on Mac). Where a Mac step differs in a way that is easy to miss, a Mac: note is called out inline.

2.3 Sorting

Click any cell inside the data, then choose Data → Sort.

A dialog opens. Choose:

Column: SumOfTotalHa
Sort On: Values
Order: Largest to Smallest

Click OK. The largest cell rises to the top — a mature Conifer Lowland area of over 300,000 ha. Look at the context: MCL, Mature, an age class around 135. Does it make sense for a Region 3E mature lowland that has been growing for over a century? Yes — old conifer lowland covers much of northern Ontario.

2.3.1 Multi-key sort

Sort by PFTName (A→Z), then by AC_10 (smallest to largest), then by SumOfTotalHa (largest to smallest). Use Data → Sort → Add Level to add each additional sort column. Mac: the Sort dialog has no Add Level button — click the + button below the list of sort fields to add a level (and – to remove one).

Now the table reads like a story: for each forest type, you see every age class in order, and within each age class the largest area is on top. This is the human equivalent of group_by() |> arrange() in R, which you will meet in Chapter 7.

Sorting changes the file

Unlike filtering, sorting rearranges the underlying rows. To recover the original order:

Add an original_row_number column before sorting (formula: =ROW() in the first row, dragged down), or
Convert your data into a structured table first (next section) — sorts on a table do not destroy original order if you have an original_row_number column.

2.4 Filtering

AI prompt to check your work

Use this after your own attempt. It should check your reasoning, not hand you the answer:

“In Excel I filtered the data to show only [describe your criteria] and got [N] visible rows. Check whether my criteria actually capture what I intended, whether I might be hiding rows I meant to keep, and how to confirm the visible count is right. Do not give me the final filter.”

Two ways: AutoFilter and Slicers.

2.4.1 AutoFilter

Click any cell in your data, then Data → Filter. A small dropdown arrow appears on each column header.

Click the arrow on the PFTName header, uncheck Select All, and check only “Jack Pine”. The view collapses to roughly two dozen rows. The data is unchanged — you have only hidden rows, not deleted them.

To re-show everything, click the arrow again and choose Clear Filter From.

2.4.2 Multiple filter criteria

Click the arrow on SumOfTotalHa. Choose Number Filters → Greater Than. Type 50000. The view filters to rows where the area exceeds 50,000 hectares. Mac: there is no Number Filters submenu — open the column arrow, use the “Choose One” dropdown, pick Greater Than, and enter 50000.

You can stack column filters: first filter PFTName = "Jack Pine", then filter SumOfTotalHa > 5000. Excel shows only the rows matching both conditions — equivalent to filter(PFTName == "Jack Pine" & SumOfTotalHa > 5000) in R.

2.4.3 Slicers (with structured tables)

Slicers are visual filter panels. They only work on structured tables (next section). Click any cell in a structured table, then Table Design → Insert Slicer. Check which fields you want.

Slicers are clickable buttons sitting beside the table. They are visual, fun, and dangerous: a slicer can be on without the viewer noticing. The number changes but the title does not. Always check the slicer state before trusting a screenshot.

2.5 Structured tables (`Ctrl+T`)

Excel’s most undersold feature.

Click any cell in your data. Press Ctrl+T (Cmd+T on Mac). Confirm “My table has headers”. Click OK.

What changed:

The data became a structured table with a name (default Table1, which you should rename in the Table Design ribbon — Mac: the Table tab — try ontario_data).
Headers got a coloured band.
AutoFilter is automatically on.
Formulas that reference table columns use column names instead of cell ranges:

=AVERAGE(ontario_data[SumOfTotalHa])

Compare to the un-tabled version:

=AVERAGE(H2:H180)

The structured version is self-documenting and self-resizing. If you add a row at the bottom, the formula automatically includes it.

Always start with Ctrl+T

When you open any new Excel file, the first thing to do is convert each sheet’s data into a structured table. The benefits compound: slicers work, formulas are readable, growth is automatic, and references are stable.

2.5.1 Renaming the table

Click anywhere in the table. Look at the Table Design ribbon (Mac: the Table tab). In the leftmost field labelled Table Name — on Mac, the Table Name box at the left of that tab — type a meaningful name:

✅ ontario_data
✅ bc_silviculture
❌ Table1, Sheet1!_FilterDatabase

Use snake_case (lowercase with underscores). No spaces, no special characters. This matches the R convention you will meet in Chapter 4.

2.6 Detecting duplicates

A real summary table should have one row per combination of the grouping columns. If two rows have the same combination, something went wrong upstream.

2.6.1 On a single column

Select the column you want to check (e.g., PFTName). Choose Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values. Duplicate values get highlighted.

For the Ontario file, every PFTName appears many times (because the file has many seral × age combinations per forest type). That is expected — not a bug. Duplicates on a single column only signal a problem when the column should be a unique key.

2.6.2 On a composite key

In a cross-classification like this file, the natural unique key is (landscape_guide_region, own_group, LGDS, PFTName, AC_10). A composite key. To detect duplicates on it:

Add a helper column composite_key next to the data:

=A2 & "|" & B2 & "|" & C2 & "|" & F2 & "|" & G2

The | separator prevents false collisions between, say, "AB" + "C" and "A" + "BC". If any concatenated key appears twice, the cell highlights when you apply Conditional Formatting → Highlight Cells Rules → Duplicate Values to the helper column.

Verification in R: 0 duplicate key combinations in the Ontario file.

For this file, the answer is zero. Good — the upstream pipeline is clean.

2.6.3 Removing duplicates

If you do find duplicates and want to remove them, Data → Remove Duplicates. A dialog asks which columns to use as the key. Always work on a copy. Document the removal in your cleaning log with row counts before and after.

2.7 Summary formulas — the essentials

The seven most-used aggregating functions:

Formula	What it does	Example
`=SUM(range)`	Adds every numeric value	`=SUM(H2:H180)`
`=AVERAGE(range)`	Arithmetic mean (sum ÷ count)	`=AVERAGE(H2:H180)`
`=MEDIAN(range)`	Middle value when sorted; robust to outliers	`=MEDIAN(H2:H180)`
`=MIN(range)`	Smallest numeric value	`=MIN(H2:H180)`
`=MAX(range)`	Largest numeric value	`=MAX(H2:H180)`
`=COUNT(range)`	Number of numeric values	`=COUNT(H2:H180)`
`=COUNTA(range)`	Number of non-empty values (text + numeric)	`=COUNTA(A2:A180)`
`=STDEV(range)`	Standard deviation (sample)	`=STDEV(H2:H180)`

The distinction between COUNT and COUNTA matters: COUNT skips text cells. If your column should have 179 numeric entries and COUNT says 177, two cells contain text or are blank.

2.7.1 Worked examples on the Ontario file

Statistic	Value
Total area covered (sum of SumOfTotalHa)	9.00 million ha
Mean area per combination	50,257 ha
Median area per combination	12,676 ha
Maximum combination	320,678 ha
Number of rows	179

Look at the gap between mean and median. The mean is much larger — a few very large rows pull the average up. The median is closer to the typical combination. Both numbers are correct; they answer different questions.

2.8 Conditional aggregation: SUMIF, COUNTIF, AVERAGEIF

What if you want the sum, count, or average only for rows that match a condition?

2.8.1 `COUNTIF(range, criterion)`

How many rows describe Jack Pine?

=COUNTIF(F2:F180, "Jack Pine")

The criterion can be a number, text, a comparison, or a wildcard:

Criterion	Matches
`50000`	Exact 50000
`">50000"`	Greater than 50000
`"<>0"`	Anything not zero
`"Jack*"`	Text starting with “Jack”
`"pine"`	Text containing “pine” (case-insensitive)

2.8.2 `SUMIF(criterion_range, criterion, sum_range)`

What is the total area of “Mature” forest in Region 3E?

=SUMIF(D2:D180, "Mature", H2:H180)

Read it: “sum the SumOfTotalHa values for rows where SERAL_NAME equals ‘Mature’”.

2.8.3 `AVERAGEIF(criterion_range, criterion, average_range)`

What is the average area for “Jack Pine” combinations?

=AVERAGEIF(F2:F180, "Jack Pine", H2:H180)

2.8.4 `SUMIFS`, `COUNTIFS`, `AVERAGEIFS` — multiple criteria

The plural versions allow multiple criteria. The argument order is different — the sum range comes first, then criterion pairs:

=SUMIFS(H2:H180, F2:F180, "Jack Pine", D2:D180, "Mature")

Read it: “sum the SumOfTotalHa values for rows where PFTName is ‘Jack Pine’ AND SERAL_NAME is ‘Mature’”.

When to use these vs a PivotTable

For a one-off number, SUMIF is fastest. For a whole table of numbers (cross-classification across multiple categories), a PivotTable is the right tool. We will meet PivotTables in Chapter 3.

2.9 Safe cell references: the dollar sign

Type =B2 + C2 in cell D2. Copy down. D3 becomes =B3 + C3. D4 becomes =B4 + C4. The references moved with the formula. That is a relative reference.

Type =B2 + $C$2 in cell D2. Copy down. D3 becomes =B3 + $C$2. D4 becomes =B4 + $C$2. The first part moves; the second part stays put. The dollar signs locked it. That is an absolute reference.

You can mix:

$A1 — column locked, row moves.
A$1 — row locked, column moves.
$A$1 — both locked.

2.9.1 A worked bug

A common bug: you want to divide every value by the total. You write =H2 / H180 in cell I2, where H180 is the grand total. Copy down. I3 becomes =H3 / H181. But H181 is empty. Every percentage below the first is wrong.

The fix is =H2 / $H$180. The denominator stays put.

A keyboard shortcut worth memorizing

While editing a cell reference, press F4 to cycle through:

$A$1 → A$1 → $A1 → A1 → $A$1 → …

This is the fastest way to add or remove dollar signs.

2.10 Conditional logic: IF, nested IF, IFS

2.10.1 `IF(condition, value_if_true, value_if_false)`

Basic form. Returns one of two values based on a logical test.

=IF(H2 > 1000, "large", "small")

2.10.2 Nested `IF` — multiple categories

Add a size_class column on the Ontario file based on SumOfTotalHa:

=IF(H2 < 1000, "small",
    IF(H2 < 50000, "medium", "large"))

This nested IF evaluates left to right. If the first condition is true, return “small”. Otherwise check the second. Otherwise return “large”.

2.10.3 `IFS` — cleaner for many conditions

Available in Excel 2019+:

=IFS(H2 < 1000, "small",
     H2 < 50000, "medium",
     TRUE, "large")

IFS evaluates each pair left to right. The first matching condition returns its value. The trailing TRUE is a catch-all for “everything else”. Without it, unmatched rows return #N/A.

2.10.4 `SWITCH` — exact-match recoding

When you are recoding based on one cell’s exact value (not a threshold test), SWITCH is often clearer than a nested IF:

=SWITCH(D2, "Mature", "old", "Young", "new", "other")

SWITCH compares D2 to each value in turn and returns the label after the first match; the final lone argument is the catch-all default. Rule of thumb: use IFS for range/threshold tests and SWITCH for exact-match recoding.

size_class	n
large	60
medium	69
small	50

2.10.5 `AND`, `OR`, `NOT` — combining conditions

=IF(AND(H2 > 1000, F2 = "Jack Pine"), "flag", "")

Use AND when all conditions must be true. Use OR when any condition can be true. Use NOT to reverse a condition.

2.11 Lookups: VLOOKUP and XLOOKUP

Suppose you have a small reference sheet mapping forest-type codes to plain-English names:

Code	Name
PJK	Jack Pine
MCU	Conifer Upland
MCL	Conifer Lowland
MIX	Mixedwood
POP	Poplar
BWT	White Birch
PWR	Red and White Pine
TOL	Tolerant Hardwoods

Place this on a new sheet called codes. Your data uses the code in column E. In column I you want the plain-English name.

2.11.1 `XLOOKUP` (Excel 2019+, recommended)

=XLOOKUP(E2, codes!A:A, codes!B:B, "unknown")

Arguments: value to find, where to look, what to return, default if not found. Clear and modern.

The sheet name in the formula must match

codes!A:A refers to a sheet named codes. If your lookup sheet has a different name — or you paste the formula before renaming the sheet — Excel returns #REF! or #N/A. Rename the reference sheet first, or fix the sheet name in the formula.

2.11.2 `VLOOKUP` (everywhere)

=VLOOKUP(E2, codes!A:B, 2, FALSE)

Arguments: value to find, table range starting with the lookup column, column number to return, exact match. The FALSE at the end forces exact match — always include it. The default of TRUE (approximate match) silently returns the wrong value if the lookup table is unsorted.

When lookups go wrong

Missing key — #N/A. Either the value is missing, or the key is misspelled. Wrap with IFERROR to flag rather than crash: =IFERROR(VLOOKUP(...), "missing").
Duplicate key — silently returns the first match. Always check that your lookup table has each key exactly once with =COUNTIF(codes!A:A, "PJK") (should return 1).
Wrong type — looking up "123" (text) against 123 (number) fails. Inspect both sides.
Trailing spaces — "PJK " does not match "PJK". Use =TRIM(...) to clean strings first.

2.12 Putting it together — a worked cleaning sequence

You receive a colleague’s spreadsheet. Here is a complete cleaning sequence:

Step	Action	Excel feature
1	Make a working copy of the raw file	File → Save As (with `_clean` suffix)
2	Open the working copy	—
3	Convert data to structured table	Ctrl+T, rename in Table Design
4	Apply `TRIM` to text columns to remove stray spaces	Helper column with `=TRIM(A2)`
5	Check for duplicates on the natural key	Conditional Formatting on composite key
6	Investigate missing values column by column	`=COUNTBLANK(...)` per column
7	Apply `IFS` to recode messy categories	New column with recoded values
8	Look up reference values where needed	`XLOOKUP` against reference sheet
9	Compute summary statistics	`SUMIF`, `COUNTIF`, `AVERAGEIF`
10	Write the cleaning log	New sheet, document every change

Each step is reversible because we are working on a copy and never modifying the raw values directly. Every step is documented in the log. This is what reproducible Excel looks like.

2.13 Active learning

These short activities are designed to be done alongside the chapter.

2.13.1 Activity 1 — Find duplicates in the Ontario file

Apply the duplicate-detection technique to the Ontario file. Use the natural composite key. Report how many duplicates you found.

Verification: 0 duplicates on the natural key (should be zero).

2.13.2 Activity 2 — Mature area by forest type

Using SUMIF (or SUMIFS), compute the total area of mature forest in Region 3E for each forest type.

PFTName	mature_ha
Conifer Lowland	903,009
Conifer Upland	745,944
Mixedwood	471,449
White Birch	242,519
Poplar	219,451
Jack Pine	92,894
Red and White Pine	4,026
Tolerant Hardwoods	1,142

2.13.3 Activity 3 — Classify and count

On the Ontario file, add the size_class column from the conditional-logic section. Then count rows in each class. Compare with your neighbour.

2.13.4 Activity 4 — Multi-criteria SUMIFS

Compute the total area where PFTName = "Jack Pine" AND SERAL_NAME = "Mature" AND AC_10 >= 95 (older mature jack pine).

Verification: 33,991 ha of older mature jack pine.

2.13.5 Activity 5 — Build a lookup

Build a reference sheet codes with the 8 forest-type codes (PJK, MCU, MCL, etc.) and their plain-English names. Then use XLOOKUP (or VLOOKUP) to retrieve the name from the PFT column of the Data sheet. Compare your retrieved column to the existing PFTName column.

2.14 Practice Demo Lab

Practice demo only

This is a guided practice lab in the OER book, designed to help you learn the workflow before completing the official lab assignment on Canvas. The Canvas lab is the graded assignment. Use this activity to practice, compare your work with the reference solution, and learn how to write an AI verification prompt.

This demo lab has six parts — work them in order:

Your attempt. Work through the tasks below.
Reference solution. Compare against the worked examples earlier in this chapter (your public reference). The graded Canvas lab has its own private answer key — do not copy demo solutions into a Canvas submission.
Compare your work with the reference. Where did it match? Where did it differ? What caused the difference, and how did you fix it?
Write your own AI verification prompt. Ask an AI to check your reasoning, code, and outputs — not to produce the answer for you.
Model AI verification prompt.

“I am doing a FRST 232 practice demo lab. My dataset is [file name] (columns [columns]). I attempted [paste your steps or code] and got [paste the output]. Explain what my work does line by line, tell me whether it answers the task, and list the checks I can run to verify it against the chapter’s worked example. Do not give me the final answer.”
Reflection: what changed after checking? In two or three sentences — did your attempt hold up? what did you fix? what will you check first next time?

Lab: Clean and summarize the Ontario forest file together

Work in groups of 3 or 4. Use one shared copy of on_forest_statistics_1.xlsx.

Task 1 — Save a working copy. Save the workbook as on_forest_statistics_1_clean.xlsx in your outputs/ folder. The raw file in data/ should remain unchanged.

Task 2 — Make it a structured table. Convert the Data sheet into a structured table. Rename it to ontario_data. Confirm that autofilter dropdowns appear in the header row.

Task 3 — Add a size_class column. Use IFS (or nested IF) to classify each row’s SumOfTotalHa into small / medium / large using the thresholds 1,000 and 50,000. Then use COUNTIF to count how many rows fall in each class. Compare across groups.

Task 4 — One conditional aggregation. As a group, choose one of these:

Total area of Jack Pine in Region 3E (use SUMIF)
Average area for Mature combinations (use AVERAGEIF)
Count of rows where SumOfTotalHa > 100,000 (use COUNTIF)

Write the formula. Confirm the result with a spot check.

Task 5 — Submit. Submit either a short worksheet (a one-page document with your group’s answers to Tasks 1–4) or a screenshot of the cleaned workbook showing the structured table and the new size_class column. Include all group members’ names on the submission.

This lab is designed to fit comfortably inside a single hands-on session.

Reference solution — download

Open the Chapter 2 reference solution (HTML answer key) — the completed, task-by-task answer key — or download the completed Excel workbook with the formulas worked into the sheets (it recalculates when you open it in Excel). Open it after your own attempt and use it to check your work.

This is the public practice solution. The graded lab on Canvas has its own private answer key — do not copy this into a Canvas submission.

2.15 Exercises

These are the take-home exercises that go with this chapter.

Open on_forest_statistics_1.xlsx. Filter to LGDS = "M" (Mature). What is the total area, in hectares, of mature forest in Region 3E? Use SUMIF.
Open bc_disturbance_reforestation.xlsx (from Chapter 1). Add a Decade column using =FLOOR(A2, 10). Then use AVERAGEIF to compute the mean Harvested_ha per decade.
Build a codes lookup sheet on the Ontario file with the eight forest-type codes and their plain-English names. Add a PFTName_check column that uses XLOOKUP to retrieve the name from the code. Use IF to flag any mismatches between the looked-up name and the existing PFTName column.
Compute the coefficient of variation (standard deviation divided by mean) of SumOfTotalHa on the Ontario file. Why might this be useful alongside the mean?
Apply conditional formatting to the SumOfTotalHa column with a three-colour scale: green for small, yellow for medium, red for large. Take a screenshot and submit.
Write a one-page cleaning log for the changes you made to the working copy of the Ontario file. Use the format shown at the top of this chapter. Submit the log next to the workbook.
Optional, harder. Build a small summary table on a new sheet of the Ontario file using only SUMIFS and COUNTIFS (no PivotTable yet — that’s Chapter 3). Rows = forest type, columns = seral stage, values = sum of SumOfTotalHa and count of combinations.

2.16 Optional, advanced

Items in this section are not required for the main path.

2.16.1 Custom number formats

Beyond standard formats, Excel supports custom format codes. For example, the custom format #,##0 "ha" displays 216,305 ha. The "ha" is part of the format, not the value — formulas still treat the cell as a plain number.

2.16.2 `INDEX` / `MATCH` — the classic lookup combo

VLOOKUP and XLOOKUP cover most cases. For old-Excel compatibility or for left-of-lookup-column returns (which VLOOKUP cannot do), use INDEX / MATCH:

=INDEX(codes!B:B, MATCH(E2, codes!A:A, 0))

MATCH(E2, codes!A:A, 0) returns the row number of the match. INDEX(codes!B:B, n) returns the value in column B at that row. Combining them gives you a fully flexible lookup.

2.17 A glimpse ahead: From Excel to R

In Chapter 7 (Summarize forestry data overall and by group) you will do exactly what this chapter does — but in R. The Excel moves you learned here map directly to R commands:

What you did in Excel (Ch 2)	What you will do in R (Ch 6–7)
`=SUMIF(D2:D180, "Mature", H2:H180)`	`ontario \\|> filter(SERAL_NAME == "Mature") \\|> summarise(sum(SumOfTotalHa))`
`=COUNTIF(F2:F180, "Jack Pine")`	`ontario \\|> filter(PFTName == "Jack Pine") \\|> nrow()`
`=SUMIFS(H, F, "Jack Pine", D, "Mature")`	`ontario \\|> filter(PFTName == "Jack Pine", SERAL_NAME == "Mature") \\|> summarise(sum(SumOfTotalHa))`
`=IFS(H2<1000, "small", H2<50000, "medium", TRUE, "large")`	`mutate(size_class = case_when(SumOfTotalHa < 1000 ~ "small", SumOfTotalHa < 50000 ~ "medium", TRUE ~ "large"))`
`=VLOOKUP(E2, codes!A:B, 2, FALSE)`	`ontario \\|> left_join(codes, by = "PFT")`
Structured table named `ontario_data`	`ontario` (the tibble)
Cleaning log on a separate sheet	A Markdown chunk in the same `.qmd` file

The intent is identical. The R version is scriptable and shareable; the Excel version is visible and forgiving. By the time you reach Chapter 7, this whole chapter’s logic will be familiar — you will just be writing it in a different language.

2.18 Self-assessment quiz

Click the answer you think is correct. The right answer turns green; wrong answers turn red and the correct one is revealed. Explanations appear below each question.

1. In the formula =B2 / $C$38, what does the dollar sign do?

Marks the cell as currency
Locks the reference so it does not move when the formula is copied
Indicates an error
Nothing — Excel ignores it

$C$38 stays as $C$38 when copied. Without the dollar signs, the reference shifts relative to the formula's new position.

2. Which function returns the number of non-empty cells in a range, including text cells?

COUNT
COUNTA
COUNTIF
LEN

COUNTA counts every non-empty cell (text + numeric + dates). COUNT only counts numeric cells.

3. To check that a lookup key appears exactly once in a reference table, which formula is best?

=VLOOKUP(A2, codes!A:B, 2, FALSE)
=COUNTIF(codes!A:A, A2) — should return 1
=IF(A2 = codes!A2, "OK", "not OK")
=ISNUMBER(VLOOKUP(...))

If the result is 1, the lookup is safe. If it is 2 or more, your VLOOKUP would silently return the first match.

4. A cleaning log should record:

Only the final cleaned data
Every change you made between raw and clean, with a reason
Only changes you cannot reverse
The data dictionary

A cleaning log captures every change with a reason. This is what lets you defend your numbers months later.

5. Which function is best for recoding the value in a cell to one of several exact-match labels (e.g., "P" → "Pre-sapling", "S" → "Sapling")?

VLOOKUP
A long chain of nested IF
IFS (or SWITCH)
SUMIF

IFS is designed for exact-match recoding. Cleaner and more readable than a long chain of nested IF.

6. You apply AutoFilter to hide rows where SumOfTotalHa < 1000. The other rows are not deleted — they are just hidden. True or false?

True — AutoFilter hides; it does not delete
False — AutoFilter deletes the hidden rows

AutoFilter is reversible. Sorting, by contrast, rearranges the rows permanently.

7. The formula =SUMIFS(H2:H180, F2:F180, "Jack Pine", D2:D180, "Mature") does what?

Sums every numeric value in column H
Sums the H values for rows where column F is "Jack Pine" AND column D is "Mature"
Returns "Jack Pine" if column D is "Mature"
Counts the rows that match both conditions

SUMIFS sums the first range, subject to one or more criterion ranges.

8. What is the main advantage of converting your data into a structured table (Ctrl+T) before writing formulas?

It makes Excel faster
Formulas use column names instead of cell ranges, and they auto-extend when rows are added
It encrypts the data
It is required for sorting

Structured tables make formulas self-documenting (=AVERAGE(tbl[col]) instead of =AVERAGE(H2:H180)) and self-extending.

9. Where does the Ontario dataset come from?

A private consulting firm
The Government of Ontario, distributed through the Ontario Open Data Catalogue
A textbook publisher
A research paper

The file is from the Ministry of Natural Resources and Forestry, published under the Open Government Licence — Ontario.

10. The Ontario file in this chapter is a cross-classification. That means —

It has only one column of data
Each row is one unique combination of categorical variables, with one numeric value
The data is encrypted
It is sorted by region

Every row is one unique combination of region × ownership × seral stage × forest type × age class, with the total area for that combination.

11. What is the natural composite key for the Ontario file?

PFTName alone
SumOfTotalHa alone
landscape_guide_region, own_group, LGDS, PFTName, AC_10
There is no key

The combination of region, ownership, seral stage, forest type, and age class uniquely identifies each row. If any combination repeats, something went wrong upstream.

12. Press F4 while editing a cell reference. What does it do?

Closes the file
Cycles the reference through absolute / mixed / relative forms ($A$1 → A$1 → $A1 → A1)
Inserts a function
Triggers AutoFilter

F4 is the fastest way to add or remove dollar signs.

13. After cleaning the Ontario file, what should the file be named and where should it live?

Overwrite on_forest_statistics_1.xlsx in data/
Save as on_forest_statistics_1_clean.xlsx in outputs/
Email it to yourself
Delete the original

The raw file should stay unchanged in data/. The cleaned version goes in outputs/ with a clear suffix.

14. =AVERAGEIF(D2:D180, "Mature", H2:H180) returns —

The total area of mature forest
The mean SumOfTotalHa for rows where SERAL_NAME = "Mature"
The number of mature rows
The largest mature area

AVERAGEIF averages the third range, subject to the criterion. Use SUMIF for total, COUNTIF for count.

15. Which is true about =VLOOKUP(A2, codes!A:B, 2, TRUE)?

It is the safest form of VLOOKUP
The TRUE means approximate match — dangerous if the lookup table is unsorted; can silently return the wrong value
It returns an error if the key is missing
It looks up the value in column B

Always use FALSE (exact match) for VLOOKUP unless you really mean approximate. The default of TRUE is dangerous.

16. The mean of SumOfTotalHa in the Ontario file is much larger than the median. What does this tell you?

The data is wrong
The distribution is right-skewed — a few very large rows pull the mean up
The data is sorted
Half the rows are missing

When mean > median by a lot, the distribution is right-skewed. The median is the more honest measure of a "typical" row in this case.

17. You convert your Ontario data to a structured table named ontario_data. Which formula references the SumOfTotalHa column correctly?

=AVERAGE(SumOfTotalHa)
=AVERAGE(ontario_data[SumOfTotalHa])
=AVERAGE("SumOfTotalHa")
=AVERAGE(@SumOfTotalHa)

Structured-table syntax uses table_name[column_name]. Self-documenting and self-extending.

18. Which of these does NOT cause a VLOOKUP to fail?

A trailing space in the lookup key
The key is numeric but the lookup column is text
The lookup table is on the same sheet as the data
The exact-match flag is missing or TRUE

VLOOKUP works regardless of which sheet the lookup table is on. Trailing spaces, type mismatches, and the approximate-match default are the real culprits.

19. Why is sorting a structured table different from sorting a plain range?

Structured tables cannot be sorted
Structured tables keep formulas connected to the right columns even after rows are rearranged
Sorting a structured table makes Excel slower
There is no difference

Structured-table column references stay valid through sorts and filters because they refer to column names, not cell ranges.

20. Reflection. Describe one formula you wrote this chapter that did not work the first time. What was wrong, and how did you find out?

Common answers: VLOOKUP returned #N/A because of a trailing space in the lookup key · SUMIFS gave 0 because the criterion was a number formatted as text · a relative reference shifted incorrectly when copied down because I forgot the dollar signs on the denominator · IFS returned #N/A on some rows because the final TRUE catch-all was missing.

2.19 AI as a debugging companion

Useful prompts for this chapter

“In Excel, my formula =VLOOKUP(A2, codes!A:B, 2, FALSE) returns #N/A for some rows. The values in column A look the same as in the codes sheet. What are the three most common reasons VLOOKUP fails when the values look right?”
“Explain step by step what this formula does: =IFS(C2 < 1000, "small", C2 < 50000, "medium", TRUE, "large")”
“I want to write a SUMIFS that adds up the SumOfTotalHa column whenever PFTName is one of {Jack Pine, Mixedwood, Poplar}. Can I do this in a single formula?”

2.19.1 Verifying AI’s answers

After AI tells you why your VLOOKUP fails, check:

Are there trailing spaces? =LEN(A2) and =LEN(codes!A2) should match.
Are both values the same type? =ISTEXT(A2) and =ISTEXT(codes!A2).
Is the case the same? VLOOKUP is case-insensitive in Excel, but worth eliminating.

AI’s answer is a starting point, not the final answer. Always verify against the data.

2.19.2 When not to use AI

Do not use AI to write the cleaning log for you. The log is a record of your decisions.
Do not use AI to invent lookup values that are not in the reference table. If a value is missing, document the gap.

2.20 Reading

Microsoft Excel official documentation on VLOOKUP.
Microsoft Excel official documentation on XLOOKUP.
Ontario open-data dataset page: https://data.ontario.ca/dataset/forest-resources-of-ontario-2021
Open Government Licence — Ontario: https://www.ontario.ca/page/open-government-licence-ontario

Instructor notes

Pacing and emphasis

This chapter is heavier than Chapter 1. Plan to spread it across multiple sessions.
The main path covers seven skills: sort, filter, structured tables, summary formulas, conditional aggregation, conditional logic, lookups. The cleaning log ties them together.
Custom number formats and INDEX/MATCH are moved to the Optional, advanced section. Touch on them only if your group is moving quickly or already comfortable.
The group lab is the central hands-on deliverable. Plan for one full session.

2.20.1 Expected output checklist (matches the top-of-chapter callout)

Raw on_forest_statistics_1.xlsx in chapter02/data/, unchanged.
Working on_forest_statistics_1_clean.xlsx in chapter02/outputs/ containing a structured table, a cleaning log sheet, a size_class column built with IFS, and a small codes lookup sheet wired with XLOOKUP.
Group-lab worksheet or screenshot.

2.20.2 Materials provided alongside this chapter

File	Purpose
`on_forest_statistics_1.xlsx`	The raw open-data workbook (About + ReadMe + Data). Place in `data/`.
`on_forest_statistics_1_learner.xlsx`	Learner-facing exercises workbook with blanks and formula prompts. Distribute through the course site.
`on_forest_statistics_1_solutions.xlsx`	Instructor solutions with completed formulas and worked answers. Do not distribute to learners.
`quiz_ch02.html`	Standalone interactive quiz. Embedded in the rendered chapter; can also be hosted separately.

2.20.3 Suggested facilitation guidance for the Practice Demo Lab

These are optional facilitation and feedback suggestions for instructors running this book demo — not a grading key. The percentages below are relative emphasis, not Canvas marks; the graded lab assignment is on Canvas.

Task	Weight	What to look for
Save working copy (Task 1)	10 %	Cleaned file in `outputs/`; raw file untouched.
Structured table (Task 2)	20 %	Table named meaningfully; autofilter present.
size_class column (Task 3)	30 %	`IFS` formula correct; counts in each class confirmed.
Conditional aggregation (Task 4)	30 %	Correct formula AND a spot check.
Submission (Task 5)	10 %	Worksheet or screenshot is legible; group members named.

2.20.4 Common stumbling points

Some learners forget the trailing TRUE in IFS and get #N/A on rows that don’t match any explicit condition.
Some try to write a structured-table formula by typing the column name without the table prefix. The right form is ontario_data[SumOfTotalHa], not SumOfTotalHa alone.
A few will edit the raw on_forest_statistics_1.xlsx in place. Remind them often that the raw file stays unchanged.
Excel-to-R bridge: The “A glimpse ahead” section at the end of this chapter previews how the same logic appears in R. Refer to it when learners ask why we are still in Excel — the bridge is concrete and forward-pointing.