Spreadsheet Data Analysis: Formulas, Pivot Tables and Charts
Clean, look up, summarise and chart data in Excel, Google Sheets or LibreOffice Calc, then build what-if models with validation, scenario tables and Goal Seek.
Beginner · Data Science · about 14 hours · 5 modules · certificate on completion
Taught by Marisèl Rodriguèz, Akosùa Mensàh, AI Learning Companions.
About this course
Spreadsheets are where most of the world's data analysis happens, and a few well-chosen techniques turn them from a grid of numbers into a reliable analysis tool. This course teaches them in Microsoft Excel, Google Sheets and LibreOffice Calc side by side, noting where the three differ, through a year of (made-up) sales from Morning Loaf, a bakery with three branches. You'll start by setting data up as a tidy table, importing CSV files safely and writing formulas whose relative, absolute and mixed references behave when they're copied. Then you'll clean a messy export of online orders: stray spaces, inconsistent capitals, dates stored as digits, amounts stored as text and duplicate rows.
Next come lookups, with XLOOKUP, and INDEX/MATCH and VLOOKUP for older versions, including approximate matches for bands and the reasons lookups fail. You'll summarise the year with SUMIFS, COUNTIFS and AVERAGEIFS, building a branch-by-month grid from one formula, check it against a pivot table and chart the result. Finally you'll protect input with data validation and build a what-if model of a price change, with a scenario table and Goal Seek. Every lab adds to one workbook and has exact results to check, worked out from the same data, and the capstone is a full analysis for the bakery's owners. Data Storytelling and Dashboard Design builds on it, and Data Analysis with Python and pandas is the next step when data outgrows a spreadsheet.
What you'll learn
- Set data up as a tidy table, import CSV files and write formulas with relative, absolute and mixed references
- Clean text, dates and numbers stored as text, and find duplicates and gaps with formulas and checks
- Look up values with XLOOKUP, INDEX/MATCH and VLOOKUP, and diagnose lookups that fail
- Summarise data with SUMIFS, COUNTIFS and AVERAGEIFS, pivot tables and charts
- Validate input and build what-if models with scenario tables and Goal Seek
Skills you'll gain
Spreadsheet formulas, Absolute and relative references, Data cleaning, XLOOKUP and INDEX/MATCH, SUMIFS and COUNTIFS, Pivot tables, Spreadsheet charts, Data validation, What-if analysis, Goal Seek
Tools
Microsoft Excel, Google Sheets, LibreOffice Calc
Before you start
Basic computer skills: opening files, typing, copying and pasting. You need one spreadsheet app: Microsoft Excel (XLOOKUP needs Microsoft 365 or Excel 2021 or later), Google Sheets (free, in a browser) or LibreOffice Calc (free; XLOOKUP needs 24.8 or later). The labs give alternatives where the apps differ, and the data comes with them.
Modules
Formulas and references
Set data up as a tidy table, import a CSV file into Excel, Google Sheets or LibreOffice Calc, and write formulas whose references behave when they're copied.
- Setting up data for analysis (12 min)
- Formulas, functions and errors (12 min)
- Relative, absolute and mixed references (12 min)
- Hands-on lab: Import the year's sales and work out the totals
Cleaning text and dates
Fix the mess exports bring: stray spaces and inconsistent capitals, dates stored as digits, numbers stored as text, duplicates and gaps, using formulas in helper columns and checks that count problems.
- Cleaning text (12 min)
- Dates and numbers stored as text (12 min)
- Duplicates, blanks and quality checks (12 min)
- Hands-on lab: Clean a messy order export
Lookups
Bring information from one table into another with XLOOKUP, or INDEX/MATCH and VLOOKUP where XLOOKUP isn't available, use approximate matches for bands, and find out why lookups fail.
- XLOOKUP (12 min)
- INDEX/MATCH and VLOOKUP (12 min)
- When lookups fail (12 min)
- Hands-on lab: Bring product details into the sales
Conditional aggregates, pivot tables and charts
Summarise sales with SUMIFS, COUNTIFS and AVERAGEIFS, build a branch-by-month grid with one formula, check it with a pivot table, and chart the result.
- SUMIFS, COUNTIFS and AVERAGEIFS (12 min)
- Pivot tables (12 min)
- Charts from summaries (12 min)
- Hands-on lab: Summarise sales by branch and month
Data validation and what-if analysis
Stop bad input with data validation, build a model that separates assumptions from calculations, compare scenarios and use Goal Seek, then check and document a workbook before sharing it.
- Data validation (12 min)
- What-if analysis (12 min)
- Checking and sharing a workbook (10 min)
- Hands-on lab: Build a price-change model
Project: Analyse a year of sales for the bakery's owners
Morning Loaf's owners want one workbook that answers their questions about 2025 and helps them plan 2026. Using sales_2025.csv, products.csv and online_orders.csv, build it in Excel, Google Sheets or LibreOffice Calc. It needs: the sales data, untouched, on its own sheet, with revenue, fees, product details and profit added by formulas; the online orders cleaned, with duplicates flagged and a cleaning log; a summary sheet answering, with SUMIFS-style formulas, which branch, category and month earn the most revenue and gross profit, how customers pay, and what the unlisted seasonal specials added; a pivot table that reconciles with the formulas; at least three charts titled with their findings; a validated what-if model for a price change in the category you'd recommend changing, with a scenario table and a Goal Seek answer; and a README sheet. Finish with a one-page summary for the owners: five findings with their numbers, one recommendation, and the limits of the data (a sample of 30 sales lines a month, and unit costs covering ingredients only).
Part of
The products this course names are trademarks of their owners, who haven't made or endorsed it.