1  Excel as a Data-Analysis Workbench

NoteData for this chapter

This chapter uses the BC silviculture workbook (bc_disturbance_reforestation.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:

  • Open a real BC Government Excel workbook and orient yourself to its contents on first read.
  • Read the About, ReadMe, and Data sheets in the right order, and explain why that order matters.
  • Identify each column’s type (text, number, date) and unit (hectares, year).
  • Use freeze panes and a few keyboard shortcuts to move through a multi-column file without losing context.
  • Apply basic number formats that distinguish display from value.
  • Write a few simple formulas (SUM, AVERAGE, MIN, MAX) and check whether the results make sense.
  • Set up a clean project folder structure that separates raw data from cleaned files.

Expected output for this chapter

At the end of this chapter, each learner (or group) will produce:

TipWhat you will hand in
  1. The raw workbook bc_disturbance_reforestation.xlsx placed in your data/ folder, unchanged from the BC Data Catalogue download.
  2. A working copy you may modify, saved as bc_disturbance_reforestation_clean.xlsx in your outputs/ folder, containing:
    • the original three sheets (About, ReadMe, Data) kept intact — you add to them, you do not create new sheets or delete the originals,
    • one short data dictionary entry in your own words for any column you choose, and
    • the five simple formulas from this chapter computed in any empty cell.
  3. 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.

1.1 Why we start with Excel

Excel has flaws. It hides decisions in invisible mouse clicks. It is hard to reproduce or recreate. It is hard to version-control. We will spend most of this book moving away from it.

We still start with Excel deliberately. The interface is forgiving. Mistakes are visible. There is no console to fight. And the fundamental discipline we want learners to leave the course with — inspect first, compute second, verify always — is easier to teach when the data is sitting on the screen in plain sight.

When we move to R in Chapter 4, we will use the same dataset we analyse in Excel here. The continuity is the point: R is not a fresh start; it is a more honest version of what you already do. (See A glimpse of Chapter 4 at the end of this chapter for a preview.)

1.1.1 What “data analysis” actually means

People say “data analysis” to mean different things. In this course it means a sequence of five repeatable steps:

  1. Inspect the file. Know what is in it before computing anything.
  2. Document the file. Write a data dictionary you can defend.
  3. Clean the file. Remove what is wrong, document what was removed.
  4. Summarize the file. Compute the numbers that answer your question.
  5. Communicate the result. Tell the story with a chart, a table, or a sentence.

Chapter 1 covers steps 1 and 2 — inspect and document. The rest of the book builds the other three steps on top of that foundation.

1.2 The dataset for this chapter

This chapter uses one real, openly licensed Excel workbook from the BC Government / BC Data Catalogue:

Property Value
File name bc_disturbance_reforestation.xlsx
Source Environmental Reporting BC — Trends in Silviculture in B.C.
Catalogue page https://catalogue.data.gov.bc.ca/dataset/indicator-summary-data-trends-in-silviculture-in-b-c-
Licence Open Government Licence — British Columbia
Rows of data 37
Columns 8
Time span 1987–2023 (fiscal years)
Spatial coverage Province of British Columbia
Unit Hectares (ha)

The file is directly downloadable from the BC Data Catalogue. You do not need an account. There is no shapefile bundle to manage. It is one Excel file with three sheets.

1.2.1 Why this dataset matters for forestry

This is not a textbook dataset. Every number was published by the BC Ministry of Forests under the Trends in Silviculture in B.C. program. It is the same indicator that the province uses to track whether reforestation is keeping pace with the area being harvested and lost to natural disturbance.

A working forester or natural-resources analyst in BC will encounter this dataset (or one with the same structure) within a few months of starting any monitoring or reporting role. Learning to read it now is real, applicable work.

The dataset captures the story of BC silviculture from 1987 onward:

  • The shift in harvest practice from straight clearcutting toward clearcutting-with-reserves through the 1990s — a real policy change visible in the columns.
  • BC’s record-breaking 2017 wildfire season, which dominates the natural-disturbance column.
  • Steady growth in reforestation that, by the 2020s, exceeds total disturbance in most years.
ImportantWhere the data comes from — and what to do with it

This dataset is published by the BC Government and distributed through the BC Data Catalogue. Two rules:

  1. Keep the raw downloaded file unchanged. Save the file as bc_disturbance_reforestation.xlsx exactly as you downloaded it. Do not sort it, edit it, or save over it. The raw file is your point of reference if anything later goes wrong.
  2. Save any cleaned or modified version separately. Use a different file name (typically bc_disturbance_reforestation_clean.xlsx) and a different folder. That way you can always go back to the original.

This is the single most important habit in this chapter.

TipWhy this dataset is right for this chapter
  • It is real. Every number was published by the BC Ministry of Forests. You can verify any value against the official indicator page.
  • It is small. 37 rows × 8 columns. You can scroll through every row and inspect every cell.
  • It has no missing values. Cleaning comes later in the book.
  • The columns mean something. Every column has a forestry meaning we can talk about.
  • It tells a story — see Why this dataset matters for forestry above.

1.2.2 What the dataset measures

Each row is one fiscal year in BC. Each column is one area-based forestry indicator — harvested area, naturally disturbed area, reforested area. Units are always hectares (ha).

A fiscal year in BC runs from April 1 to March 31 of the following calendar year. So “Fiscal Year 2023” means April 2023 through March 2024.

1.3 Project folder structure

Before you open the file, set up where it will live. The way you organize files now saves a hundred “where is that file?” moments later.

Create this folder on your computer (or on Posit Cloud):

frst232/
├── chapter01/
│   ├── data/                                Raw data files — never edit anything in here.
│   │   └── bc_disturbance_reforestation.xlsx
│   ├── outputs/                             Cleaned or modified files you produce.
│   │   └── bc_disturbance_reforestation_clean.xlsx   (you save this yourself)
│   ├── notes/                               Lecture notes, screenshots.
│   └── deliverables/                        What you submit to the course site.
└── shared/
    ├── data_dictionaries/                   ReadMe sheets you have written.
    └── references/                          Course PDFs, papers, links.

Three rules:

  1. Never edit raw data in place. Always work on a copy that lives in outputs/.
  2. Use lowercase, snake_case names. bc_disturbance_reforestation.xlsx — not BC Disturbance Data (Final).xlsx. Spaces and capitals cause problems when you move to R in Chapter 4.
  3. One folder per chapter. Easier to find things in a dozen small folders than in one huge dump.
TipWhere the file goes in this course

For every code chunk in this book to work, the data file should live at:

your_project_root/data/bc_disturbance_reforestation.xlsx

When you see here("data", "bc_disturbance_reforestation.xlsx") in a later chapter, that is the path it refers to.

1.4 The three-sheet pattern: About, ReadMe, Data

Download bc_disturbance_reforestation.xlsx from the BC Data Catalogue and place it in your data/ folder. Open the file. You will see three tabs at the bottom:

  • About — what is this file? Source, publisher, licence, citation, retrieval date.
  • ReadMe — what does each column mean? One row per variable, with description, unit, and notes.
  • Data — the actual 37 rows of data.

Read these tabs in order. Always. Reading the Data sheet before reading About means computing something before you know what it measures.

1.4.1 About sheet

The About sheet answers where did this file come from?. Typical fields:

Field Example value
Dataset Indicator Summary Data: Trends in Silviculture in B.C.
Publisher Resource Practices Branch, Ministry of Forests, B.C.
Source URL catalogue.data.gov.bc.ca/…
Licence Open Government Licence — British Columbia
Cite as Environmental Reporting BC, Trends in Silviculture in B.C.
Retrieved 2026-05-24

The point of the About sheet is provenance. A reader who finds this file two years from now needs to know where it came from and under what licence they can use it.

1.4.2 ReadMe sheet

The ReadMe sheet answers what does each column mean?:

Variable Description Unit Notes NA
Fiscal_Year Fiscal year of the row. April 1 to March 31 of the next calendar year. year (integer) number Integer between 1987 and 2023.
Clearcutting_ha Area harvested using the clearcutting silvicultural system. hectares number Most of BC’s harvest historically. Declines after the mid-1990s as clearcutting-with-reserves grows.
Clearcutting_with_reserves_ha Area harvested using clearcutting with reserves — some trees retained for wildlife, visual, or hydrological values. hectares number Near-zero in the late 1980s. Increases sharply through the 1990s and 2000s due to BC silvicultural policy changes.
Partial_cutting_ha Area harvested using partial cutting silvicultural systems. hectares number Smaller share of total harvest. Used where ecological or visual constraints favour retention.
Harvested_ha Total area harvested = sum of the three harvest types above. hectares number Should equal Clearcutting_ha + Clearcutting_with_reserves_ha + Partial_cutting_ha for every row.
Natural_Disturbance_ha Area disturbed by natural causes (wildfire, pest, wind) tracked for reforestation. hectares number Note the 2017 spike (218,275 ha) — BC’s record-breaking wildfire season.
Total_Disturbance_ha Sum of harvested area + natural disturbance area. hectares number Should equal Harvested_ha + Natural_Disturbance_ha for every row.
Reforestation_ha Area planted or naturally regenerated in that fiscal year. hectares number Includes both human planting and natural regeneration.

One row per column. Notice how the unit (hectares) appears in every numeric row. Never assume the unit; check it.

TipA pattern to keep forever

Every dataset you create for this course should ship with About and ReadMe sheets like these. They cost an hour to write and save many hours of “what does this column mean?” arguments later.

1.4.3 Data sheet

The Data sheet holds the 37 rows of forestry indicators. We will inspect it next.

1.5 Inspecting the Data sheet

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

“I read the About, ReadMe, and Data sheets of bc_disturbance_reforestation.xlsx and concluded it has one row per fiscal year with area columns in hectares. Check whether my reading of the sheets and column meanings is complete and consistent, and tell me what a careful analyst might have missed on a first inspection. Do not summarise the file for me from scratch.”

Open the Data sheet. Before computing anything, answer these five questions:

  1. How many rows of data are there? (Ignore the header.)
  2. How many columns?
  3. What is the earliest and latest year?
  4. Pick one column. What does it measure, and in what units?
  5. Look at the first row. Does anything look unexpected?

For this file:

Question Answer
Rows of data 37
Columns 8
Earliest fiscal year 1987
Latest fiscal year 2023
Maximum Natural_Disturbance_ha (and its year) 218,275.3 ha in 2017

The 2017 spike in Natural_Disturbance_ha is BC’s record-breaking wildfire season. The dataset would look like a flat trend without it. The 2017 row alone is a lesson in why you always look at the extremes of every column.

1.5.1 The eight columns, one by one

Column Type What it measures
Fiscal_Year Integer year The fiscal year the row describes (April–March, named by starting year).
Clearcutting_ha Number (hectares) Area harvested using the clearcutting silvicultural system.
Clearcutting_with_reserves_ha Number (hectares) Area harvested using clearcutting with reserves — some trees left standing.
Partial_cutting_ha Number (hectares) Area harvested using partial cutting (shelterwood, selection, etc.).
Harvested_ha Number (hectares) Sum of the three harvest types above.
Natural_Disturbance_ha Number (hectares) Area disturbed by natural causes (fire, pest, wind).
Total_Disturbance_ha Number (hectares) Total harvested + naturally disturbed area.
Reforestation_ha Number (hectares) Area planted or naturally regenerated that year.
TipA small spot check

Total_Disturbance_ha is the sum of Harvested_ha and Natural_Disturbance_ha. Pick any one row — say row 38 (year 2023) — and check that the formula =E38 + F38 = G38 evaluates to TRUE. If it does, the dataset is internally consistent.

1.6 Data types — and why they matter

Every cell in Excel has a type. The three you care about for this chapter:

Type Example Default alignment
Number 216304.919 right
Date 2024-09-15 right
Text Vancouver left

Excel guesses the type when you open a file. Most of the time it guesses right. But sometimes a number arrives stored as text (maybe because of an extra apostrophe or a stray space) and is then silently skipped when you SUM the column.

A quick test: select a numeric column and look at the status bar at the bottom of the Excel window. It usually shows Sum, Average, and Count. If Count matches your row count but Sum is suspiciously small, some cells are stored as text.

1.8 Number formats: display vs value

A single cell carries two pieces of information:

  • The value (what the cell contains).
  • The display format (how the value is shown).

These are different. Changing the format never changes the value.

1.8.1 A worked example

Type 216304.919 into a cell. The cell displays 216304.919.

Now right-click the cell → Format Cells → Number. Set decimal places to 0. The cell now displays 216,305. The underlying value is still 216304.919, just rounded for display.

Try Format Cells → Currency with two decimals. The cell shows $216,304.92. Same underlying value.

Try Format Cells → Custom with the code 0 "ha". The cell shows 216305 ha. The string "ha" is part of the format, not the value — formulas referencing this cell still treat it as a plain number.

1.8.2 Why this matters

Two cells can display the same thing but have different values. If cell A holds 100000 and cell B holds 99999.999999, both can be formatted to display 100,000. But =A1=B1 returns FALSE.

This is the single most common silent bug in Excel-based analysis. Always be aware of the underlying value, not just the display.

TipTwo formats worth knowing
  • #,##0 — thousands separator, no decimals. Use for hectares.
  • yyyy-mm-dd — ISO 8601 date format. Sorts correctly as text and as a date. Universally readable across regions.

1.9 Writing simple formulas

The five most-used aggregating formulas. Type each in any empty cell on the Data sheet.

Formula What it does
=SUM(H2:H38) Adds every value in column H (Reforestation_ha)
=AVERAGE(G2:G38) Mean Total_Disturbance_ha across all rows
=MIN(F2:F38) Smallest Natural_Disturbance_ha
=MAX(F2:F38) Largest Natural_Disturbance_ha
=COUNT(A2:A38) Number of rows with a numeric year

On this file:

Formula Result
=SUM(H2:H38) 8,264,064 ha
=AVERAGE(G2:G38) 228,089 ha
=MIN(F2:F38) 1,448 ha
=MAX(F2:F38) 218,275 ha
=COUNT(A2:A38) 37

1.10 Checking whether values make sense

Before you trust any number you compute, run a spot check.

1.10.1 Three quick checks

  1. Range check. A mean should sit between the min and the max. If MIN = 50,000, MAX = 250,000, and your AVERAGE says 400,000, something is wrong.
  2. Identity check. If the dataset claims Total_Disturbance_ha = Harvested_ha + Natural_Disturbance_ha, pick one row and verify the identity.
  3. Order-of-magnitude check. BC reforests roughly 200 000 ha per year. If your computed mean comes out at 20,000 or 2,000,000, you have likely selected the wrong column or misformatted the result.

These three checks take seconds and catch most bugs.

TipRead the value, not the display

After you write a formula, click the result cell and look at the formula bar. If the formula says =SUM(B2:B38) but you wanted column H, you have selected the wrong column. Always read the formula, not just the answer.

1.11 Building a data dictionary

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

“Here is my data-dictionary entry for one column: [paste your column name, type, unit, and plain-language description]. Check whether my description matches the ReadMe, whether I named the correct type and unit, and what a reviewer might question. Do not rewrite the dictionary for me.”

A data dictionary is a separate sheet that lists every column with:

  • Variable name (exactly as it appears in the data).
  • Description in plain English — what does it measure?
  • Unit of measurement, always.
  • Type (number, text, date).
  • Notes — anything a future reader should know.

The ReadMe sheet of bc_disturbance_reforestation.xlsx already has one. Look at it.

1.11.1 How to build your own

In any new workbook:

  1. Add a new sheet, name it ReadMe.
  2. In row 1, type the column headers: Variable, Description, Unit, Type, Notes.
  3. In row 2 onward, add one row per column from your Data sheet.
  4. Be specific. “Reforestation, in hectares” is okay. “Area planted or naturally regenerated in that fiscal year, in hectares” is better.
WarningTest your dictionary

After you write a dictionary, hand the workbook to someone who has never seen the data and ask them to compute one statistic from it without asking you any questions. If they can, your dictionary is good. If they ask what a column means, the dictionary needs work.

1.12 The three habits: inspect first, compute second, verify always

If you take only one thing from this chapter, take this.

1.12.1 Inspect first

Before you compute any number, look at the data. Open the file. Read the About sheet. Read the ReadMe. Scroll through a few rows. Find the extremes — the largest, the smallest, the oldest, the newest.

This takes a few minutes and catches more bugs than any other practice in data analysis.

1.12.2 Compute second

Only after you have inspected do you start typing formulas. You should be able to predict what your formula will return before you press Enter. If the answer surprises you, that surprise is information — either the data is interesting or the formula is wrong.

1.12.3 Verify always

Every time you compute a number, verify it against something:

  • Against a published total. If the BC Ministry says the total reforestation in 2020 was X, your formula should equal X.
  • Against a spot check. A mean cannot be larger than the max or smaller than the min.
  • Against a second method. If you computed it with SUM, also compute it with a PivotTable (Chapter 3). If both agree, trust the number.

1.13 Active learning

These short activities are designed to be done alongside the chapter. Pick whichever match the pace of your session.

1.13.1 Activity 1 — Read the About sheet aloud

In pairs, one learner reads the About sheet, the other listens and asks one clarifying question. Switch.

The point is slowing down. Force yourself to read the metadata before the data.

1.13.2 Activity 2 — Five-question inspection

On your own, complete this inspection worksheet, then compare with a partner. If your answers differ, go back to the About, ReadMe, and Data sheets to resolve them.

Check Your answer
Number of rows
Number of columns
Earliest and latest fiscal year
One column name, its unit, and meaning
One value or pattern that seems important or surprising

1.13.3 Activity 3 — Write five simple formulas

In your working copy (not the raw download), type each formula from the Writing simple formulas section into an empty cell. Confirm each result matches the expected value in the table.

1.13.4 Activity 4 — Verify one identity

On any one row of the Data sheet, verify that Harvested_ha + Natural_Disturbance_ha = Total_Disturbance_ha. Use a formula like =E2 + F2 - G2. The result should be 0 (or very close — Excel sometimes shows tiny non-zero values for floating-point reasons).

1.13.5 Activity 5 — Build a one-row data dictionary

Pick any column from the Data sheet. Write a one-sentence description for it without looking at the ReadMe. Then compare with the ReadMe. How close did you get?

1.14 Practice Demo Lab

ImportantPractice 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:

  1. Your attempt. Work through the tasks below.

  2. 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.

  3. Compare your work with the reference. Where did it match? Where did it differ? What caused the difference, and how did you fix it?

  4. Write your own AI verification prompt. Ask an AI to check your reasoning, code, and outputs — not to produce the answer for you.

  5. 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.”

  6. 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?

NoteLab: Inspect a real BC dataset together

Work in groups of 3 or 4. Use one shared copy of bc_disturbance_reforestation.xlsx (one machine per group, or share the screen).

Task 1 — Inspect the workbook. Open the file. Take turns reading the About sheet, the ReadMe sheet, and the Data sheet. Each group member should be able to answer:

  • Where did this file come from?
  • What does each column measure?
  • How many rows are there?

Task 2 — Small data dictionary task. As a group, pick two columns of the Data sheet and write your own one-sentence description of each in your own words. Compare your descriptions to the ReadMe sheet’s descriptions.

Task 3 — Compute one or two summary values. As a group, choose one of these:

  • Mean Reforestation_ha across all 37 fiscal years.
  • Maximum Natural_Disturbance_ha and the year it happened.
  • Total Harvested_ha across all 37 fiscal years.

Write the formula on the Data sheet. Then sanity-check the result with the check that fits your statistic:

  • Mean — must fall between the column’s MIN and MAX.
  • Maximum — must equal the single largest value in the column (and be ≥ every other row).
  • Total — must be at least as large as the MAX (usually much larger), and never negative for an area column.

Task 4 — Verify one formula. Pick any row of the Data sheet. Verify that Harvested_ha + Natural_Disturbance_ha = Total_Disturbance_ha. Use a formula like =E10 + F10 - G10. The result should be 0.

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 Data sheet showing all of your group’s formulas. Include all group members’ names on the submission.

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

TipReference solution — download

Open the Chapter 1 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.

1.15 Exercises

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

  1. Download bc_disturbance_reforestation.xlsx from the BC Data Catalogue. Place it in the data/ folder of your project. Open it and read the About sheet aloud.

  2. In one sentence, write down what Clearcutting_with_reserves_ha measures in your own words, without looking at the ReadMe sheet. Then compare with the ReadMe.

  3. Find the fiscal year with the smallest Total_Disturbance_ha. What year was it, and what was the value? (Hint: =MIN(...) finds the value; sort the table or scan to find the year.)

  4. Add a new column F_to_D (reforestation-to-disturbance ratio) = Reforestation_ha / Total_Disturbance_ha. In what fraction of fiscal years was reforestation greater than total disturbance?

  5. Set up the folder structure from the Project folder structure section above. Place a copy of the BC silviculture file in data/. Take a screenshot of your folder structure.

  6. Save a cleaned copy of the workbook (rename it bc_disturbance_reforestation_clean.xlsx) into the outputs/ folder. The raw file in data/ should remain unchanged.

  7. Apply ISO date format (yyyy-mm-dd) to any date in any file. Sort by that column. Confirm the sort works correctly.

1.16 Optional, advanced

Items in this section are not required for the main path through the chapter. Read them once you are comfortable with the basics above.

1.16.1 Named ranges

You can give a range of cells a name and refer to it by that name in formulas:

  • Select cells A2:A38 (the Fiscal_Year column).
  • Type years into the Name Box (left of the formula bar).
  • Press Enter.

Now anywhere in the workbook you can write =MAX(years) instead of =MAX(A2:A38). The formula is self-documenting. Manage named ranges with Formulas → Name Manager (or Ctrl + F3).

Skip named ranges in Chapter 1 if you find them overwhelming — the chapter does not depend on them. Chapter 2 will show structured tables, which solve the same problem more cleanly.

1.16.2 Custom number formats

Beyond the standard formats, Excel supports custom format codes. For example, the custom format 0.000 "ha" displays 216304.919 ha. The "ha" is part of the format, not the value.

This is useful for at-a-glance unit labels, but use sparingly — too much custom formatting can make a workbook harder for a collaborator to read.

1.17 A glimpse ahead: From Excel to R

In Chapter 4 we will install R and re-open this same file reproducibly. The Excel moves you learned in this chapter map directly to R commands you will meet later. Here is a preview:

What you did in Excel What you will do in R (Ch 4–5)
Open the file by double-clicking read_excel(here("data", "bc_disturbance_reforestation.xlsx"), sheet = "Data")
Look at the status bar Count dim(bc) and nrow(bc)
Status-bar Sum of column H sum(bc$Reforestation_ha)
Status-bar Average of column G mean(bc$Total_Disturbance_ha)
Scan the Data sheet visually glimpse(bc) and summary(bc)
Apply MIN and MAX range(bc$Natural_Disturbance_ha)

The numbers will be exactly the same. Same dataset, same columns, same answers — but the R version is scriptable, shareable, and reproducible.

You do not need to remember any of this now. The point is just to see that R is not a fresh start. Everything you learn in Chapters 1–3 transfers directly. Chapter 4 will walk through each step in detail.

1.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. Which sheet should you read first when you open bc_disturbance_reforestation.xlsx?
  • About
  • Data
  • ReadMe
  • It does not matter
Read About first — it tells you the source, licence, and citation. Then ReadMe (variable dictionary). Then Data. Reading in the wrong order means you start computing before you know what the columns mean.
2. Where does the dataset come from?
  • A textbook publisher
  • The BC Government, distributed through the BC Data Catalogue
  • A personal blog
  • A private consulting firm
Every dataset in this book is openly licensed and comes from a public source. This one is from Environmental Reporting BC and is distributed through the BC Data Catalogue under the Open Government Licence — British Columbia.
3. The ReadMe sheet is best understood as —
  • A backup copy of the data
  • A data dictionary describing each column
  • A blank sheet for personal notes
  • The location of cleaned data
The ReadMe sheet (also called a data dictionary) has one row per column of the Data sheet — explaining what each column measures, its units, its type, and any notes.
4. True or false: when you change the display format of a cell from "general" to "two decimals", the underlying value also changes.
  • True
  • False
Display format is independent of value. The cell still holds 216304.919 even when it shows 216,304.92. Formulas always operate on the underlying value, not the display.
5. Total_Disturbance_ha should equal —
  • Just Harvested_ha
  • Just Natural_Disturbance_ha
  • Harvested_ha + Natural_Disturbance_ha
  • Reforestation_ha
By definition, total disturbance is harvested area plus naturally disturbed area. Always verify this kind of identity on at least one row — it is a cheap way to confirm the data was assembled correctly.
6. What is the keyboard shortcut to jump to the edge of the data in Excel?
  • Ctrl + Tab
  • Ctrl + Arrow
  • Ctrl + Shift + V
  • F1
Ctrl + Arrow jumps to the next non-empty cell in that direction — usually the edge of your data block.
7. The fiscal year with the largest Natural_Disturbance_ha in the BC dataset is —
  • 1987
  • 2010
  • 2017
  • 2023
2017 was BC's record wildfire season — 218,275 ha of natural disturbance. It dominates the time series. Always look at the extremes of your data; the outliers tell you something about how the world worked that year.
8. Which folder is the right place to store the raw, untouched copy of a downloaded dataset?
  • data/
  • outputs/
  • deliverables/
  • shared/
Raw data goes in data/ and is never edited in place. Cleaning produces files that go in outputs/. Anything you submit goes in deliverables/.
9. To freeze the top row so it stays visible while you scroll, which menu path do you use?
  • Home → Format → Freeze
  • View → Freeze Panes → Freeze Top Row
  • Data → Outline → Group
  • Insert → Header & Footer
View → Freeze Panes → Freeze Top Row keeps row 1 in view while you scroll down.
10. Two cells both display 100,000. Cell A's underlying value is 100000; cell B's is 99999.999999. The formula =A1=B1 returns —
  • TRUE
  • FALSE
  • An error
  • 100,000
The comparison operates on the values, not the displays. The cells look identical but are not equal. Always remember: display ≠ value.
11. Which date format sorts correctly both as text and as a date?
  • 15/09/2024
  • September 15, 2024
  • 2024-09-15 (ISO 8601)
  • 9/15/24
ISO 8601 (yyyy-mm-dd) is the only date format that sorts correctly when treated as text. It is also unambiguous across regions (no day/month confusion).
12. The status bar at the bottom of an Excel window shows Sum, Average, and Count when you select a numeric range. If Count is lower than the number of cells you selected, what is the most likely reason?
  • A sum that matches the visible total
  • Some cells in the range are stored as text and being skipped
  • An average is close to the median
  • Excel is calculating slowly
Count only counts numeric cells. If you selected 37 cells but the status bar says Count: 35, two are stored as text and are being silently skipped by aggregation formulas.
13. When you download bc_disturbance_reforestation.xlsx, what should you do with the raw file?
  • Open it and immediately start editing
  • Keep it unchanged in a data/ folder; save any cleaned version separately
  • Delete the original after copying values
  • Email it to yourself as a backup
Always keep the raw downloaded file unchanged. Save any cleaned or modified version with a different name (and ideally in a different folder, like outputs/) so you can always go back to the original.
14. Which approach is best for documenting the meaning of every column in a workbook?
  • Write a long email when you share the file
  • Trust that the column headers are self-explanatory
  • A dedicated ReadMe sheet in the workbook with one row per column
  • A separate Word document
A ReadMe sheet travels with the data. Emails get lost. Headers are rarely self-explanatory.
15. The fiscal year in BC for forestry reporting runs —
  • January 1 to December 31
  • April 1 to March 31 of the following calendar year
  • July 1 to June 30
  • September 1 to August 31
BC fiscal years run April–March. The fiscal year is named by its starting year — "Fiscal Year 2023" means April 2023 through March 2024.
16. Which formula on the Data sheet computes the total reforestation across all 37 fiscal years?
  • =AVERAGE(H2:H38)
  • =SUM(H2:H38)
  • =COUNT(H2:H38)
  • =MAX(H2:H38)
SUM adds every value in the range. Reforestation_ha is column H in this file. AVERAGE would give the mean per year, not the total.
17. Which of these is the most reliable way to verify a number you computed?
  • Run the same formula twice
  • Recompute the number a different way (a different formula, or against a published total) and see if you get the same answer
  • Round it and check that it looks reasonable
  • Ask AI whether it looks right
Verification means producing the same number a second way. Running the same formula twice just confirms that the formula has not been edited — not that it is correct.
18. The three habits of a good data analyst, in order, are —
  • Compute first, plot second, write last
  • Inspect first, compute second, verify always
  • Read the question, write the code, send the answer
  • Open the file, sum the column, copy to email
Inspect (know your data before you compute). Compute (write the formulas). Verify (cross-check the answer). These three habits, applied every time, prevent more bugs than any single tool ever invented.
19. You receive a file with a column called DBH. The ReadMe says "Diameter at breast height". What unit should you assume?
  • Centimetres — it is the most common
  • Millimetres — for precision
  • Inches — the column name sounds American
  • Do not assume — check the ReadMe more carefully, or ask the data provider
Never assume units. Cm is most common for DBH in Canadian forestry, but it can be mm or inches depending on the source. Getting the unit wrong by a factor of 10 silently changes every later calculation.
20. Reflection. Describe one thing you noticed about bc_disturbance_reforestation.xlsx that surprised you — a column, a value, a pattern, anything.
Things learners typically notice: the 2017 wildfire spike dwarfs every other year of natural disturbance · clearcutting-with-reserves was near-zero in the 1980s and grew through the 1990s — that is a real BC policy shift · reforestation is bigger than the total annual harvest for most years (because both planted and naturally regenerated area count) · the units are hectares — about the size of two football fields, which makes the numbers easier to visualize.

1.19 AI as a debugging companion

TipWhen to use AI in this chapter
  • Ask AI to define a forestry term that appears in the ReadMe but is new to you. “What does ‘silvicultural system’ mean?”
  • Ask AI to explain an Excel formula. Paste the formula verbatim and ask what it does.

1.19.1 When not to use AI in this chapter

  • AI is not the source of truth for what a BC dataset measures. The BC indicator page is. Use the ReadMe and the source URL first.
  • Do not paste an exercise question into AI and copy the answer back. The point of an exercise is the thinking, not the result.

1.20 Reading

Instructor notes

  • This chapter is deliberately beginner-friendly. The main path covers six skills: inspecting About / ReadMe / Data; identifying columns, units, and types; writing five simple formulas; checking whether values make sense; building a one-row data dictionary; setting up a folder structure.
  • Named ranges and custom number formats are moved to the Optional, advanced section. Touch on them only if your group is moving quickly.
  • The group lab is the central hands-on deliverable. Plan to give learners time to complete all five tasks in one session.

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

  1. Raw bc_disturbance_reforestation.xlsx in data/, unchanged.
  2. Working bc_disturbance_reforestation_clean.xlsx in outputs/ containing About / ReadMe / Data, a one-row data dictionary entry, and the five chapter formulas.
  3. Group-lab worksheet or screenshot.

1.20.2 Materials provided alongside this chapter

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

1.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
Inspect workbook (Task 1) 20 % Group can articulate source, columns, row count.
Data dictionary (Task 2) 20 % Two columns described in own words; compared with ReadMe.
Summary value (Task 3) 30 % Correct formula AND a spot check.
Verify identity (Task 4) 20 % Harvested + Natural = Total confirmed on at least one row.
Submission (Task 5) 10 % Worksheet or screenshot is legible; group members named.

1.20.4 Common stumbling points

  • Some learners try to compute the answer to Task 3 from the Summary sheet of the solutions file. Hide that file.
  • Learners often write =E2+F2=G2 and expect to see a number; they get TRUE/FALSE instead. That is correct — point out the difference between a comparison and an arithmetic expression.
  • Some learners edit the raw bc_disturbance_reforestation.xlsx in place. Remind them that the raw file should stay unchanged.
  • Excel-to-R bridge: If learners ask why we are starting in Excel “if we are going to move to R anyway”, the answer is twofold: (a) the column meanings and forestry context are easier to learn with the data sitting visible on screen, and
    1. Chapter 4 deliberately uses the same file to make the transition explicit. The “A glimpse ahead” table at the end of this chapter is the preview of that bridge.