csis1/16wk-week-07-09-spreadsheets.md

Weeks 7–9: Spreadsheets (Microsoft Excel)

Hours: 6 (3 lectures)

---

Learning Objectives

• Navigate the Excel interface and understand workbooks, worksheets, cells, rows, and columns
• Enter and edit data (text, numbers, dates)
• Write formulas using arithmetic operators and cell references
• Use common functions: SUM, AVERAGE, MIN, MAX, COUNT, IF
• Format cells, rows, and columns for readability
• Create and format basic charts
• Use sorting, filtering, and conditional formatting
• Print spreadsheets with proper page setup

---

Lecture 7: Creating a Worksheet

Key Concepts

The Excel Interface

• Workbook: The entire file (.xlsx). Contains one or more worksheets.
• Worksheet (Sheet): A single tab/page within a workbook. Grid of rows and columns.
• Cell: The intersection of a row and column (e.g., B3). The fundamental unit.
• Cell Reference: The address of a cell (column letter + row number).
• Name Box: Shows the active cell reference. Can also be used to navigate.
• Formula Bar: Shows the content or formula in the active cell.

💡 Analogy: A workbook is like a binder. Each worksheet is a page in the binder. Each cell is a box on that page where you write one piece of information.

Entering Data

• Text (Labels): Category names, headers — left-aligned by default
• Numbers (Values): Quantities, amounts — right-aligned by default
• Dates: Excel stores dates as numbers internally (important for calculations)
• Press Enter to confirm and move down. Tab to confirm and move right.
• AutoFill: Drag the fill handle (small square at bottom-right of cell) to extend patterns (months, days, number sequences).

Cell References in Formulas

• Type = to start a formula
• Arithmetic operators: + - * / ^ (exponent)
• Example: =B2+B3 adds the values in B2 and B3
• Why use cell references instead of numbers? Because when the data changes, the formula automatically recalculates. This is the entire point of spreadsheets.

💡 Demo: Type =5+3 in a cell. It shows 8. Now type =A1+A2 where A1=5 and A2=3. Same result, but change A1 to 10 — the formula updates automatically. "This is why spreadsheets changed the world."

Order of Operations (PEMDAS) Excel follows standard math order:

1. Parentheses
2. Exponents
3. Multiplication / Division (left to right)
4. Addition / Subtraction (left to right)

Example: =2+3*4 = 14 (not 20). Use parentheses to override: =(2+3)*4 = 20.

Common Functions

Function	What It Does	Example
=SUM(B2:B10)	Adds all values in a range	Total sales
=AVERAGE(B2:B10)	Calculates the mean	Average grade
=MAX(B2:B10)	Returns the largest value	Highest score
=MIN(B2:B10)	Returns the smallest value	Lowest temperature
=COUNT(B2:B10)	Counts cells containing numbers	How many entries
=COUNTA(B2:B10)	Counts non-empty cells	How many responses

💡 Teaching idea: Build a gradebook together in class. Enter student names, assignment scores. Calculate total, average, highest, lowest. Students follow along step by step.

Diagram Ideas

1. Excel Interface Labeled — Screenshot with callouts: Name Box, Formula Bar, Column Headers, Row Numbers, Sheet Tabs, Active Cell.
2. Cell Reference Anatomy — Show that B3 = Column B, Row 3. Highlight the intersection.
3. AutoFill Magic — Before/after showing Jan, Feb → drag → Mar, Apr, May...
4. Formula vs Value — Split view showing what's displayed in the cell (result) vs what's in the Formula Bar (the formula).

Slide Concepts

Slide	Content
1	Title: "Spreadsheets: Where Data Comes Alive"
2	The Excel Interface — labeled screenshot
3	Workbook → Worksheet → Cell hierarchy
4	Entering Data — text, numbers, dates, AutoFill
5	Your First Formula — =A1+A2, why cell references matter
6	Order of Operations — PEMDAS in Excel
7	Essential Functions — SUM, AVERAGE, MAX, MIN, COUNT
8	Live Build: Class Gradebook

---

Lecture 8: Formatting a Worksheet

Key Concepts

Cell Formatting

• Number Formats: General, Number (decimal places), Currency ($), Percentage (%), Date, Text
  • Formatting changes how a number LOOKS, not its actual value
• Font: Same options as Word — family, size, bold, italic, color
• Alignment: Horizontal (left, center, right) and Vertical (top, middle, bottom)
• Merge & Center: Combine cells for titles. Use sparingly — can cause issues with sorting/formulas.
• Wrap Text: Makes all content visible by expanding row height
• Borders: Add lines around cells for visual structure
• Fill Color: Background color for cells (use for headers, totals, emphasis)

💡 Design principle: Format for readability. Bold headers, currency format for money, right-align numbers, consistent decimal places. A well-formatted spreadsheet tells its story at a glance.

Column & Row Operations

• Resize: Drag borders or double-click to auto-fit
• Insert/Delete rows and columns
• Hide/Unhide (for temporary simplification)
• Freeze Panes: Keep headers visible while scrolling (View → Freeze Panes)

Conditional Formatting

• Automatically format cells based on their values
• Examples:
  • Highlight cells > 90 in green (A grades)
  • Color scale from red (low) to green (high)
  • Data bars showing relative magnitude
  • Icon sets (arrows, traffic lights)

💡 Demo: Apply conditional formatting to the gradebook. Scores above 90 → green. Below 60 → red. Instantly visual.

Cell References: Relative vs Absolute

• Relative (default): =A1 — adjusts when copied (A1 becomes A2, A3, etc.)
• Absolute: =$A$1 — stays fixed when copied. The $ locks the reference.
• Mixed: =$A1 (column locked) or =A$1 (row locked)
• When do you need absolute? When one cell is a constant used by many formulas (tax rate, conversion factor, etc.)

💡 Demo: Create a price list with a tax rate in one cell. Write a formula to calculate tax. Copy it down — it breaks because the tax rate reference shifts. Fix it with $. Lightbulb moment.

Diagram Ideas

1. Number Format Examples — Same number (1234.5) shown as General, Number, Currency, Percentage, Date.
2. Relative vs Absolute — Show a formula being copied down: relative references shift, absolute stays fixed. Color-code which parts move.
3. Conditional Formatting Gallery — Examples of highlight rules, color scales, data bars, icon sets applied to sample data.
4. Freeze Panes — Before (headers scroll away) vs After (headers stay visible).

Slide Concepts

Slide	Content
1	Title: "Making Data Readable"
2	Number Formats — same value, different appearances
3	Cell Formatting — font, alignment, borders, fill
4	Merge & Center (and why not to overuse it)
5	Column/Row Operations — resize, insert, freeze panes
6	Conditional Formatting — highlight rules, color scales
7	Relative vs Absolute References — the $ explained
8	Demo: Tax Calculator with absolute references

---

Lecture 9: Finalizing a Worksheet — Charts & Output

Key Concepts

The IF Function

• Logical test that returns one value if true, another if false
• Syntax: =IF(condition, value_if_true, value_if_false)
• Example: =IF(B2>=60, "Pass", "Fail")
• Can be nested but keep it simple at this level

💡 Teaching idea: Add a Pass/Fail column to the gradebook using IF. Then try letter grades with nested IF (or introduce IFS if using newer Excel).

Charts Charts turn numbers into visual stories. Four main types for this course:

Chart Type	Best For	Example
Column/Bar	Comparing categories	Sales by product, scores by student
Line	Showing trends over time	Temperature over a week, stock price
Pie	Showing parts of a whole	Budget breakdown, market share
Scatter (XY)	Showing correlation between two variables	Study hours vs grade

Creating a Chart

1. Select the data (including headers)
2. Insert → Chart type
3. Chart appears on the worksheet

Chart Elements

• Title: Descriptive (not just "Chart 1")
• Axes: X-axis (horizontal, categories) and Y-axis (vertical, values)
• Legend: Identifies data series by color
• Data Labels: Show exact values on chart elements
• Gridlines: Help read values from the axes

💡 Design tip: Every chart should answer a question. "What sold the most?" → Bar chart. "Is it trending up?" → Line chart. "What's the biggest piece?" → Pie chart. Start with the question, then choose the chart.

Sorting & Filtering

• Sort: Rearrange rows by a column's values (A→Z, Z→A, smallest→largest)
• Filter: Temporarily hide rows that don't match criteria (dropdown arrows on headers)
• AutoFilter: Home → Sort & Filter → Filter. Adds dropdowns to each column header.

Printing a Spreadsheet

• Page Layout tab: Margins, Orientation, Size, Print Area
• Scale to Fit: shrink wide spreadsheets to fit on one page
• Print Titles: Repeat header row on every printed page
• Page Break Preview: See exactly where pages split
• Headers & Footers for printed pages

Diagram Ideas

1. Chart Type Decision Tree — "What do you want to show?" → Comparison? → Bar/Column. Trend? → Line. Composition? → Pie. Relationship? → Scatter.
2. Chart Anatomy — Labeled chart showing title, axes, legend, data labels, gridlines.
3. Sort vs Filter — Before/after examples. Sort rearranges. Filter hides.
4. Print Setup Checklist — Visual guide: set print area → choose orientation → add print titles → preview.

Slide Concepts

Slide	Content
1	Title: "Telling Stories with Data"
2	The IF Function — syntax and gradebook example
3	Chart Types — when to use each (decision tree)
4	Creating a Chart — step-by-step
5	Chart Anatomy — labeled example
6	Chart Design Tips — title, labels, clean layout
7	Sorting & Filtering — demo
8	Printing — Page Layout, Print Titles, Preview

---

Vocabulary

Term	Definition
Workbook	An Excel file (.xlsx) containing one or more worksheets
Worksheet / Sheet	A single page/tab within a workbook; a grid of cells
Cell	The intersection of a row and column; the basic unit of a spreadsheet
Cell Reference	The address of a cell using its column letter and row number (e.g., B3)
Range	A group of cells identified by the reference of the upper-left and lower-right cells (e.g., A1:C10)
Formula	An expression that calculates a value, starting with =
Function	A predefined formula (e.g., SUM, AVERAGE, IF)
AutoFill	Feature that extends a pattern or series by dragging the fill handle
Fill Handle	The small square at the bottom-right corner of the selected cell
Relative Reference	A cell reference that adjusts when a formula is copied (default behavior)
Absolute Reference	A cell reference that stays fixed when copied, using $ (e.g., $A$1)
SUM	Function that adds all values in a range
AVERAGE	Function that calculates the arithmetic mean of a range
MAX / MIN	Functions that return the largest / smallest value in a range
COUNT	Function that counts the number of cells containing numbers
IF	Logical function that tests a condition and returns different values for true/false
Conditional Formatting	Rules that automatically change cell appearance based on cell values
Chart / Graph	Visual representation of data
Axis	The horizontal (X) or vertical (Y) reference line on a chart
Legend	A key that identifies the data series in a chart
Sort	Rearranging data in order (alphabetical, numerical, by date)
Filter	Temporarily hiding rows that don't meet specified criteria
Freeze Panes	Keeping rows or columns visible while scrolling through the rest of the sheet
Print Area	The specific range of cells designated to be printed
Number Format	The display style of a number (Currency, Percentage, Date, etc.)

---

Activities & Assignments

In-Class

1. Gradebook Build-Along: Instructor builds a gradebook from scratch; students follow. Enter names, scores, calculate totals, averages, IF for pass/fail. Format it. Add a chart.
2. Formula Challenge: Worksheet with 15 progressively harder formula/function problems. Start with =A1+A2, end with nested IF.
3. Chart Makeover: Give students an ugly, misleading chart. They identify what's wrong and recreate it properly.

Homework / Projects

1. Personal Budget Spreadsheet: Track one month of income and expenses. Categories in column A, amounts in B. Use SUM for totals, pie chart for expense breakdown, conditional formatting for overspending. Format as currency.
2. Data Analysis Exercise: Provide a dataset (e.g., class survey results, weather data, fictional sales). Students must: sort and filter, calculate summary statistics (SUM, AVERAGE, MAX, MIN), create 2 charts, write a paragraph interpreting what the data shows.
3. IF Function Practice: Worksheet of scenarios requiring IF formulas (grading scale, shipping cost tiers, discount eligibility).

---

Discussion Questions

1. Why are spreadsheets one of the most-used tools in business? What makes them so versatile?
2. What's the danger of formatting a number as Currency vs actually being Currency? (Hint: display vs value)
3. When would a pie chart be misleading? (Too many slices, values don't add to 100%, 3D distortion)
4. You're handed a spreadsheet with 10,000 rows. What's the first thing you'd do to make sense of it?