The Work Gym

Courses / Data and analytics tools

Google Sheets

Practice formulas, data tools, charts, and Apps Script in a copy of Sheets that checks your work.

Start lesson 1 Practice interview Take the assessmentPractice the first unit of every course. No account needed.

76lessons

482checked exercises

11projects, cases and exams

Prepares you for Office and analyst roles

Projects, case studies and mock exams

Case study · BeginnerThe cafe's slow Tuesday

Separate transaction volume from average spend to explain a cafe's weak Tuesday.

6 steps · 22 min Project · IntermediateClean a contact export

Turn a messy export of 18 member contacts into clean names, emails, phones and dates, flag duplicates, add a status dropdown and count who is ready to import.

11 steps · 40 min Case study · IntermediateA slipping product line

Compare two years of sales by product line, find the line that fell most, and pin down the quarter to investigate.

7 steps · 25 min Project · AdvancedMonthly report from three store files

Pull October sales from three store files with IMPORTRANGE, stack them into one table, and build the month-end report: totals against target, categories, top products, a pivot table and a headline.

10 steps · 45 min Case study · AdvancedThe free shipping test

Judge whether a free-shipping threshold raised order values enough to cover the shipping fees given up.

8 steps · 25 min Project · ExpertHousehold budget tracker

Build a category budget tracker with refund-aware actuals, exceptions, a comparison chart, and entry checks.

14 steps · 50 min Project · ExpertInventory and reorder sheet

Build a reorder worksheet that handles lead times, zero demand, missing SKUs, and whole-pack purchasing.

11 steps · 45 min Mock exam · ExpertGoogle Sheets skills assessment

Complete twelve original analyst tasks covering calculations, cleaning, arrays, charts, views, and scripts in 35 minutes.

12 questions · 35 min

Work in a spreadsheet that follows Google Sheets menus, formulas, spilled arrays, and error messages. Your answers are checked on the visible data and a changed copy, so a calculation must adapt rather than repeat a typed result.

Start with references, formats, filling, and everyday totals. Continue through lookups, dates, text cleanup, filter views, pivot tables, charts, arrays, QUERY, connected files, named ranges, and protection. The expert level covers LET, the LAMBDA helpers, custom functions, and scripts that read and write ranges.

Unit reviews use fresh fictional data. Four projects and three case studies combine the tools into finished reports and checked findings. Three interview sets and a timed practical assessment help you prepare for spreadsheet job tests.

Projects that use Google Sheets

Work one company brief through several tools. Your Google Sheets stage reads the tables from the stage before.

Project · Greenbasket Market · OutlineThe empty-shelf problemGoogle Sheets → SQL → Tableau · 2 hr

See all projects

Beginner

Basics in Sheets

Formulas, references, SUM, AVERAGE, MIN, MAX, counting and percentages.

5 lessons

1Formulas and cell referencesWrite formulas that point at cells, copy them down a column, and lock one cell with $.5 exercises · 8 minFree

2SUM and AVERAGE, with the function helpTotal and average a column, read the function help card, and use open-ended ranges like B2:B.5 exercises · 9 minFree

3MIN, MAX, COUNT and COUNTAFind the smallest and largest values, and count numbers, entries and blanks the right way.5 exercises · 9 minFree

4Percentages: share, change and growthWork out a share of a total, a percent change, and a price raised or cut by a percent.5 exercises · 10 minFree

Practice setEveryday formulasMixed practice on a food truck's week: totals, averages, extremes, counts, per-cover figures and change.Free

Entering and formatting data

Number formats, series fills, entry shortcuts, and checkboxes.

4 lessons

6Number formats: currency, percent, datesUse the toolbar and 123 menu to show numbers as amounts, rates and dates without changing their values.5 exercises · 12 minWith a plan

7Fill handle and seriesCopy formulas, continue number and date patterns, and enter one value across a selection.5 exercises · 12 minWith a plan

8Checkboxes and counting ticksInsert checkboxes, then count, measure and total what is ticked.5 exercises · 9 minWith a plan

Practice setTidy entry and formatsPrepare an equipment rental sheet with readable amounts, copied calculations, dates, rates and payment counts.With a plan

Conditional totals

Count, total, average and find extremes of the rows that match one or more conditions.

4 lessons

10COUNTIF and SUMIF: count and total what matchesCount rows that meet a condition and total the numbers that go with them.5 exercises · 10 minWith a plan

11COUNTIFS and SUMIFS: several conditionsCount and total rows that meet two or more conditions, including number and date ranges.5 exercises · 11 minWith a plan

12AVERAGEIF, MAXIFS and MINIFSAverage, maximum and minimum of the rows that match, and how blanks are treated.5 exercises · 10 minWith a plan

Practice setConditional totalsMixed practice on a library's loans: count, total, average and find extremes of the rows that match.With a plan

Sort, filter and clean

Sort complete records, save personal views, remove duplicates, and investigate a cafe week.

5 lessons

14Sort a range and sort by several columnsSort complete records by one or several columns while keeping the header out of the data.5 exercises · 12 minWith a plan

15Filters and filter viewsCreate named and temporary views without changing another person's shared filter.5 exercises · 15 minWith a plan

16Remove duplicates and trim whitespaceChoose a duplicate key, preserve legitimate repeat visits, and remove stray whitespace.5 exercises · 12 minWith a plan

Case studyThe cafe's slow TuesdaySeparate transaction volume from average spend to explain a cafe's weak Tuesday.With a plan

Practice setBeginner reviewPrepare and inspect a food pantry delivery list using the beginner tools together.With a plan

Intermediate

Logic

IF, IFS, AND, OR, NOT, and handling errors with IFERROR and IFNA.

4 lessons

19IF: one result or anotherReturn one value when a test is true and another when it's false, with numbers, text and blank cells.5 exercises · 11 minWith a plan

20IFS, AND, OR and NOTCombine tests with AND and OR, pick from several bands with IFS, and add the catch-all IFS needs.5 exercises · 12 minWith a plan

21IFERROR and IFNAReplace errors with something useful, and choose IFNA when only a missing lookup should be hidden.5 exercises · 10 minWith a plan

Practice setLogicMixed practice on a gym's class bookings: labels, bands, combined rules and error handling.With a plan

Lookups

VLOOKUP, XLOOKUP, INDEX and MATCH, and lookups that need two keys.

5 lessons

23VLOOKUP in Google SheetsLook up a price or a name from another tab with VLOOKUP, exact match, and IFNA for missing keys.5 exercises · 12 minWith a plan

24XLOOKUP in Google SheetsLook up in any direction, return several columns, and set your own not-found value with XLOOKUP.5 exercises · 11 minWith a plan

25INDEX and MATCHFind a position with MATCH and read a cell with INDEX, including two-way lookups across a grid.5 exercises · 12 minWith a plan

26Lookups with two conditionsLook up a value that needs two keys, such as product and size, with FILTER, XLOOKUP or SUMIFS.5 exercises · 12 minWith a plan

Practice setLookupsMixed lookup practice on a bike rental shop: types, rates, missing bikes and two-way finds.With a plan

Dates and text

Date parts, month ends, working days, and cutting and cleaning text.

5 lessons

28Dates: YEAR, MONTH, EOMONTH and EDATEPull parts out of dates, find month ends and renewal dates, and turn text into real dates.5 exercises · 11 minWith a plan

29Days between dates: DATEDIF and NETWORKDAYSCount years, months, calendar days and working days between dates, with holidays left out.5 exercises · 12 minWith a plan

30LEFT, RIGHT, MID, LEN and FINDCut codes and words out of text by position, and find where a character is.5 exercises · 11 minWith a plan

31TRIM, PROPER, SUBSTITUTE and VALUETidy spaces and capitals, strip unwanted characters, and turn text numbers into real numbers.5 exercises · 11 minWith a plan

Practice setDates and textMixed practice on an online store's orders: shipping times, months, names and codes.With a plan

Data tools

Conditional formatting, dropdowns, pivot tables, charts, contact cleanup, and a sales case study.

7 lessons

33Conditional formatting with a custom formulaColor whole rows, overdue dates and duplicates with "Custom formula is" rules.5 exercises · 13 minWith a plan

34Dropdown lists with data validationAdd dropdowns, read their options from a range, reject bad entries, and add checkboxes.5 exercises · 10 minWith a plan

35Pivot tablesSummarize a sales log with a pivot table: rows, columns, values and filters.5 exercises · 12 minWith a plan

36Charts: column, line and pieChoose chart types for comparisons, trends, shares, and mixed series, then set their source and labels.5 exercises · 20 minWith a plan

ProjectClean a contact exportTurn a messy export of 18 member contacts into clean names, emails, phones and dates, flag duplicates, add a status dropdown and count who is ready to import.With a plan

Case studyA slipping product lineCompare two years of sales by product line, find the line that fell most, and pin down the quarter to investigate.With a plan

Practice setData toolsUse validation, formatting, pivots, and charts to prepare a workshop coordinator's booking report.With a plan

Advanced

Dynamic arrays

FILTER, UNIQUE, SORT, SORTN, ARRAYFORMULA, array literals and SEQUENCE: formulas that return whole lists.

6 lessons

40FILTER: the rows that matchReturn every row that meets one or more conditions with FILTER, and see how the result spills.5 exercises · 12 minWith a plan

41UNIQUE and SORT: clean lists in orderPull a list of distinct values with UNIQUE, order rows with SORT, and take the top rows with SORTN.5 exercises · 11 minWith a plan

42ARRAYFORMULA: one formula for a whole columnFill a whole column from one cell with ARRAYFORMULA, keep empty rows blank, and total without a helper column.5 exercises · 12 minWith a plan

43Array literals and SEQUENCEBuild arrays with curly braces, stack and join ranges, and number rows with SEQUENCE.5 exercises · 11 minWith a plan

44SORTN: top lists, ties and one per groupBuild top-N lists with SORTN, decide what happens to ties, and keep the best row per group.5 exercises · 11 minWith a plan

Practice setDynamic arraysMixed practice on a support team's tickets with the array functions: lists that filter, sort and spill.With a plan

QUERY

Select, filter, group, pivot and label data with QUERY, by date and across tabs.

5 lessons

46QUERY: select and wherePick columns and rows with the QUERY function: select, where, order by and limit.5 exercises · 13 minWith a plan

47QUERY: group by, pivot and labelSummarize with QUERY: totals and counts per group, renamed headings, and a pivot across columns.5 exercises · 14 minWith a plan

48QUERY with datesFilter QUERY results by date, group by month, and take the dates from cells.5 exercises · 13 minWith a plan

49QUERY over stacked tabsQuery several tabs at once by stacking them with braces, with Col ids, blank rows and cell inputs.5 exercises · 13 minWith a plan

Practice setQUERYMixed QUERY practice on a delivery fleet's trips: select, where, order, limit, group, pivot, label and dates.With a plan

Text patterns and other files

Text patterns, connected files, named ranges, and protection, with a review.

6 lessons

51SPLIT, JOIN and TEXTJOINBreak text into columns with SPLIT, and glue values back together with JOIN and TEXTJOIN.5 exercises · 11 minWith a plan

52REGEXMATCH, REGEXEXTRACT and REGEXREPLACEFind, pull out and clean up text patterns with the three REGEX functions.5 exercises · 14 minWith a plan

53IMPORTRANGE: pull data from another fileBring data from another spreadsheet with IMPORTRANGE, allow access, and summarize it with QUERY.5 exercises · 12 minWith a plan

54GOOGLEFINANCE with practice pricesFetch a price, an attribute and a price history with GOOGLEFINANCE, and value a small portfolio.5 exercises · 11 minWith a plan

55Named ranges and protected rangesName a range for readable formulas and protect calculation cells with the appropriate editing rule.5 exercises · 15 minWith a plan

Practice setAdvanced reviewMixed review of the Advanced units on a sales team's leads: arrays, QUERY, text patterns and imports.With a plan

Reports and dashboards

Sparklines, dashboard calculations, a monthly report project, and a shipping case study, followed by mixed practice.

5 lessons

57SPARKLINE: charts in a cellDraw line, column, bar, and win-loss sparklines with explicit options and comparable scales.5 exercises · 15 minWith a plan

58Build a one-page dashboardCombine revenue totals, target attainment, a monthly report table, and a chart with a clear source.5 exercises · 20 minWith a plan

ProjectMonthly report from three store filesPull October sales from three store files with IMPORTRANGE, stack them into one table, and build the month-end report: totals against target, categories, top products, a pivot table and a headline.With a plan

Case studyThe free shipping testJudge whether a free-shipping threshold raised order values enough to cover the shipping fees given up.With a plan

Practice setReports and dashboardsFinish a five-week bicycle shop report with reconciled headlines, a visible source, and readable charts.With a plan

Expert

LET, LAMBDA and the helpers

Name parts of formulas with LET, write your own functions with LAMBDA and named functions, and loop over ranges with MAP, BYROW, REDUCE and SCAN.

5 lessons

62LET: name the parts of a formulaGive names to the pieces of a long formula with LET, so each piece is written once and the formula reads like a sentence.5 exercises · 12 minWith a plan

63LAMBDA and named functionsWrite your own functions: a LAMBDA called in place, then named functions from Data > Named functions that work in any cell of the file.5 exercises · 15 minWith a plan

64MAP, BYROW and BYCOLRun a LAMBDA over every value, every row or every column of a range with one formula that spills.5 exercises · 14 minWith a plan

65REDUCE and SCAN: running totalsCarry a running result through a list: REDUCE returns where it ends, SCAN returns every step on the way.5 exercises · 15 minWith a plan

Practice setLET, LAMBDA and the helpersMixed practice on a class's quiz scores: LET, BYROW, BYCOL, MAP, SCAN, REDUCE, MAKEARRAY and a named function.With a plan

Apps Script basics

Write custom functions, work with range values, and run small spreadsheet automations.

3 lessons

67Apps Script: your first custom functionWrite small JavaScript functions that spreadsheet formulas can call, including a function that returns an array.5 exercises · 25 minWith a plan

68Apps Script: read and write rangesRead a sheet, transform its rows in JavaScript, and write a correctly sized result to the intended range.5 exercises · 30 minWith a plan

Practice setApps ScriptApply custom functions and small spreadsheet scripts to a garden shop's order list.With a plan

Projects, interviews and tests

Build a budget tracker and reorder sheet, practice interview questions, review expert skills, and complete a timed assessment.

7 lessons

ProjectHousehold budget trackerBuild a category budget tracker with refund-aware actuals, exceptions, a comparison chart, and entry checks.With a plan

ProjectInventory and reorder sheetBuild a reorder worksheet that handles lead times, zero demand, missing SKUs, and whole-pack purchasing.With a plan

Interview setEasyEight warm-up questions from Google Sheets screens for analyst, operations and admin roles, answered live on one order sheet.With a plan

Interview setMediumEight mid-level questions on a wholesaler's sales: date ranges, two-way lookups, filtered lists, top deals, and one-formula columns.With a plan

Interview setHardEight harder questions: two-key lookups, running totals that restart, latest dates, ties, pivots, stacked tabs and customers in both months.With a plan

Practice setExpert capstoneCombine array helpers and small scripts in a fresh dispatch workbook before the timed assessment.With a plan

Mock examGoogle Sheets skills assessmentComplete twelve original analyst tasks covering calculations, cleaning, arrays, charts, views, and scripts in 35 minutes.With a plan