Excel for data analysts

85 levels in 12 chapters

Learn Excel the way analysts use it: real tickets answered in a spreadsheet in your browser, on slices of the same store data, checked cell by cell.

By the end you can clean an export, look values up, total by condition, build PivotTables and charts, shape data with Power Query steps and assemble the monthly report. Every level is open; the path only suggests an order.

Chapter 1 · 5 levels

Formulas and references

5 lanterns to light

  1. Line totals with cell references
  2. Summarise the lighting range
  3. Prices with tax, one rate for every row
  4. Shipping fees with IF
  5. Boss Boss: build the order-total sheet

Chapter 2 · 8 levels

Decisions and lookups

8 lanterns to light

  1. Three shipping bands with IFS
  2. Thank-you notes with AND and OR
  3. List prices with VLOOKUP and IFERROR
  4. Last year's quarter averages with HLOOKUP
  5. Product names with XLOOKUP
  6. INDEX/MATCH, right to left
  7. What a lookup returns for a missing key
  8. Boss Boss: update the kitchen price list

Chapter 3 · 7 levels

Conditional totals

7 lanterns to light

  1. Count what's there with COUNTA, COUNTBLANK and COUNTIF
  2. App orders with COUNTIF and SUMIF
  3. Several conditions with SUMIFS and COUNTIFS
  4. Criteria strings, wildcards and a threshold cell
  5. What "<>" does with blank cells
  6. Biggest and smallest promo order with MAXIFS
  7. Boss Boss: the channel × status summary table

Chapter 4 · 8 levels

Text and dates

8 lanterns to light

  1. Split emails with LEFT, FIND and RIGHT
  2. Pick labels with &, UPPER and TEXT
  3. Codes, slugs and keyword flags
  4. Month keys and anniversaries with TEXT and EDATE
  5. Why a date shows as 45672
  6. Deadlines and same-week checks with WEEKNUM
  7. Days between dates with DATEDIF
  8. Boss Boss: the monthly returns sheet

Chapter 5 · 8 levels

Cleaning an export

8 lanterns to light

  1. Duplicate customers by email
  2. Fix spaces and casing in city and state
  3. Numbers stored as text
  4. Quiz: SUM vs + on numbers stored as text
  5. Dates stored as text, in three formats
  6. Missing values, and a reason for each fix
  7. Outliers in a quantity column
  8. Boss Boss: hand back a cleaned customer export

Chapter 6 · 8 levels

Tables, validation and formatting

8 lanterns to light

  1. Quiz: what kind of data is in each column?
  2. A filter you can check, written as a formula
  3. Quiz: sort by category, then by revenue
  4. Highlight rules, one TRUE or FALSE per row
  5. A "Use a formula" rule across a whole grid
  6. Quiz: what an Excel Table does when a row arrives
  7. Validation rules for the product import
  8. Boss Boss: replay the order-entry log through the new rules

Chapter 7 · 7 levels

PivotTables

7 lanterns to light

  1. Your first PivotTable
  2. Count orders by channel and status
  3. Average pages per visit, mobile only
  4. The marketing mix as % of each column
  5. Returns by reason and month, biggest first
  6. Where does each field go?
  7. Boss Boss: a day's sales, from lines to a pivot to the headline

Chapter 8 · 7 levels

Charts

7 lanterns to light

  1. Revenue by category as a column chart
  2. Which chart answers the question?
  3. Daily spend around Black Friday as a line chart
  4. A price histogram with COUNTIFS
  5. Price vs units sold on a scatter chart
  6. Reading a chart honestly
  7. Boss Boss: a one-page daily dashboard

Chapter 9 · 7 levels

Statistics and finance

7 lanterns to light

  1. A typical order line with AVERAGE, MEDIAN and MODE.SNGL
  2. Restock numbers with ROUND, INT and ABS
  3. Same average, different days with STDEV.S
  4. STDEV.S or STDEV.P?
  5. Does traffic move with sales? CORREL
  6. Financing a packing line with PMT and NPER
  7. Boss Boss: can we fund a second warehouse?

Chapter 10 · 6 levels

Consolidating data

6 lanterns to light

  1. Three channel reports, one quarter total
  2. Two exports, two sort orders
  3. One master table, totals per product and channel
  4. A PivotTable over three monthly exports
  5. Consolidate, PivotTable or master table?
  6. Boss Boss: compare February's price list with today's catalog

Chapter 11 · 7 levels

Power Query

7 lanterns to light

  1. Import a CSV and clean it in three steps
  2. Split a payment code, combine two columns
  3. A total column, a month column, then one month
  4. Merge a discount table, then price every line
  5. Append Q3 under Q2
  6. Import from SQL Server with a query
  7. Boss Boss: from a messy export to revenue by category

Chapter 12 · 7 levels

The monthly report

7 lanterns to light

  1. Quiz: from a vague request to a SMART objective
  2. From order lines to order totals with SUMIF
  3. The channel summary with SUMIFS
  4. Return rate per category with COUNTIFS
  5. Quiz: which formula is average order value?
  6. The KPI block and a one-line summary with TEXT
  7. Boss Boss: assemble the monthly report

Brian Mitchell · Finance Analyst

"I exported the first twelve order lines and need what each line actually earned. Can you fill in line_total for every row? Remember the discount: 10 means 10% off."

Light the next lantern

Cookie settings

Pick what may run on this site. You can change this any time from "Cookie settings" at the bottom of every page.