Spreadsheets That Do the Work: From Formulas to Decisions

By the end

What you'll build

• Write formulas that refer to cells instead of typed-in numbers, so a sheet corrects itself when the data changes
• Predict what any formula returns by applying the order of operations, and use brackets to force the order you meant
• Lock a reference with a dollar sign so a formula survives being copied down a column
• Summarise a column with SUM, AVERAGE, COUNT, COUNTA, MIN and MAX, and say which one a given question needs
• Round money correctly with ROUND, and explain the missing-paisa problem that rounding creates
• Build a decision inside a cell with IF, AND and OR, and recognise when nested IFs have grown too deep to trust
• Total and count only the rows that meet one or several conditions using SUMIF, COUNTIF, SUMIFS and COUNTIFS
• Fetch a value from another table with a column lookup, then rewrite it as index-and-match to remove the fragility
• Clean imported text with TRIM, LEFT, MID and FIND, and calculate with dates by treating them as numbers
• Audit a sheet you did not write, find the broken range, and hand over a documented budget-and-variance tracker

Curriculum

What's inside

7 modules · 48 lessons

1. 01

   Getting the Grid Right

   8 lessons
   • Start Here: What This Course Is, and What It Is Not
   • The Grid, and How to Point at a Box
   • Lab 1: Your First Formulaspreadsheet lab
   • Arithmetic, and the Order Operations Happen In
   • Lab 2: Brackets Change the Answerspreadsheet lab
   • + 3 more lessons
2. 02

   The Everyday Functions

   7 lessons
   • What a Function Is: SUM and AVERAGE
   • Lab 4: Total and Average the Weekspreadsheet lab
   • COUNT, COUNTA, MIN and MAX
   • Lab 5: Count What Is Really Therespreadsheet lab
   • ROUND, and Why Money Needs It
   • + 2 more lessons
3. 03

   Decisions Inside a Cell

   6 lessons
   • IF: the Shape of a Decision
   • Lab 7: Flag the Over-Budget Rowsspreadsheet lab
   • Nested IF, and Why Deep Nesting Is a Smell
   • Lab 8: Three Bands with a Nested IFspreadsheet lab
   • AND and OR: Asking Two Questions at Once
   • + 1 more lesson
4. 04

   Counting and Totalling with Conditions

   6 lessons
   • COUNTIF and SUMIF: Answering a Question About Some of the Rows
   • Lab 10: SUMIF for One Branchspreadsheet lab
   • COUNTIFS and SUMIFS: More Than One Condition
   • Lab 11: SUMIFS with Two Conditionsspreadsheet lab
   • Lab 12: COUNTIFS with a Number Testspreadsheet lab
   • + 1 more lesson
5. 05

   Lookups, and Data That Arrives Dirty

   8 lessons
   • The Classic Column Lookup, and Where It Breaks
   • Lab 13: A Column Lookup That Worksspreadsheet lab
   • Index and Match: the Safer Lookup Pattern
   • Lab 14: Rewrite It with Index and Matchspreadsheet lab
   • Cleaning Imported Text: TRIM, LEFT, RIGHT, MID and FIND
   • + 3 more lessons
6. 06

   Dates, Growth and Summaries

   6 lessons
   • Dates Are Numbers
   • Lab 17: Days Between Two Datesspreadsheet lab
   • Percentage Change, and Talking About It Honestly
   • Lab 18: Month-on-Month Percentage Changespreadsheet lab
   • Making a Big Table Readable
   • + 1 more lesson
7. 07

   Sheets Other People Can Trust, and Your Capstone

   7 lessons
   • Stop Bad Input at the Door: Validation and Named Ranges
   • Lab 19: Let a Sheet Fail Politely with IFERRORspreadsheet lab
   • Auditing a Sheet You Inherited
   • Lab 20: Find the Error in an Inherited Sheetspreadsheet lab
   • Protect It, Document It, and Know When to Stop
   • + 2 more lessons

The shape of it

How this course works