Google Sheets in Practice: Formulas, Tables, Charts and Gemini

What's inside

1. Level 1: What You Have: Plans, IT Settings and Gemini AccessFree
   Find out which Sheets and Gemini features your consumer, small business or corporate plan includes, what IT may have switched off, and why every plan and feature note in this course is dated and checked against Google's current pages; this course is independent of Google and reproduces none of its documentation.
2. Level 2: Laying Out a Sheet That WorksFree
   Structure a spreadsheet so formulas, filters and analysis work later: one table per tab, one header row, one record per row and raw data kept apart from summaries.
3. Level 3: Data Types, Number Formats and Dates
   Make Sheets read numbers, currency, percentages and dates correctly, and fix values stored as text or dates read in the wrong locale.
4. Level 4: Cleaning Text and Using Cleanup Suggestions
   Clean inconsistent text with TRIM, PROPER, SPLIT, text-to-columns, find-and-replace and duplicate removal, use Sheets' cleanup suggestions, and try Gemini-assisted fills where your plan includes them, checking every result.
5. Level 5: Data Validation and Dropdowns
   Stop bad data at entry with validation rules, dropdown chips, checkboxes and helpful rejection messages.
6. Level 6: References and Named Ranges
   Control how formulas behave when copied, using relative, absolute and mixed references, cross-sheet references and named ranges.
7. Level 7: Conditional Totals: SUMIFS and COUNTIFS
   Total and count rows that meet one or more conditions, and use Gemini to draft these formulas, then test each draft against a total you have worked out by hand.
8. Level 8: Decisions and Lookups: IF, IFS and XLOOKUP
   Add logic with IF, IFS, AND, OR and IFERROR, and retrieve matching values with XLOOKUP, including what to return when no match is found.
9. Level 9: Dynamic Arrays: FILTER, SORT, UNIQUE, ARRAYFORMULA
   Produce results that spill and update automatically with FILTER, SORT and UNIQUE, and apply one formula to a whole column with ARRAYFORMULA.
10. Level 10: QUERY and IMPORTRANGE
    Summarise data with QUERY's SQL-like language and pull data from another spreadsheet with IMPORTRANGE, including its access permissions and limits.
11. Level 11: Tables and Filter Views
    Turn ranges into Sheets tables with typed columns, and use filter views so collaborators can sort and filter without changing what others see.
12. Level 12: Pivot Tables
    Summarise large data sets with pivot tables using rows, columns, values, filters, grouping and calculated fields, and check any pivot table Gemini suggests.
13. Level 13: Charts That Tell the Truth
    Choose the right chart type and build it honestly, with suitable axis baselines, consistent scales, clear labels and no distortion, and review any chart Gemini generates.
14. Level 14: Conditional Formatting
    Highlight exceptions, thresholds, duplicates and trends with preset and custom-formula conditional formatting rules, used sparingly enough to stay readable.
15. Level 15: Google Forms into Sheets: A Live Tracker
    Link a Google Form to a spreadsheet and build a tracker that reads the responses tab without editing it, with optional AI-assisted categorising where your plan includes it.
16. Level 16: Budget and Invoice Workbooks
    Build a monthly budget and a reusable invoice template with inputs, calculations and outputs kept separate, and protect the formula cells.
17. Level 17: Sharing, Version History and Excel Files
    Share safely with the right roles, recover earlier work from version history, and know what breaks when files move between Sheets and Excel.
18. Level 18: Final Challenge: From Messy Export to Findings Exam
    Bring together the whole course to turn a messy data export into a clean, validated, checked spreadsheet with a summary, an honest chart and written findings, using Gemini only where your plan allows and checking every number it produces.

Access

The first 2 levels are free with a free account. Every level, the podcast edition and the AI tutor come with All Access at £4.99/month or any Creator plan — see pricing.

Start free →