Data Analysis for Excel and Google Sheets
Work as the analyst for a wholesaler. Answer real business questions with formulas, pivot summaries and charts, and pass a timed skills test.
- Free
- About 11 hours
- 62 lessons
- Beginner
249 practice questions · 2 timed tests · a score for each skill area
You’ll learn toContents
- Write formulas that stay right when they are copied: relative and absolute references, totals, averages and percentages
- Make decisions in a sheet with IF, AND, OR and IFERROR
- Clean a messy list: stray spaces, mixed capitals, numbers stored as text, blanks and duplicates
- Bring two tables together with XLOOKUP, VLOOKUP and INDEX with MATCH
- Total and count by any rule with SUMIFS, COUNTIFS and AVERAGEIFS, and check them with a pivot summary
- Choose the right chart for the question and report the answer in one honest sentence
Free, about 11 hours, all in your browser, even on a phone. You join Pellingham Provisions, a wholesaler that supplies cafés, as their analyst. The owner and the team send you questions. You answer them with formulas on real-looking, messy data: totals you can trust, clean lists, lookups, conditional totals, pivot summaries and charts. Every part has a lab that checks your work as you go. You finish with a timed skills test on data you have never seen, and a certificate. The skills work in Microsoft Excel and Google Sheets.
- Chapter 1 · Getting started: your first week at PellinghamMeet Pellingham Provisions and the team, write a first formula in under 10 minutes, and see where you start.
Your first sheet4 lessons · 1 optionalFree
- Welcome to Pellingham
- Your first formula, one step at a time
- How the lab works
- First look at the orders
- Where you start
- Chapter 2 · Chapter 1: Formulas you can trustAnswer Ines's first questions with formulas that stay right when the data changes or the formula is copied.
Totals that update themselves6 lessons · 1 optional
- How did January go?
- Build it one step at a time
- Traps, and how to check
- January's order lines
- Key terms
- Assessment
- Key points
Rates, shares and the $ sign6 lessons · 1 optional
- What do we keep from each product?
- Build it one step at a time
- Traps, and how to check
- Margins on the price list
- Key terms
- Assessment
- Key points
Decisions with IF6 lessons · 1 optional
- Which orders get free delivery?
- Build it one step at a time
- Traps, and how to check
- Flag the big orders
- Key terms
- Assessment
- Key points
- Chapter 3 · Chapter 2: Clean and connect the dataTurn Mei's messy lists into clean ones, and bring products, customers and orders together in one sheet.
Cleaning a messy list6 lessons · 1 optional
- Mei's messy customer list
- Clean a list, one step at a time
- Cleaning traps, and how to check
- Clean the customer list
- Key terms
- Assessment
- Key points
Lookups: one table into another6 lessons · 1 optional
- Ravi's April lines
- Build a lookup, one step at a time
- Lookup traps, and how to check
- Bring in the product details
- Key terms
- Assessment
- Key points
Codes, dates and months6 lessons · 1 optional
- June's import won't add up
- Fix the import, one step at a time
- Dates, codes and checks
- Fix June's import
- Key terms
- Assessment
- Key points
- Chapter 4 · Chapter 3: Summarise and tell the storyBuild the half-year report Ines asked for: totals by any rule, a pivot summary that checks them, and charts that make the answer plain.
Totals by any rule6 lessons · 1 optional
- Who sells what?
- Build the rep summary, step by step
- Traps, and how to check
- Sales by rep and category
- Key terms
- Assessment
- Key points
Pivot summaries6 lessons · 1 optional
- Half a year on one screen
- Build the summary, step by step
- Traps, and how to check
- Summarise the half year
- Key terms
- Assessment
- Key points
Charts and the one-page report6 lessons · 1 optional
- One page for the bank
- Build the dashboard, step by step
- Report it honestly
- Ines's half-year dashboard
- Key terms
- Assessment
- Key points
- Chapter 5 · Show what you can doPractise on a bike workshop's data if you like, then pass a timed skills test on a garden centre's data you have never seen.
Practice testOptional
- The practice test
- Practice test: Kelvessa's jobs
- Practice skills test
Skills test4 lessons
- Before you start
- Skills test: Orrinvale's sales
- Skills test
- Your certificate, and what next
Share