Module 1 lab: getting started in Excel
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.
Plan on about ninety minutes for the eleven steps, and more if you do the Practice boxes. It covers the same ground as 1 Getting Started with Excel, 2 Working with Data in Excel and 3 Describing Data, so those chapters are the place to look if you want more detail on any step.
You need the desktop version of Excel, not the browser one. The web version is missing several things we use today. It is free through the university.
Step 1: Get the data open
Download rm_yields_1990plus.csv. It holds the average yield of eight crops in every Saskatchewan Rural Municipality from 1990 to 2025, with one row per RM-year and one column per crop. Yields are in bushels per acre.
A csv is a plain text file: rows separated by line breaks, columns separated by commas, and nothing else. There is no place in it for a formula, a second sheet, or a chart. Excel will open one and display it in a grid, which makes it easy to forget you are not in a workbook yet.
Open it in Excel. Then save it straight away as an .xlsx file – File > Save As, and change the format to Excel Workbook. If you skip this, everything you write today disappears when you close the file. Excel warns you at that point, but the warning is easy to click through.
Rename the sheet tab Data by double-clicking it.
Check: the columns are Year, RM, Spring Wheat, Durum, Canola, Barley, Oats, Peas, Lentils, Flax, running A to J. The title bar shows a .xlsx file.
If the whole first row landed in column A with commas still in it, Excel has not split the file into columns. That usually means the csv was opened through a route that skipped the import step. Close it without saving and open it again with File > Open.
Step 2: Find the edges of the data
This file is much bigger than it looks, and scrolling to the bottom with the mouse takes a while.
Click cell A1 and press Ctrl+↓ (Windows) or ⌘+↓ (Mac). That jumps to the last row of the block. Ctrl+↑ brings you back.
The rule the jump follows is: move in that direction until the cell before a blank. In a column with no gaps it takes you straight to the end. Try the same keystroke from E1, in canola, and you stop at row 109 instead, because E110 is blank. Column A is the safe one to measure the file with, since Year is filled on every row.
Now use View > Freeze Panes > Freeze Top Row so the headings stay visible while you scroll.
Check: Ctrl+↓ from A1 lands you on row 10650, so there are 10,649 rows of data. The headings stay on screen when you scroll down.
Blank cells are worth noticing here. Not every RM grows every crop, so the crop columns have gaps – 610 blanks in Canola, 3,512 in Durum, 115 in Spring Wheat. A blank means “not reported”, not zero, and that distinction matters once we start averaging.
The gaps are not scattered at random either. Durum is grown in the drier south, so the northern RMs are blank for it down the whole file. Missing data usually has a reason, and the reason changes how you should read an average over what is left.
Step 3: Write a formula with a locked reference
Canola is in column E. Suppose canola sells for $14.00 a bushel and you want the revenue per acre each row implies.
In L1 type Price. In L2 type 14.
In K1 type Canola revenue. In K2 write:
=E2*$L$2Now copy K2 down. Click K2, then double-click the small square at its bottom-right corner – the fill handle. Excel fills the formula down as far as the neighbouring column has data.
Check: K2 reads 308.00 (22.0 bu/ac × $14). K3 reads 337.40 and K4 reads 210.00. Click K3 and the formula bar shows =E3*$L$2 – the E moved down a row, the $L$2 did not.
The $ signs are the whole point. When Excel copies a formula it rewrites the references by however far the formula moved. Copy from K2 to K3 and everything shifts down one row: E2 becomes E3, and that is what you want, because each row should use its own canola yield. L2 would become L3 for the same reason, and that is what you do not want, because the price lives in one fixed cell. A $ switches that rewriting off for whatever follows it, so $L$2 stays put no matter where the formula goes.
Without the $, K3 reads =E3*L3, and L3 is empty. Excel treats empty as zero, so you get a column of zeros from row 3 down. This is the common mistake here, and the thing to notice is that it does not produce an error message – just a plausible-looking column of wrong numbers.
$ is worth a keystroke rather than typing: click into the formula, put the cursor on the reference, and press F4 (Windows) or ⌘+T (Mac). Pressing it repeatedly cycles L2 to $L$2 to L$2 to $L2 and back.
Locking only half a reference
$L$2 locks both the column and the row, which is right when the formula only ever copies down one column. It is the wrong tool as soon as you copy in two directions, and that is what those half-locked forms in the F4 cycle are for.
Suppose you want the revenue at two prices, $14 and $16, side by side. Put the prices in a row: in P1 type 14 and in Q1 type 16. Then in P2 write:
=$E2*P$1Fill P2 right into Q2, then select both and fill them down a few rows.
Check: P2 is 308.00 and Q2 is 352.00 – the same 22.0 bu/ac at $14 and at $16. P3 is 337.40 and Q3 is 385.60. Click Q3 and the formula bar shows =$E3*Q$1.
Read the two $ separately. $E2 locks the column and lets the row move, so every cell in the block reaches back to column E for its yield, but each row uses its own. L$2 locks the row and lets the column move, so the block reads across the price row while staying on row 2. Each $ holds still the one thing that should not change as the formula travels in that direction.
The working question when you write a reference: as this formula is copied, which part should follow it and which part should stay? Put a $ on whatever should stay.
Practice
Some rows are blank in column E, and those give a revenue of 0 rather than a blank. Scroll down and find one. Why does Excel return 0 rather than leaving the cell empty?
Excel treats a blank cell as zero inside an arithmetic formula, so =E2*$L$2 becomes 0*14. The cell looks like a real measurement of zero revenue, but it is really a missing observation. This is the same problem as filling blanks with zeros before taking an average – Ranges and Functions – and it is why AVERAGE skipping blanks is usually what you want.
Step 4: Descriptive statistics
Now summarise a whole column. We will use Spring Wheat, column C.
Pick an empty patch of the sheet – N1 is fine – and build a small block of labels and formulas. Put the label in N and the formula in O:
=COUNT(C2:C10650)
=AVERAGE(C2:C10650)
=MEDIAN(C2:C10650)
=MIN(C2:C10650)
=MAX(C2:C10650)
=STDEV.S(C2:C10650)Each of these takes a range and returns one number from it. The range is the same every time, which is the normal shape of a summary block: one column of labels, one column of formulas all pointed at the same data.
Typing the range by hand is slow. Instead type =AVERAGE( then click C2 and press Ctrl+Shift+↓, which selects to the bottom of the column. Then type the closing bracket and press Enter.
Check: COUNT gives 10534, AVERAGE gives 35.31, MEDIAN gives 33.3, MIN gives 0, MAX gives 198 and STDEV.S gives 12.09.
The range starts at C2, not C1. C1 holds the heading. Starting a range on the header row is the single most common thing that goes wrong on a summary block, and on a file this size you cannot see it: =AVERAGE(C1:C10649) gives 35.31 too, because AVERAGE ignores the text and the range has just slid one row up, dropping the last observation. The number is wrong in the sixth decimal place and right everywhere you would look.
It stops being invisible on a small table, which is where the test puts you. Copy the ten 2023 canola yields for RMs 1 to 10 into a blank sheet with a heading in row 1, so the numbers run A2:A11 and read 36.8, 34.4, 31.5, 30.8, 23.9, 21.0, 21.0, 20.5, 17.9, 18.0. The mean is 25.58 over ten values. Write =AVERAGE(A1:A10) instead and you get 26.42 over nine, because the range picked up the heading at the top and stopped one short at the bottom. Both numbers are plausible canola yields and neither cell shows an error.
The check that catches it is COUNT on the same range. Ten rows of data should give 10; =COUNT(A1:A10) gives 9. Put a COUNT at the top of every summary block and read it before you read anything else.
COUNT returns 10,534 rather than 10,649 because it counts numbers, and 115 rows have no spring wheat figure. That gap between the row count and the observation count is worth checking whenever you summarise a column. COUNTA is the other one: it counts non-empty cells of any kind, text included, so =COUNTA(C2:C10650) also gives 10,534 here but would count a cell reading “n/a” where COUNT would not.
All of these skip blanks rather than treating them as zero. You can see what the alternative would cost: =SUM(C2:C10650)/10649 divides the total by every row rather than by the rows with data, and gives 34.93 instead of 35.31. AVERAGE makes that choice for you without saying so.
STDEV.S has a partner, STDEV.P. The .S version is for a sample, the .P version for a complete population. With 10,534 observations the two agree to more decimal places than anyone will report, but .S is the one to reach for by default – almost all data is a sample of something.
The mean sits above the median, 35.31 against 33.3. That tells you the distribution is stretched further to the right than to the left – a long tail of high-yielding RM-years pulling the mean up.
That MAX of 198 bu/ac deserves suspicion. It is RM 314 in 2018, and the second-highest value anywhere in 36 years is 88.6. A spring wheat crop of 198 bu/ac is not plausible.
Practice
Add the range and the coefficient of variation to your block. The range is the maximum minus the minimum; the CV is the standard deviation divided by the mean. Then work out what the range would be if that 198 row were a typo and the true maximum were 88.6.
=MAX(C2:C10650)-MIN(C2:C10650)
=STDEV.S(C2:C10650)/AVERAGE(C2:C10650)The range is 198 and the CV is 0.34. Drop the suspect row and the range falls to 88.6 – less than half. One observation out of 10,649 more than doubles the range, which is why the range is a weak measure of spread. The mean barely moves (35.31 to 35.29) and the median does not move at all.
Step 5: Quartiles
A percentile is the value with a given share of the data below it. The 25th percentile, or first quartile, has a quarter of the observations below it; the median is the 50th. QUARTILE.INC takes the range and a quarter number from 0 to 4, and PERCENTILE.INC takes the range and a proportion.
Add three more rows to the same block:
=QUARTILE.INC(C2:C10650,1)
=QUARTILE.INC(C2:C10650,3)
=PERCENTILE.INC(C2:C10650,0.9)Check: Q1 is 27.2, Q3 is 42 and the 90th percentile is 52.5.
The two functions overlap. =QUARTILE.INC(C2:C10650,1) and =PERCENTILE.INC(C2:C10650,0.25) are the same request written two ways and both give 27.2. Use whichever reads more clearly: quartiles for the standard four, percentiles when you want something like the 90th.
The argument that trips people is the second one. QUARTILE.INC(...,1) is the 25th percentile, not the 1st. Ask for quarter 3 and you get 42; ask for 0.75 from PERCENTILE.INC and you get the same 42.
So half of all RM-years fall between 27.2 and 42 bu/ac. The interquartile range is Q3 minus Q1, 14.8 bu/ac. Unlike the range it does not care about the 198: that value is out past the 75th percentile either way, and moving it further out does not shift where the quarter marks fall.
Step 6: IF
IF makes a decision row by row. It takes three arguments in this order: the condition, the value to return when the condition is true, and the value to return when it is false.
In M1 type Rating. In M2:
=IF(C2>=40,"Good","Poor")Fill it down.
Check: M2 reads Poor (spring wheat in that row is 30.7). Row 21 – 2009, RM 1, 41.8 bu/ac – is the first Good.
Text inside a formula needs quotation marks. The condition does not, because C2>=40 is a comparison rather than a label. Leave the quotes off and Excel reads Good as the name of something it cannot find, and you get #NAME? down the whole column.
The condition can be any comparison: <, >, <=, >=, =, or <> for “not equal to”. It can also compare against another cell rather than a typed number. If you put the threshold in a cell – say 40 in P5 – then =IF(C2>=$P$5,"Good","Poor") does the same job and lets you change the cut-off in one place. The $ is there for the reason it was in Step 3.
The returned values do not have to be text. =IF(C2>=40,1,0) gives a column of ones and zeros, which is the form you want when the next thing you do is sum or average the column.
The problem here is the blanks. A blank cell compared against a number behaves as zero, so C2>=40 is false for the 115 rows with no spring wheat figure, and they all come back Poor. Nothing marks them as different from a genuine 20 bu/ac crop. If you want them held out, test for the blank first:
=IF(C2="","",IF(C2>=40,"Good","Poor"))That reads: if C2 is empty return an empty string, otherwise do the original decision. An IF inside another IF is the standard way to add a case, and the nesting is easier to read if you write the outer one first and paste the original inside it.
Practice
Count how many rows got each rating, using COUNTIF on your new column. Do the two numbers add to 10,649?
=COUNTIF(M2:M10650,"Good")
=COUNTIF(M2:M10650,"Poor")3,115 Good and 7,534 Poor. They do add to 10,649, but that hides a problem: the 115 blank rows were rated Poor, because a blank compares as less than 40. Only 7,419 rows actually have a spring wheat yield under 40. An IF will happily classify missing data unless you tell it not to.
Step 7: COUNTIF, AVERAGEIF, and the plural versions
These do a calculation on the rows that meet a condition, rather than on all of them. They are how you answer “what about just 2021?” without sorting, filtering, or copying anything to a new sheet.
COUNTIF takes the range to test and the condition. AVERAGEIF takes a third argument: the column holding the values to average.
Try these in empty cells:
=COUNTIF(C2:C10650,">50")
=COUNTIF(A2:A10650,2025)
=AVERAGEIF(A2:A10650,2025,E2:E10650)Check: 1,324 RM-years had spring wheat above 50 bu/ac; there are 295 rows for 2025; and canola averaged 43.94 bu/ac in 2025.
A condition with an operator in it goes in quotes: ">50", "<40", "<>0". A plain value to match exactly does not need them, so 2025 works bare. Text criteria do need them: "Good". If you want to compare against a cell rather than a typed number you have to join the operator to it with &, as in ">"&$P$5, because ">P5" would search for the literal text.
Note what the third formula did. It tested column A for the year and then averaged column E – two different columns, matched row by row. COUNTIF has no equivalent third argument, because counting rows needs only the test.
Matched row by row is the part to take literally. The two ranges have to start and end on the same rows, because Excel pairs them by position rather than by row number. Write =AVERAGEIF(A2:A10650,2025,E3:E10651) and the average range is offset by one: every 2025 test now reads the canola yield from the row below the one it tested. You get 21.29 instead of 43.94. Excel does not object, because both ranges are the same height and that is the only thing it checks.
A half-typed drag does the same damage in the other direction. =AVERAGEIF(A2:A10650,2025,E1:E10649) reads one row too high and returns 31.50. Three formulas, three different answers, one correct. When you build these by clicking rather than typing, glance at the row numbers in the formula bar before you press Enter.
The other criteria mistake is writing a value where a comparison was wanted. =COUNTIFS(A2:A10650,2025,C2:C10650,50) counts the 2025 rows whose spring wheat is exactly 50, not the ones above it. Across the whole file only 25 rows hit exactly 40 bu/ac, against 3,090 above it, so the equals version returns a small number that looks like a reasonable count of something. The > has to be inside the quotes: ">50".
For two conditions at once you need the plural versions, COUNTIFS and AVERAGEIFS. Watch the argument order, because it changes between them. COUNTIFS is pairs of range-and-condition all the way along. AVERAGEIFS puts the column being averaged first, then the pairs.
=COUNTIFS(A2:A10650,2025,C2:C10650,">50")
=AVERAGEIFS(C2:C10650,A2:A10650,2021)
=AVERAGEIFS(C2:C10650,A2:A10650,2025)Check: 187 RMs beat 50 bu/ac of spring wheat in 2025. Spring wheat averaged 30.22 bu/ac in 2021 and 52.96 in 2025.
The order is the thing that catches people, because AVERAGEIF and AVERAGEIFS want their arguments in opposite arrangements. AVERAGEIF is test, condition, values. AVERAGEIFS is values, test, condition. Writing =AVERAGEIFS(A2:A10650,2025,C2:C10650) gets you an error rather than a wrong answer, which is the good kind of mistake.
There is nothing special about two conditions – AVERAGEIFS takes as many pairs as you want to add. Spring wheat in 2025, in the RMs where canola also beat 40 bu/ac:
=AVERAGEIFS(C2:C10650,A2:A10650,2025,E2:E10650,">40")Check: 58.27 bu/ac, over the 221 RMs meeting both conditions, against 52.96 for 2025 as a whole. The RMs that did well in canola also did well in wheat.
SUMIF and SUMIFS exist too and follow exactly the same pattern, SUMIFS putting the summed column first.
2021 was a drought year across the prairies, and the file shows it plainly. Canola averaged 21.86 bu/ac that year against 43.94 in 2025.
Practice
Barley is column F. Find the average barley yield in 2021 and in 2025, and count how many RMs yielded under 40 bu/ac of barley in 2021.
=AVERAGEIFS(F2:F10650,A2:A10650,2021)
=AVERAGEIFS(F2:F10650,A2:A10650,2025)
=COUNTIFS(A2:A10650,2021,F2:F10650,"<40")Barley averaged 34.83 bu/ac in 2021 and 72.84 in 2025 – a bit more than double. 170 RMs came in under 40 bu/ac in 2021.
Practice
What fraction of RMs reporting canola beat 40 bu/ac in 2023, and what fraction in 2025? Format both as percentages. You need two COUNTIFS: one for the rows meeting the condition and one for the rows that reported canola at all.
=COUNTIFS(A2:A10650,2023,E2:E10650,">40")
=COUNTIFS(A2:A10650,2023,E2:E10650,">0")114 out of 289 in 2023, which is 39.4%. In 2025 it is 224 out of 290, or 77.2%.
The second COUNTIFS is the part to get right. The denominator is the RMs that reported a canola yield, not the 295 rows the year has – ">0" counts the numeric cells and leaves the blanks out. Divide by 295 instead and 2023 reads 38.6%, which is wrong by the five RMs that grew no canola.
A count on its own answers almost nothing here. 114 sounds like a lot until you learn it is out of 289, and the jump from 39% to 77% is the finding. Whenever a question asks for a fraction, share or percentage, the count is the intermediate step and the division is the answer.
Step 8: SUMPRODUCT
SUMPRODUCT multiplies two ranges together element by element and adds up the results. It is one formula for a job that otherwise needs a helper column of products and a SUM underneath it.
The 2025 row for RM 1 has spring wheat 60.4, canola 48.3, barley 80.2 and oats 122.6 bu/ac. Put those four prices in a row somewhere free – 8.00, 14.00, 5.50 and 4.50 – directly above or beside the four yields, so the two blocks are the same shape.
=SUMPRODUCT(C37:F37,$P$8:$S$8)Adjust the references to wherever your yields and prices actually sit. The first range is the four yields, the second the four prices.
Check: 2152.20. That is 60.4 × 8.00 plus 48.3 × 14.00 plus 80.2 × 5.50 plus 122.6 × 4.50, the gross revenue per acre if one acre of each crop were grown at those yields and prices.
The failure here is giving it one range instead of two. =SUMPRODUCT(C37:F37) is a legal formula – with nothing to multiply against, it just adds the range and returns 311.50. A sum of four yields, sitting in a cell labelled revenue, in dollars. Nothing flags it.
The two ranges also have to be the same shape. Hand it a four-cell block and a five-cell block and you get #VALUE!, which is the helpful version of the mistake. The dangerous one is two ranges of equal length that are offset from each other, exactly as in Step 7: four yields against four prices starting one column over pairs wheat with the canola price and the arithmetic proceeds without complaint.
The weighted average is built from the same formula. Multiply each value by its weight, sum, then divide by the total weight:
=SUMPRODUCT(C37:F37,$P$8:$S$8)/SUM($P$8:$S$8)Check: 67.26. That is the price-weighted average yield across the four crops, which is not a number anybody wants. The shape is the point – swap the prices for acres and the same formula gives the acreage-weighted average yield, which is what a farm’s actual average yield means. Section 2 of the test bank asks for exactly this against a column of acres.
Step 9: XLOOKUP
The file identifies RMs by number, which is hard to read. The names are in a second file.
Download rm_lookup.csv, open it, and copy its contents into a new sheet in your workbook called Lookup. It has 296 rows of data, with RM_Number in column A and RM_Name in column B.
Back on the Data sheet, in N1 type RM name – or any free column. In the row below:
=XLOOKUP(B2,Lookup!$A$2:$A$297,Lookup!$B$2:$B$297)Fill it down.
The three arguments are the value to find, the range to search in, and the range to return from. Read it as a sentence: take the RM number in B2, find it somewhere in Lookup!$A$2:$A$297, and give me back whatever sits in the matching position of Lookup!$B$2:$B$297. The second and third ranges have to be the same height, because XLOOKUP works by position – the 7th cell it searches corresponds to the 7th cell it returns.
The two lookup ranges are locked with $ because the table stays where it is; only B2 should move as the formula copies down. This is the same reasoning as Step 3, and the consequence of getting it wrong is worse here. Without the $, row 3 searches Lookup!A3:A298 – a window that has slid down one row past the end of the table. Most rows still find a match, so you get names that look fine and are wrong for the rows near the bottom.
Check: the first rows all show Argyle, which is RM 1. Row 6852 – RM 314 in 2018, the 198 bu/ac row – shows Dundurn.
Scroll and you will find some #N/A results. Three RMs in the yield file (278, 408 and 529) are not in the lookup table at all, which affects 30 rows. #N/A means the lookup found nothing. It is doing exactly what it should – an unmatched row genuinely has no name to return.
If you would rather see something readable than #N/A, XLOOKUP takes an optional fourth argument for exactly that:
=XLOOKUP(B2,Lookup!$A$2:$A$297,Lookup!$B$2:$B$297,"No name")Use it only when you already know why the misses happen. Replacing #N/A with blank text before you have looked at the unmatched rows hides the very thing the error was telling you.
The other way to get #N/A on every row is a type mismatch: RM numbers stored as text in one file and as numbers in the other look identical on screen but never match. If a lookup fails for all rows rather than a few, check whether one column is left-aligned and the other right-aligned, which is Excel’s default hint that one is text.
Practice
The lookup file also has a Weather_Station column (column E) and a Station_km column (F) giving the distance to it. Bring the weather station name across for each row.
=XLOOKUP(B2,Lookup!$A$2:$A$297,Lookup!$E$2:$E$297)Only the third argument changes – you are searching the same column for the same value, just returning something different. RM 1 uses Kipling, RM 314 uses Outlook PFRA. Nothing about the search changes when you want a different field, which is the advantage of XLOOKUP over counting columns across.
Practice
A single RM number does not identify a row in this file, because each RM has 36 of them. Pull out the canola yield for RM 1 in 2023 by searching on both the RM and the year at once. The trick is that XLOOKUP takes ranges, and two ranges joined with & make a single combined key.
=XLOOKUP(1&2023,$B$2:$B$10650&$A$2:$A$10650,$E$2:$E$10650)36.8 bu/ac. Spring wheat for RM 100 in 2023, the same way, is 52.9.
The first argument builds the text 12023 and the second builds a matching column of joined keys, one per row. XLOOKUP then searches that combined column as if it were an ordinary one. The two halves have to be joined in the same order on both sides – write 1&2023 against Year&RM and every row returns #N/A, because you are comparing 12023 to 202 3-style strings that were built the other way round.
Searching on the RM alone would return the first row it finds for RM 1, which is 1990, with nothing to indicate it is the wrong year. This is the same failure as a join on an incomplete key, and it is the reason to say out loud what identifies one row before writing the lookup.
Step 10: Sort and filter
A filter hides rows that do not meet a condition. A sort reorders them. Both change what you see rather than what the file contains, but only one of them is reversible by clicking Clear – a sort rearranges the rows for good, and undoing it means Ctrl+Z or a column you can sort back by. Here the Year and RM columns let you get back to the original order.
Turn filters on with Ctrl+Shift+L (Windows) or ⌘⇧F (Mac). Every heading gets a dropdown arrow.
Click the arrow on Year and uncheck everything but 2021. The fastest route is to untick Select All first, then tick the one you want. Then click the arrow on Canola and sort smallest to largest.
Check: 295 rows remain. The lowest canola yield in 2021 is 4.0 bu/ac in RM 45 (Mankota), then 4.1 in RM 19 (Frontier). The row numbers down the left are no longer consecutive, and they have turned blue.
The blue, non-consecutive row numbers are the signal that a filter is on. It is easy to leave one on, come back later, and draw a conclusion from a tenth of the data. When a sheet looks wrong, the row numbers are the first thing to check.
Excel filtered all ten columns together, not just the one you clicked. That is what you want – a row is a single RM-year, and the yields on it belong to each other. It only works because the data is one unbroken block with a single header row. A blank row in the middle would make Excel treat the part below it as unrelated data and leave it unfiltered.
Now the trap. With the filter still on 2021, put this somewhere free:
=AVERAGE(E2:E10650)Check: it returns 28.29, not the 21.86 you would get for 2021 alone. That 28.29 is the average across all 36 years, hidden rows included.
A filter hides rows from you. It does not hide them from a formula. To summarise a subset you need the conditional functions from Step 7, or you copy the filtered rows to a new sheet first.
There is a third route. SUBTOTAL is the one family of functions that does respect a filter:
=SUBTOTAL(101,E2:E10650)Check: 21.86 – the 2021 figure, matching what AVERAGEIFS gave in Step 7.
The 101 is a code for which calculation to do: 101 is average, 109 is sum, 102 is count. The point of SUBTOTAL is that the answer changes as you change the filter, which suits a sheet somebody is going to click around in. AVERAGEIFS states its condition in the formula, so it gives the same answer whatever is on screen. That makes it the safer choice for a number you are going to report.
Clear the filter before moving on – the dropdown on Year has a Clear Filter option, or Ctrl+Shift+L turns filtering off entirely.
Step 11: A PivotTable
For grouping by crop you want the same data in long format, where the crop name is a column rather than a heading. Download rm_yields_1990_2025.csv and open it as a separate workbook. It has 71,104 rows: one per RM-year-crop, with Year, RM, Crop, Yield and Unit.
Long format is what makes grouping possible. In the wide file each crop is its own column, so “average by crop” means writing eight separate formulas. Here Crop is a column of labels, and a PivotTable can split the rows into piles by whatever a label column contains.
Click any cell in the data and choose Insert > PivotTable, putting it on a new worksheet.
You get an empty frame and a field list with the five column names in it and four boxes underneath: Rows, Columns, Values, Filters. The boxes are the whole interface. A field dragged into Rows becomes one row per distinct value in it. A field in Columns does the same across the top. A field in Values is the thing that gets calculated for each cell, and a field in Filters limits which rows go into the calculation at all without appearing in the table.
Drag Crop into Rows and Yield into Values.
Check: eight rows, one per crop, with a number beside each.
Excel will almost certainly show you Sum of Yield, which is a meaningless number here – the total of every yield ever recorded for that crop, which depends mostly on how many RMs grow it. Click the value field, choose Summarize Values By, and pick Average.
Excel guesses Sum for numeric fields and Count for text ones. The guess is about the data type, not about what you meant, so check it every time. Sum of Yield and Average of Yield look equally plausible at a glance, and the heading above the column is the only thing on screen that says which you are looking at.
Check: eight rows. Barley 51.75, Canola 28.29, Durum 33.56, Flax 20.53, Lentils 1208.39, Oats 64.71, Peas 31.76, Spring Wheat 35.31.
Lentils at 1,208 stands out because lentils are reported in pounds per acre while everything else is in bushels. The Unit column records this. The grand total at the bottom – 142.82 – averages pounds together with bushels and means nothing at all. Excel will not warn you.
Now pivot it. Drag Year into Columns.
Each cell is now the average yield for one crop in one year – the rows and columns together pick out which observations go into it. This is the move the tool is named for, and it is the reason a PivotTable beats a screenful of AVERAGEIFS: eight crops across 36 years is 284 conditional averages, and you got them by dragging one field.
That gives 36 columns, which is too many to read. Click the Year dropdown and keep only 2021 through 2025.
Check: canola reads 21.9, 35.4, 33.9, 31.5, 43.9 across 2021 to 2025. Spring wheat reads 30.2, 45.8, 42.9, 47.6, 53.0. Every crop has its worst year in 2021.
The rest of this step is about getting familiar with the field list. A PivotTable is quick to rearrange, and the way to learn it is to keep dragging things until you can predict what will happen before you let go. Try each of these, and put the table back to crops in Rows and years in Columns afterwards.
Swap the axes. Drag Year out of Columns and into Rows, above Crop. Same numbers, read down instead of across, with a subtotal for each year. The order of the fields within Rows is what sets the nesting: Year above Crop gives you each year broken into crops, and reversing them gives each crop broken into years. Drag it back.
Change what the numbers mean. Click the value field, choose Summarize Values By, and try Max, then Min, then Count, then back to Average. For canola in 2021 the average is 21.9, but the best RM managed 39.4 and the worst 4.0 – one number for the province hides a very wide spread.
Show the numbers as percentages. Click the value field, choose Show Values As, and pick % of Row Total. Each cell is now its share of the five years in that row rather than a yield. Spring wheat’s 2021 reads 13.8%, against 24.1% for 2025: an even split across five years would be 20% each, so 2021 came in well under and 2025 well over. Set it back to No Calculation when you are done looking. The underlying data has not changed – Show Values As only changes how the same number is displayed, which is why it is easy to leave on and misread later.
Filter to one crop. Drag Crop into Filters instead of Rows, and pick Canola from the dropdown at the top. The table stops being one row per crop, because Crop is no longer defining the rows; it is now restricting which rows of the source data are used. Now put RM into Rows. You have 295 rows, one per RM.
Sort by the numbers, not the labels. With canola filtered and 2025 in Columns, right-click any yield in the 2025 column and choose Sort > Largest to Smallest. A PivotTable sorts by its row labels by default, which puts RM 1 first and tells you nothing. Sorting on a value column is how you turn the table into a ranking.
Check: RM 287 is top at 61.0, then RM 292 at 57.3 and RM 257 at 56.7.
Add a slicer. With any cell in the PivotTable selected, choose Insert > Slicer, tick Crop, and click through a few crops. A slicer is the same filter, in a form somebody else can use without touching the field list. The selected crop is visible on the sheet, which is the practical advantage over the Filters box – a filter set in the dropdown shows only as a small funnel icon that nobody notices.
One thing a PivotTable does not do is update itself. Change a number in the source data and the table keeps showing the old figure until you right-click it and choose Refresh.
There is no Check for the last few – the point is to have moved the fields around enough that the field list stops feeling like guesswork.
Practice
Add a second copy of Yield to Values and summarise it as Count instead of Average, so each average sits beside the number of observations behind it. How many canola observations are there in 2021?
Drag Yield into Values a second time, then set the new one to Count through Summarize Values By.
There are 290 canola observations in 2021, out of 295 RMs. Averages resting on very different numbers of observations are not equally reliable, and putting the count beside the mean is the cheapest way to see that. Durum has only 885 observations across all five years against canola’s 1,453, because far fewer RMs grow it.
Practice
The grand total of 142.82 was meaningless because it averaged pounds per acre with bushels per acre. Use the PivotTable to get a grand total that does mean something, and say what it is.
Drag Unit into Filters and select bu/ac, or put Unit into Rows so the two unit groups are subtotalled separately. Either way the lentils drop out and the grand total becomes 38.54 bu/ac across the seven bushel crops.
It is still an average of different crops, which is not a number you would report. But it is at least an average of things measured the same way.
Practice
How many RMs reported each crop in 2023? Build this as a PivotTable and say which crop was reported in the fewest.
Crop in Rows, Yield in Values set to Count, and Year in Filters set to 2023.
Canola 289, Spring Wheat 285, Barley 285, Peas 282, Oats 200, Lentils 196, Durum 173, Flax 164. Flax is the fewest.
Count is the aggregation to reach for when the question is “how many”, and it is the one Excel gives you by accident on a text field and never on a numeric one. Dragging Yield in and leaving it as Sum here would produce eight large numbers that answer a question nobody asked.
These counts are counts of rows, and they differ by crop because the long file has no row at all for a crop an RM did not report. 2023 has 293 RMs and eight crops, which would be 2,344 rows if every RM grew everything; the file has 1,874. The gap is the same missing data that showed up as blank cells in the wide file, stored by leaving the row out instead.
Checking your own work
Almost every mistake in this lab returned a number rather than an error. A misaligned AVERAGEIF, a range that started on the header, a SUMPRODUCT with one argument, a lookup without its $ – all of them produce something that fits in the cell and looks like an answer. So the habit that matters is having a way to test a result you cannot check by eye.
Four checks cover most of it.
Does the count match what you expect? Work out the number of rows before you look at it. 295 RMs in a year, 10,649 rows in the file, 36 years. If a COUNTIFS for 2025 returns 294 or 296, the range is off by a row at one end. This is the check that catches the header-row error, and it is the reason to put COUNT at the top of a summary block rather than at the bottom where nobody reads it.
Does the mean sit between the minimum and the maximum? It has to, so this only fails when the ranges disagree with each other – a mean computed over one column and a maximum over another, or a block where you dragged one formula further than the rest. It costs nothing because MIN and MAX are already in the block.
Does the total change when you filter? Filter to 2021 and watch the cell. If a number that should describe 2021 does not move, it is reading the whole file, which is the Step 10 trap. If a number that should describe the whole file does move, you have used SUBTOTAL where you wanted AVERAGE.
Is the magnitude right? You know roughly what these numbers should be by now: canola in the twenties to forties, spring wheat in the thirties to fifties, barley higher, lentils in the hundreds because of the units. A canola average of 4.4 or 440 is a units or a range problem, not a bad year. Most wrong answers on a test are wrong by a factor of ten, by a factor of the number of rows, or by the difference between a count and a fraction – and all three are visible if you ask what size the answer should be before you read what it is.
Two results that should agree are the strongest check of all. Step 10 computed the 2021 canola average twice, once with AVERAGEIFS and once with SUBTOTAL on a filter, and both gave 21.86. When a number matters, get it a second way.
If you finish early
The best use of any time left is the test bank: Module 1 test bank. The test draws one question from each section of it, so work through examples from all four sections – building a worksheet, descriptive statistics, conditional functions and lookups, and PivotTables – rather than doing five of the kind you already find easy. Every question has a worked answer behind a dropdown and an answer workbook to download.
Doing them here rather than at home is the point of the lab: this is the one time you can get an answer to “why did mine come out different?” while the file is still open in front of you.
Three other things worth trying if you want a break from the bank.
Make a histogram of spring wheat. On the Data sheet select C2:C10650, then Insert > Statistical Chart > Histogram. Right-click the horizontal axis, choose Format Axis, and try a few bin widths. The rough starting point of \(\sqrt{n}\) bins would be about 103 here, which is a lot – try 5 bu/ac bins instead and see whether the shape survives. The right tail you inferred from the mean-median gap in Step 4 should be visible.
Then break a few things on purpose, so you recognise them later:
- Write
=E2*L2without the$signs and fill it down. - Write
=XLOOKUP(B2,Lookup!A2:A297,Lookup!B2:B297)with no$in the ranges and fill it down. - Write
=AVERAGEIF(C2:C10650,A2:A10650,2025)with the arguments in theAVERAGEIFSorder.
The first two give wrong answers rather than errors, which is the dangerous kind of mistake. The third gives an error. All three are mistakes you will make this term.
Before you leave
Save your workbook.
Everything in Module 1 is in that one file: a locked reference, a block of summary statistics, an IF column, the conditional counts, a lookup against a second table, and a PivotTable.