Difficulty: Beginner | Prerequisites: Basic arithmetic, familiarity with spreadsheet software
This assignment introduces you to working with real-world climate datasets in a spreadsheet environment. It sits at the very start of the course and assumes no prior Excel knowledge. The core purpose is to get comfortable importing, manipulating, and visualising tabular scientific data, which underpins almost every quantitative analysis you will do later. If you can calculate averages across rows, convert units using named variables, apply conditional formatting, and build a line chart, you have the toolkit this assignment tests.
You are given a CSV file of average monthly temperatures (in Celsius) for 41 Canadian weather stations recorded between 1971 and 2000. The assignment asks you to compute annual averages, convert those averages to kelvins using a named variable, highlight the highest and lowest monthly temperatures with conditional formatting, and plot a line chart comparing three stations across the year.
CSV (comma-separated values)
A plain-text file format where each line is a data row and values within a row are separated by commas. Any spreadsheet application, database, or programming language can read it.
Think of it as: the simplest possible way to store a table, with no formatting, no formulas, just raw text and commas.
XLSX (Excel Workbook)
Microsoft Excel's native binary format. Unlike CSV, it preserves formulas, formatting, charts, and multiple worksheets in a single file.
Think of it as: CSV's richer cousin that remembers everything you did to the data.
AVERAGE() function
An Excel function that returns the arithmetic mean of a range of cells. Syntax: =AVERAGE(B3:M3). It sums the selected values and divides by the count.
In simple terms, this means: add up all the numbers in the range and divide by how many there are.
Named range
A human-readable label assigned to a cell or block of cells in Excel via the Name Manager (Formulas tab). Once named, you use the label in formulas instead of cell references.
Think of it as: giving a variable a proper name so your formulas read like sentences, not grid coordinates.
Kelvin (K)
The SI base unit of thermodynamic temperature. Its zero point is absolute zero (the lowest physically possible temperature). Kelvin uses the same increment size as Celsius, so converting is a simple offset.
In simple terms, this means: Kelvin = Celsius + 273.15. No negative temperatures on this scale in everyday climate data.
Conditional formatting
An Excel feature that changes a cell's appearance (fill colour, text colour) based on its value. The Top/Bottom Rules subset highlights the N highest or lowest values in a selected range.
Think of it as: automatic colour-coding that makes extremes jump out of a large table.
Climate normals
Standard 30-year averages of meteorological variables (temperature, precipitation, etc.) used as a baseline for comparing current conditions. This dataset uses the 1971 to 2000 normal period.
Think of it as: the "expected" weather for a location, averaged over three decades to smooth out year-to-year noise.
The source file (daily_temperature_canada.csv) contains 43 rows and 13 columns.
Column A lists 41 Canadian weather stations by name and province/territory.
Columns B through M hold the average daily temperature for each month (January to December), in degrees Celsius.
The data represents climate normals for the period 1971 to 2000, sourced from Environment Canada.
Stations span the full geographic range of Canada, from southern Ontario to the High Arctic (Alert, Nunavut).
Open the CSV in Excel, then immediately Save As an .xlsx file (File > Save As > Excel Workbook). This preserves any formulas, formatting, and charts you add.
Insert a new row at the very top (select Row 1, right-click > Insert).
Merge cells A1:M1 and type the table title: "Average daily temperature (°C) by month for selected weather stations, 1971 to 2000".
Format the merged cell: font size 14, bold, centre-aligned.
In cell N2, type the header "Annual" (bold, size 11).
In cell N3, enter the formula =AVERAGE(B3:M3). This computes the arithmetic mean of the 12 monthly temperatures for the first station.
To fill the rest of column N, select N3, then double-click the small cross at the bottom-right corner of the cell (the fill handle). Excel copies the formula down to every row that has data beside it.
The result is the annual average temperature in Celsius for each station.
The conversion formula is: T(K) = t(°C) + a, where a = 273.15.
In cell O2, type "Annual (K)" as a header. In cell S2, type "a" as a label. In cell S3, enter 273.15.
Open the Name Manager (Formulas tab > Name Manager > New).
Name the range N3:N43 as "Annual_C".
Name cell S3 as "a".
In cell O3, type the formula =Annual_C + a. Press Enter. Excel fills the entire column with temperatures in kelvins because Annual_C is an array (a named range spanning multiple cells).
Using named variables makes formulas self-documenting. Anyone reading =Annual_C + a understands the intent without tracing cell references.
Select the range B3:M43 (all monthly temperature values).
Home tab > Conditional Formatting > Top/Bottom Rules > Top 10 Items. Set it to 1 item, with "Light Red Fill with Dark Red Text".
Repeat for the minimum: Bottom 10 Items, set to 1, with "Yellow Fill with Dark Yellow Text".
Result: the single highest monthly temperature in the dataset is highlighted in red (July at Windsor, Ontario), and the single lowest is highlighted in yellow (February at Alert, Nunavut).
Create a new worksheet called "Task 1".
Copy the full data table into it, then delete every row except rows 1, 2, 21, 22, and 40 (the title row, the header row, Toronto, Windsor, and Alert).
Insert a Line with Markers chart (Insert tab > Charts > Line or Area Chart > Line with Markers).
Add three data series manually (right-click the blank chart > Select Data > Add):
Series 1: Name = "Toronto, Ontario", Y values = B3:M3.
Series 2: Name = "Windsor, Ontario", Y values = the Windsor row.
Series 3: Name = "Alert, Nunavut", Y values = the Alert row.
Set the horizontal axis labels to B2:M2 (the month names).
Add chart elements: chart title ("Chart Average Daily Temperature"), axis titles ("Months" for x, "Temperature (°C)" for y), legend, and gridlines (Primary Major Horizontal and Primary Major Vertical).
Arithmetic mean (annual average)
Excel formula: =AVERAGE(B3:M3)
Equivalent longhand: (Jan + Feb + Mar + Apr + May + Jun + Jul + Aug + Sep + Oct + Nov + Dec) / 12
Celsius to Kelvin conversion
T(K) = t(°C) + 273.15
In Excel with named variables: =Annual_C + a
where Annual_C is the named range N3:N43 and a is the named cell S3 containing 273.15.
Climate normals are the baseline every meteorologist and climate scientist uses when they say a given month was "above" or "below" average. The 30-year averaging window smooths out individual hot or cold years so you get a stable reference point.
The Celsius-to-Kelvin conversion is not just an academic exercise. Kelvin is required in thermodynamic calculations (gas laws, radiation equations, entropy), so any time climate data feeds into a physics or chemistry model, this conversion is the first step.
Students often think the AVERAGE function in Excel divides by the number of cells in the range, even if some are blank. It does not. AVERAGE ignores empty cells and divides only by the count of cells that contain numbers. In this dataset every cell is populated, so the distinction does not bite you here, but it matters in messier datasets.
Students sometimes type 273 instead of 273.15 for the Celsius-to-Kelvin offset. The correct value is 273.15. Using 273 will produce answers that are off by 0.15 K, which a grader will flag.
Conditional formatting's "Top 10 Items" rule defaults to 10. You must change the number to 1 to highlight only the single maximum or minimum. Leaving it at 10 highlights ten cells, which is not what the assignment asks for.
A named range like Annual_C refers to fixed cells (N3:N43). If you later insert or delete rows above that range, the reference can shift. Always double-check named ranges in the Name Manager after structural edits to the sheet.
⚠️ The grading rubric explicitly checks that worksheets are named correctly ("daily_temperature_canada" and "Task 1"). A misspelled tab name costs marks for free.
⚠️ Formulas must use the named variables (Annual_C and a), not raw cell references, in the Kelvin conversion column. Writing =N3+273.15 instead of =Annual_C + a will lose marks even though it produces the same numbers.
⚠️ The chart must include axis titles ("Months" and "Temperature (°C)"), a chart title ("Chart Average Daily Temperature"), and a legend showing all three station names. Missing any of these elements is a common mark deduction.
⚠️ Conditional formatting colours are specified exactly: Light Red Fill with Dark Red Text for the maximum, Yellow Fill with Dark Yellow Text for the minimum. Using the wrong colour scheme will be penalised.
⚠️ The merged title cell in row 1 must span A1:M1, not A1:N1 or A1:O1. Over-merging breaks the column alignment the grader expects.
True or false: The AVERAGE function in Excel counts blank cells as zeros. (False – it ignores blank cells entirely.)
Fill in the blank: To convert 0 °C to kelvins, you add ______ . (273.15)
True or false: A named range can be used in a formula in place of a cell reference. (True)
Fill in the blank: The conditional formatting rule used to highlight the single highest value in a range is called ______ set to 1. (Top 10 Items)
True or false: The fill handle in Excel can copy a formula down a column by double-clicking it. (True)
Q: You have monthly temperature data for a weather station in cells B5:M5. Write the Excel formula to calculate the annual average temperature.
A: =AVERAGE(B5:M5)
Q: A station has an annual average temperature of -11.8 °C. What is this in kelvins?
A: T(K) = -11.8 + 273.15 = 261.35 K
Q: You have defined a named range "Annual_C" for cells N3:N43 and a named cell "a" containing 273.15. Write the formula you would enter in cell O3 to convert the annual average to kelvins.
A: =Annual_C + a
Q: What conditional formatting rule and setting do you use to highlight only the single highest temperature in a range?
A: Home > Conditional Formatting > Top/Bottom Rules > Top 10 Items, with the number changed to 1 and the format set to "Light Red Fill with Dark Red Text".
Q: In the dataset, which station recorded the highest monthly temperature and in which month? Which station recorded the lowest, and in which month?
A: The highest was recorded at Windsor, Ontario in July. The lowest was recorded at Alert, Nunavut in February.
Q: Why does the assignment require you to use named variables (Annual_C, a) in formulas rather than plain cell references?
A: Named variables make formulas self-documenting and easier to audit. When formulas become more complex, descriptive names prevent errors that come from losing track of which cell holds which parameter.
This material connects directly to any later coursework involving data visualisation or statistical summaries, since the AVERAGE function and chart-building workflow reappear throughout the course. The Celsius-to-Kelvin conversion is foundational for thermodynamics and physical chemistry (gas laws, enthalpy, entropy calculations all require absolute temperature in kelvins). Named ranges and variables in Excel are a stepping stone toward programming concepts like variable assignment, which you will encounter if the course moves into Python or MATLAB for data analysis.
Climate data analysis, Excel AVERAGE function, CSV to XLSX conversion, Celsius to Kelvin formula, named ranges Excel, Name Manager, conditional formatting Top Bottom Rules, line chart with markers, Canadian climate normals 1971-2000, Environment Canada temperature data, weather station annual average, spreadsheet data analysis, CHEM 1520, Assignment 1, York University