Difficulty: Beginner | Prerequisites: Climate Data Analysis notes (Part 1)
This set of notes covers the practical Excel mechanics tested in Assignment 1. Where the first document focuses on the climate science and the data itself, this one zeroes in on the spreadsheet operations: formatting cells, writing and copying formulas, naming ranges, applying conditional formatting rules, and building charts from scratch. These are transferable skills you will reuse in every lab or assignment that involves data. If you already know how to use the fill handle and build a chart series manually, this is revision. If not, work through these notes before opening the assignment file.
The assignment tests a specific set of Excel skills: inserting rows, merging cells, writing AVERAGE formulas, using the fill handle, creating named ranges via the Name Manager, applying Top/Bottom conditional formatting rules, and constructing a multi-series line chart with proper titles, axis labels, and a legend. Each skill has a precise expected outcome the grader checks.
Cell reference
The address of a cell in a worksheet, written as a column letter followed by a row number (e.g. B3, M43). Used inside formulas to point at data.
Think of it as: the GPS coordinate for a single box in the spreadsheet grid.
Fill handle
The small square at the bottom-right corner of a selected cell. Dragging it (or double-clicking it) copies the cell's formula or pattern into adjacent cells, adjusting relative references automatically.
In simple terms, this means: the shortcut that saves you from typing the same formula 40 times.
Merge & Centre
An Excel formatting command (Home tab) that combines several cells into one and centres the text horizontally. Commonly used for table titles that span multiple columns.
Think of it as: knocking down the walls between cells to make one wide cell.
Name Manager
A dialog under the Formulas tab where you create, edit, and delete named ranges and named constants. Each entry maps a descriptive label to a cell address or range.
In simple terms, this means: the place where you tell Excel that "Annual_C" means cells N3 through N43.
Chart series
A single set of related data points plotted on a chart. In a line chart, each series is one line. You add a series by specifying a name (for the legend) and the cell range containing its Y values.
Think of it as: one line on the graph, with its own colour and legend label.
Axis labels / Axis title
Axis labels are the tick-mark text along an axis (e.g. month names on the x-axis). An axis title is the descriptive heading for the whole axis (e.g. "Temperature (°C)"). Both are set through Chart Elements or the Select Data dialog.
Think of it as: the ruler markings (labels) and what the ruler measures (title).
Chart Elements
The green "+" icon that appears beside a selected chart in Excel. It lets you toggle chart title, axis titles, legend, gridlines, data labels, and other visual components on or off.
In simple terms, this means: the one-click menu for everything that is not the data itself on your chart.
Inserting a row: Select the entire row by clicking its number on the left margin, then right-click > Insert (or Home tab > Insert). The new row appears above the selected row.
Merge & Centre: Select a multi-cell range (e.g. A1:M1), then Home tab > Merge & Centre. The content of the leftmost cell fills the merged cell. Set font size, bold, and alignment separately after merging.
Column width: If cells show "########", the column is too narrow. Drag the right edge of the column header to widen it, or double-click the edge to auto-fit.
Worksheet tabs: Click the "+" icon at the bottom of the workbook to create a new sheet. Double-click a tab name to rename it.
The AVERAGE function: Type =AVERAGE(B3:M3) in the target cell. The range inside the parentheses is the set of cells to average.
Relative references: When you copy a formula down using the fill handle, Excel adjusts row numbers automatically. =AVERAGE(B3:M3) in row 3 becomes =AVERAGE(B4:M4) in row 4, and so on.
Fill handle shortcut: Select the cell with the formula, then double-click the small cross at the bottom-right corner. Excel fills down to the last row of adjacent data. This only works when the column next to yours has data in every row.
Creating a named range: Select the cells (e.g. N3:N43), go to Formulas tab > Name Manager > New, type the name (e.g. "Annual_C"), and press OK.
Creating a named constant: Select a single cell (e.g. S3 containing 273.15), open Name Manager > New, type the name (e.g. "a"), and press OK.
Using named variables in formulas: Type =Annual_C + a in cell O3. Excel recognises both names and applies the formula. Because Annual_C spans multiple rows, the result fills automatically.
Why it matters: Named variables make formulas readable. =Annual_C + a is immediately clear; a raw cell reference like =N3+S3 (with an absolute reference) requires you to look up what S3 contains.
Top N Items: Select the data range (B3:M43), then Home > Conditional Formatting > Top/Bottom Rules > Top 10 Items. Change the number from 10 to 1. Choose "Light Red Fill with Dark Red Text".
Bottom N Items: Same range, same menu path, but choose Bottom 10 Items. Change to 1. Choose "Yellow Fill with Dark Yellow Text".
What it reveals: The single highest monthly temperature (red) and the single lowest (yellow) jump out visually from a table of over 500 values.
Step 1, Insert a blank chart: Insert tab > Charts > Insert Line or Area Chart > Line with Markers.
Step 2, Add the first series: Right-click the blank chart > Select Data > Add. In "Series name" type the station name (e.g. "Toronto, Ontario"). In "Series values" select the monthly temperature cells for that station (B3:M3).
Step 3, Set the x-axis labels: In the Select Data dialog, click Edit under "Horizontal (Category) Axis Labels". Select the row of month names (B2:M2).
Step 4, Add more series: Click Add again for each additional station (Windsor, Ontario and Alert, Nunavut), repeating the process.
Step 5, Add chart elements: Click the green "+" icon beside the chart and enable: Chart Title, Axis Titles, Legend, Gridlines (Primary Major Horizontal and Primary Major Vertical).
Step 6, Title the chart: "Chart Average Daily Temperature". X-axis title: "Months". Y-axis title: "Temperature (°C)".
Select the cell or range you want to name.
Go to the Formulas tab on the ribbon.
Click Name Manager.
Click New.
In the Name field, type a descriptive label (no spaces; use underscores).
Verify the "Refers to" field shows the correct range.
Click OK.
Select the data range (do not include headers).
Go to Home tab > Conditional Formatting > Top/Bottom Rules.
Choose Top 10 Items (for the maximum) or Bottom 10 Items (for the minimum).
In the dialog, change the number to 1.
Choose the colour format from the dropdown.
Click OK.
Right-click inside the chart area.
Click Select Data.
Under "Legend Entries (Series)", click Add.
In "Series name", type the label for the legend.
In "Series values", click the range-select icon and highlight the data cells.
Click OK.
Repeat for each additional series.
Every field that collects quantitative data, from biology lab results to financial modelling, relies on exactly these Excel operations. Named ranges are standard practice in any spreadsheet that someone other than the author will read or audit. Conditional formatting is how analysts in consulting, finance, and operations flag outliers in large datasets without writing code. Line charts with multiple series are the default way to compare trends across categories in reports, dashboards, and publications.
Students often think double-clicking the fill handle copies the formula to every row in the spreadsheet. It does not. It copies only as far as the adjacent column has data. If there is a gap in the adjacent column, it stops there.
Students sometimes add the chart data series by selecting a range before inserting the chart, expecting Excel to figure out the layout. For this assignment you must add each series manually through the Select Data dialog so that the series names and axis labels match exactly what the grader expects.
Merging cells is not the same as Centre Across Selection. Merge & Centre physically combines cells into one; Centre Across Selection keeps cells separate but centres text visually. The assignment specifically requires Merge & Centre.
Students frequently forget to set the horizontal axis labels to the month names. Without this step, the x-axis defaults to sequential numbers (1, 2, 3...) instead of January, February, March, and so on.
⚠️ The grading rubric checks that named ranges are used in formulas. If you write =N3+273.15 in column O instead of =Annual_C + a, you will lose marks even though the output is numerically identical.
⚠️ Both worksheet tabs must be named exactly as instructed: "daily_temperature_canada" (the original) and "Task 1" (the new chart sheet). Spelling and capitalisation matter.
⚠️ The chart must be a Line with Markers, not a plain line, not a bar chart, not a scatter plot. The chart type is specified in the instructions.
⚠️ Chart elements checked by the grader: chart title ("Chart Average Daily Temperature"), x-axis title ("Months"), y-axis title ("Temperature (°C)"), a legend with all three station names, and gridlines matching Figure 11 in the assignment handout.
⚠️ Conditional formatting must highlight exactly 1 maximum and 1 minimum across the entire monthly data range, not 10. The default "Top 10 Items" rule must be changed to 1.
True or false: The fill handle adjusts relative cell references when copying a formula down a column. (True)
Fill in the blank: To merge cells A1 through M1 and centre their content, you use the ______ button on the Home tab. (Merge & Centre)
True or false: A named range created in the Name Manager can be used in any formula within the same workbook. (True)
Fill in the blank: To add a data series to an existing chart, right-click the chart, choose ______, then click Add. (Select Data)
True or false: The "Top 10 Items" conditional formatting rule can only highlight exactly 10 items. (False, you can change the number to any value from 1 to 1000.)
Q: You need to calculate the average of cells C5 through N5. Write the Excel formula.
A: =AVERAGE(C5:N5)
Q: After entering a formula in cell N3, how do you quickly copy it to cells N4 through N43 without dragging?
A: Select cell N3, then double-click the fill handle (the small square at the bottom-right corner of the cell). Excel copies the formula down to the last row with adjacent data.
Q: You want to name the cell range D2:D50 as "Pressure_kPa". Describe the steps.
A: Select D2:D50. Go to Formulas tab > Name Manager > New. Type "Pressure_kPa" in the Name field. Confirm the "Refers to" field shows the correct range. Click OK.
Q: You have a line chart with one series. How do you add a second series for a different dataset?
A: Right-click the chart > Select Data > Add. Type the series name, then select the cell range for the new series' Y values. Click OK.
Q: Your conditional formatting highlights 10 cells in red, but the assignment asks for only the single highest value. What went wrong and how do you fix it?
A: The "Top 10 Items" rule defaults to 10. Edit the rule (Home > Conditional Formatting > Manage Rules > Edit Rule) and change the number from 10 to 1.
Q: What is the difference between axis labels and an axis title on a chart?
A: Axis labels are the individual tick-mark values along the axis (e.g. January, February, March). The axis title is the heading that describes what the entire axis represents (e.g. "Months" or "Temperature (°C)").
The Excel skills in this assignment carry forward to any course that uses spreadsheets for lab data, statistical analysis, or modelling. Named ranges are the spreadsheet equivalent of variable names in programming, so this concept maps directly onto Python, MATLAB, or R if your course introduces those later. Conditional formatting is a basic form of data visualisation and connects to more advanced techniques like heat maps and dashboards. Chart-building in Excel is the foundation for any data presentation or scientific poster you produce.
Excel AVERAGE function, fill handle Excel, Merge and Centre, Name Manager Excel, named range formula, conditional formatting Top Bottom Rules, line chart with markers Excel, chart series Select Data, axis labels Excel, chart elements, CHEM 1520, Assignment 1, York University, spreadsheet data analysis, CSV to XLSX, climate data Excel