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?