Stage 2 of 8 · 6 lessons · ~5 weeks

Spreadsheets properly

Excel or Google Sheets used like an analyst: structured tables, conditional aggregation, XLOOKUP, text and date cleaning, pivot tables and a workbook layout anyone can audit. Every analyst uses spreadsheets, whatever tool they chose.

Start: References and structured tables

Lessons· one lesson for each topic, in this order

Do this

Turn a raw data range into a structured table and rewrite three formulas to use column names.
Build a regions by months summary grid with SUMIFS.
Bring a column from a second table in with XLOOKUP and investigate every unmatched key.
Clean one text column, one number column and one date column in helper columns.
Answer three questions with pivot tables, including one shown as a percentage of the total.
Add data validation to any column people type into.
Write a notes tab: source, date downloaded, and every cleaning step.

Start with

GuideFree
Excel help and learning
Microsoft · Beginner
Official, accurate guides to XLOOKUP, SUMIFS, PivotTables and cleaning functions, with examples you can paste

Practise

Kaggle Datasets
Free, real datasets to practise lookups, pivots and cleaning on something bigger than a tutorial file

Keep handy

Google Sheets function list
Every Google Sheets function on one page, if you work in Sheets or do not have Excel

Also worth your time

Google Data Analytics Professional CertificateGo deeperPaid
Its spreadsheet courses give guided exercises and feedback if you want more structure

Before moving on: A clean, documented workbook built from a public dataset of at least 5,000 rows: raw, working and summary tabs, a lookup from a second table, a SUMIFS summary grid, two pivot tables and a notes tab listing every cleaning step.

Tauseef's advice

Do not skip spreadsheets because they feel basic. Finance, operations and managers live in Excel and Google Sheets, and your SQL and dashboards will be checked against their sheets. Learn XLOOKUP, SUMIFS and pivot tables well, keep the raw data untouched, and you will be trusted with bigger work.