SStretchLearn
Sign inMembershipStart learning
Catalog / Tech & AI / Excel & Google Sheets Mastery
Tech & AIBeginnerPreview

Excel & Google Sheets Mastery

A practical, side-by-side path through Excel and Google Sheets that takes you from a raw export to a dashboard a manager can act on. You will structure data so formulas work, master the lookup and aggregation functions that do real work, summarize thousands of rows with pivot tables, build charts that tell the truth, and assemble an interactive dashboard, learning the exact equivalents and differences between the two tools at every step.

For anyone who uses spreadsheets for work or life but has never moved past basic SUM and formatting into lookups, pivots, and dashboards.

Course content

Why structure beats formulas, and how to set up a real table45m
Cleaning messy data without losing your mind50m
References, errors, and the F4 key that saves your formulas45m
Logic and conditional aggregation: IF, IFS, SUMIFS, COUNTIFS55m
Lookups done right: XLOOKUP and INDEX/MATCH55m
Google Sheets superpowers: QUERY, FILTER, ARRAYFORMULA, UNIQUE50m
Pivot table fundamentals in both tools50m
Grouping, calculated fields, and showing values as50m
Slicers, filters, and interactive exploration45m

Workbook & downloads

Put the course into practice — a printable workbook plus editable templates you can fill in and reuse.

Download workbook (PDF)19 KBDownload (XLSX)9 KBDownload (XLSX)8 KBDownload (DOCX)8 KB
Preview the workbook
This workbook turns the course into the audits, drills, and reusable files you need to go from a messy export to a working dashboard. Work each section against your own data: clean and structure a real table, drill the formulas that matter (XLOOKUP, SUMIFS, INDEX/MATCH, QUERY) until they are reflexes, build and slice pivot tables, then assemble an interactive dashboard. The templates are ready-to-fill spreadsheets you can drop your data into and reuse as the working files you take into Excel and Google Sheets.

Getting Your Data Right

Audit and clean a real dataset into a proper table, then prove your references copy correctly before any analysis.
Checklist: Clean-Table Readiness Check
  • There is exactly one header row, with short unique text labels and no merged cells
  • Every cell holds a single value (Red, Large is split into a Color column and a Size column)
  • There are no blank rows or columns inside the data block
  • Each column holds one consistent type (a Date column has only real dates, a number column only numbers)
  • Totals, notes, and summaries live outside the raw data range, not inside it
  • Text was trimmed with TRIM, and stray N.Y. / N.Y / New York values were standardized
  • Numbers stored as text were converted to real numbers (VALUE or multiply by 1) so they sum
  • Duplicates were found, defined, and removed against a backup copy of the raw data
Worksheet: Data Cleaning Log
Before fixing anything, profile your real dataset column by column. List every problem you find and the exact tool or formula you will use, then do the fix in a helper column and paste back as values. This becomes your audit trail.
  • Column name
  • Problem found (split needed / stray spaces / text-as-number / inconsistent text / duplicates / blanks)
  • Example bad value
  • Fix tool or formula (Text to Columns / Flash Fill / TRIM / VALUE / SUBSTITUTE / Remove Duplicates / QUERY)
  • Helper-column formula used
  • Verified result (yes / no)
  • Pasted back as values? (yes / no)
Exercise: References Drill: Lock the Right Cells
Build a tiny model that forces you to use each reference mode, then copy it and confirm it still works. Use F4 to stamp the dollar signs rather than typing them. Do this in both Excel and Google Sheets if you can.
  1. Put a commission rate in F1, then in a column write =B2*$F$1 down ten rows. After copying, does B shift to B3, B4 while $F$1 stays pinned? Confirm the dollar signs are right.
  2. Build a small multiplication grid using mixed references ($A2 down the side, A$1 across the top) so one formula fills the whole grid. Which dollar sign locks which part, and why?
  3. Deliberately delete a column your formula references to trigger #REF!, then fix it. What does this teach you about building formulas that survive edits?
  4. Replace the cell-address version with an Excel Table structured reference or a Sheets named range. How does naming the column remove the copy-paste reference problem entirely?

The Formulas That Matter

Drill conditional aggregation, reliable lookups, and the Google Sheets QUERY/FILTER family until you can write them from memory.
Exercise: SUMIFS / COUNTIFS Criteria Drill
Using a sales table with Region, Quarter, Product, Qty, and Revenue columns, write the formulas to answer each question. Point at least one formula at a cell you can change, so the answer updates live.
  1. Total revenue for the West region in Q3: write the SUMIFS with both criteria. What is the argument order (sum_range first, then pairs)?
  2. Count of orders over 1000: write a COUNTIFS using a comparison criterion like ">1000". How do you put the operator and value together?
  3. Average order value for one product whose name is typed in cell H1: write =AVERAGEIFS(...) referencing H1 so changing H1 re-answers it.
  4. Revenue on or after 1 Jan 2026: build a date criterion with ">=" & DATE(2026,1,1). Why must the operator be joined to the date this way?
Worksheet: Lookup Build Sheet (XLOOKUP and INDEX/MATCH)
Plan a real lookup from one table into another (for example, pull a Price from a Products table into an Orders table). Fill the row, then write both versions and confirm they return the same value.
  • What am I looking up (lookup_value cell, e.g., the product code in A2)
  • Search column (lookup_array, e.g., Products[Code])
  • Return column (return_array, e.g., Products[Price])
  • XLOOKUP formula (with an if_not_found message)
  • INDEX/MATCH equivalent (=INDEX(return, MATCH(value, search, 0)))
  • Both return the same value? (yes / no)
  • Key-mismatch risks checked (trailing spaces / text-vs-number / capitalization)
Exercise: Google Sheets QUERY Challenge
On a copy of your data in Google Sheets, reproduce a grouped summary with a single QUERY formula instead of a pivot table. Build the clauses one at a time so you can see each add a piece of the result.
  1. Write =QUERY(range, "select B, sum(E) group by B", 1) to total revenue per product. What does the trailing 1 mean?
  2. Add a where clause to keep only the West region, then an order by sum(E) desc to sort biggest first. Read the final string in plain English.
  3. Use FILTER to return every column for rows where Revenue > 500 and Region = "West". How is this different from QUERY, and when would you pick each?
  4. Use UNIQUE (and SORT) to build a clean, sorted list of regions for a dropdown, then ARRAYFORMULA to compute a whole line-total column from one cell. Why does this keep the sheet lighter than copying a formula down?
Checklist: Formula Fluency Check
  • I can write IF and replace nested IFs with a readable IFS
  • I can write SUMIFS, COUNTIFS, and AVERAGEIFS with multiple criteria and a cell-driven criterion
  • I use XLOOKUP as my default lookup and can read/write INDEX/MATCH for older files
  • I clean both key columns before a lookup so #N/A means a real mismatch, not a stray space
  • I wrap formulas in IFERROR only where I understand why an error can legitimately occur
  • In Google Sheets I can write a QUERY with select, where, group by, and order by
  • I can use FILTER, UNIQUE, and ARRAYFORMULA to reshape data without helper columns

Pivot Tables

Build, group, calculate, and slice pivot tables in both tools, and catch the pitfalls that make totals wrong.
Exercise: Build Your First Pivot, Four Zones
On your clean table, build a revenue-by-region-by-month pivot, then rearrange it to prove you understand the model. Do it in Excel and in Google Sheets if possible and note any differences.
  1. Put Region in Rows, Month in Columns, and Revenue in Values. Does Values read Sum of Revenue or Count? If Count, what in the source data caused it and how do you fix it?
  2. Swap Region and Month between Rows and Columns. What does this teach you about the pivot in pivot table?
  3. Add Year to a Filter or Slicer and limit to one year. How does this change which rows feed the whole pivot?
  4. Add or edit a few source rows, then refresh. Did the pivot update, and is its source range built on a Table or full columns so new rows are included?
Worksheet: Pivot Analysis Planner
Plan a pivot that answers a real question before you drag a single field. One row per pivot you intend to build. This keeps you building reports on purpose instead of poking at fields.
  • Business question (e.g., which products grew fastest in the West?)
  • Rows field
  • Columns field
  • Values field + aggregation (Sum / Count / Average / Max / Min)
  • Date grouping needed (Month / Quarter / Year / none)
  • Calculated field needed (e.g., Profit = Revenue - Cost; Margin = Profit / Revenue)
  • Show Values As view (% of grand total / % of column / running total / % difference)
  • Slicers / filters to add
Exercise: Grouping, Calculated Fields, and Slicers
Push one pivot beyond simple sums so it actually analyzes. Use the pivot's own features rather than formulas outside it.
  1. Group a date field into Months, Quarters, and Years (Excel: right-click, Group; Sheets: Create pivot date group). What changed about how readable the pivot is?
  2. Add a calculated field for Margin = Profit / Revenue. Does it compute on the summed values (total profit / total revenue) rather than averaging per-row margins, and why does that matter when a few big orders dominate?
  3. Apply Show Values As Percent of Grand Total, then a Running Total. What business question does each view answer that the raw sum did not?
  4. Add a Region slicer and, in Excel, use Report Connections to make one slicer drive a second pivot too. What is required for one slicer to control multiple pivots?
Checklist: Pivot Pitfall Check
  • The pivot reads Sum, not Count, because the value column has no stray text
  • Date fields are real dates so they group, not text that refuses to group
  • The source is a Table or full columns, and I refresh after editing data
  • Ratios live in calculated fields or Show Values As, not in math outside the pivot that breaks on rearrange
  • Active slicer or filter settings are cleared or clearly labeled so later numbers are not misread
  • I can rearrange fields fluidly to explore, then lock a clean view to present

Charts and the Interactive Dashboard

Choose honest charts, surface key numbers with conditional formatting and KPI tiles, then assemble a connected dashboard.
Worksheet: Chart Selection Planner
For each chart you plan, name the relationship first, then pick the type that shows it most clearly. Fill one row per chart. This stops you from defaulting to whatever the tool suggests.
  • Question / relationship (comparison / trend over time / part of a whole / relationship between two numbers / distribution)
  • Chart type chosen (column or bar / line / stacked bar or pie / scatter / histogram)
  • Data source (which pivot or QUERY summary feeds it)
  • Axis baseline (bar charts start at zero? yes / no)
  • Data labels needed? (exact values matter vs shape matters)
  • Title that states the takeaway
  • Misleading-chart risks avoided (truncated axis / dual axis / chart junk / too many series / pie overload)
Exercise: Conditional Formatting and KPI Tiles
Make the important numbers find the reader. Build a few targeted rules and a row of KPI tiles, using restraint so the page does not become a rainbow.
  1. Add a formula-based rule to highlight every row where revenue missed target, e.g. =$G2<$H2. Why lock the column with the dollar sign, and how does this format a whole row from one cell's logic?
  2. Apply a three-color scale to a region-by-month matrix to create a heatmap. What does color reveal here that raw numbers do not?
  3. Build KPI tiles for Total Revenue, Orders, Average Order Value, and Revenue vs Target using SUMIFS / COUNTIFS, formatted large with a green/red conditional color against target.
  4. Add a sparkline to each tile (=SPARKLINE(range) in Sheets, or Insert Sparklines in Excel). What does showing the number and its recent trend together give a busy reader?
Worksheet: Dashboard Architecture Plan
Plan the three-layer build before you place a single tile, so raw data, calculations, and presentation stay separate and the dashboard stays maintainable.
  • Data layer (which sheet holds the clean raw table; Excel Table or named Sheets range name)
  • Calculation layer (which sheet holds pivots, QUERY results, lookups, SUMIFS)
  • Presentation layer (the dashboard sheet: KPI tiles + which charts)
  • Named ranges defined (e.g., SalesData, SalesRevenue, SalesRegion, SelectedRegion)
  • Slicers and which pivots/charts each one drives (Excel Report Connections)
  • Refresh model (Sheets auto vs Excel manual Refresh) and who is told
  • Protection plan (protect sheets/ranges so viewers click but cannot break formulas)
Checklist: Dashboard Done Check
  • Data, calculation, and presentation live on separate sheets
  • Tile and chart formulas use named ranges, not opaque A2:F9999 addresses
  • Charts read a summary (pivot/QUERY output), never ten thousand raw rows, and bars start at zero
  • One row of KPI tiles with conditional color and sparklines sits top-left
  • Slicers are wired (one slicer drives the relevant pivots and charts) and labeled
  • Working sheets or gridlines are hidden and the layout is protected
  • A decision-maker can learn what to do at a glance and click a slicer to ask why, never seeing a formula

Your Action Plan

  1. Make a backup copy of your raw data, then clean it into one proper table: single header row, atomic cells, consistent types, trimmed text, real numbers, no duplicates.
  2. Formalize the table as an Excel Table (Ctrl+T) or a named Google Sheets range, and freeze the header row.
  3. Drill references until F4 and absolute/relative are automatic, and confirm a copied formula keeps its locked cells.
  4. Build a SUMIFS/COUNTIFS summary block pointed at a cell you can change, the seed of your dashboard KPIs.
  5. Write your lookups with XLOOKUP (and be able to read INDEX/MATCH), cleaning both key columns first.
  6. In Google Sheets, reproduce a grouped summary with a single QUERY, and try FILTER, UNIQUE, and ARRAYFORMULA.
  7. Build a pivot table (Region x Month x Revenue), group the dates, add a Margin calculated field, and apply a Show Values As view.
  8. Add slicers and, in Excel, use Report Connections so one slicer drives several pivots and charts at once.
  9. Choose charts by naming the relationship first, build them from your summaries, and remove chart junk and truncated axes.
  10. Assemble the three-layer dashboard (data, calculation, presentation) with named ranges, KPI tiles, sparklines, conditional formatting, and protection, then test it against the at-a-glance standard.

Pairs well with

Courses members commonly take alongside this one.

Flagship CoursePreview

Freelance Business Foundations: Position, Price, Sell, and Deliver High-Value Services

Freelancing · Beginner · 16h

Build a freelance business clients understand, trust, and pay for—without vague positioning, random referrals, or underpriced custom work.

Self-pacedPreview
Client GrowthPreview

Freelance Client Acquisition: Outreach, Leads, Referrals, and Deal Flow

Freelancing · Beginner · 15h 30m

Build a repeatable acquisition system that turns targeting, outreach, referrals, and follow-up into a stable freelance opportunity pipeline.

Self-pacedPreview
Sales SystemPreview

Freelance Sales & Proposals: Discovery Calls, Scoping, Objections, and Closing

Freelancing · Intermediate · 16h

Run better discovery calls, scope work properly, write proposals clients can decide on, and close without discounting your value into the floor.

Self-pacedPreview