One dataset. Six tools. Let's build.

1 Cells, references, and basic formulas

A cell is named by its column letter and row number (e.g. D2). A formula always starts with =.

=SUM(D2:D25)        → adds up the Cost column
=AVERAGE(E2:E25)     → average Price
=D2*1.2              → Cost plus 20%

Relative vs. absolute reference is the single most common beginner trip-up. =D2*1.2 dragged down becomes D3, D4... automatically (relative). Lock a cell in place with dollar signs — =D2*$H$1 — and it always points at the same cell no matter where you copy it.

2 Conditional logic: IF, COUNTIF, SUMIF

=IF(G2="Complete", "Paid", "Chase Payment")
=COUNTIF(C2:C25, "Flat Repair")            → how many flat-repair jobs
=SUMIF(C2:C25, "Flat Repair", E2:E25)      → total revenue from flat repairs

Plain English: IF asks a yes/no question and gives a different answer for each. COUNTIF/SUMIF do the same across a whole column — "count/total only the rows that match."

3 VLOOKUP and INDEX/MATCH

A separate Customers sheet lists each customer's city. Rather than retyping it next to every job, VLOOKUP fetches it automatically:

=VLOOKUP(B2, Customers!A:C, 3, FALSE)

"Take the customer ID in B2, look it up in the Customers sheet's first column, and bring back the value from the 3rd column, exact match only." INDEX/MATCH does the same job more flexibly. Learn VLOOKUP first — it's the one you'll see in the wild.

4 PivotTables

This is where Excel stops being a calculator and starts being an analyst. Select your data → Insert → PivotTable. Drag JobType into Rows and Price into Values — Excel instantly shows total revenue per job type, recalculating live if your data changes.

1 SELECT and WHERE

SELECT * FROM Jobs;
SELECT * FROM Jobs WHERE Status = 'Complete';
SELECT * FROM Jobs WHERE Price > 100 AND JobType = 'Full Tune-Up';

SELECT says what columns you want back; WHERE filters which rows qualify — this maps directly onto Excel's Filter.

2 Aggregates and GROUP BY

SELECT JobType, SUM(Price) AS TotalRevenue, COUNT(*) AS JobCount
FROM Jobs
GROUP BY JobType
HAVING SUM(Price) > 100;

This is the SQL version of a PivotTable. HAVING filters groups after they're calculated, whereas WHERE filters rows before.

3 JOIN

This is the concept most people find hardest, so slow down here. A separate Customers table shares a CustomerID with Jobs. A JOIN stitches the two together:

SELECT Jobs.JobType, Jobs.Price, Customers.Name, Customers.City
FROM Jobs
INNER JOIN Customers ON Jobs.CustomerID = Customers.CustomerID;

"For every job, go find the matching customer row and bring its columns along too." This is exactly what VLOOKUP does in Excel — a JOIN is VLOOKUP that can pull back a whole matching table at once. LEFT JOIN keeps every row from the first table even with no match in the second.

4 Building, not just reading

UPDATE Jobs SET Status = 'Complete' WHERE JobID = 3;
DELETE FROM Jobs WHERE JobID = 5;

1 QUERY(): SQL inside a spreadsheet

=QUERY(Jobs!A1:G, "SELECT C, SUM(E) WHERE G = 'Complete' GROUP BY C", 1)

This is literally SQL syntax living inside a cell — total revenue (E) per job type (C), completed jobs only. If SQL made sense to you in Module 2, this will feel instantly familiar.

2 Live, shareable dashboards

IMPORTRANGE("sheet-url", "Jobs!A1:G") pulls live data from a separate spreadsheet. Share a sheet as "View only" so a client sees an always-current dashboard without being able to break your formulas. A Google Form feeding a Sheet gives you a free, live "New Enquiry" tracker.

3 ARRAYFORMULA

=ARRAYFORMULA(IF(E2:E100="", "", E2:E100 - D2:D100))

Instead of dragging one formula down 100 rows, this applies it to the whole range in one go, and keeps working as new rows are added by a Form.

1 Dimensions vs. Measures

This is the one idea Tableau hinges on. Dimensions are categories (JobType, Customer, Status — things you group by). Measures are numbers you calculate (Price, Cost, Margin — things you sum/average). Drag a Dimension onto Columns and a Measure onto Rows, and Tableau draws a chart automatically.

2 Building a dashboard

Connect to your CSV → drag JobType to Columns, SUM(Price) to Rows → instant bar chart. Drag Status onto Colour to split each bar. Add a Date filter, combine charts onto one Dashboard canvas, and add a "filter action" so clicking one chart filters the others.

3 Calculated fields

Margin = [Price] - [Cost]
Margin % = ([Price] - [Cost]) / [Price]

Same logic as an Excel formula, just written in Tableau's own box.

1 Power Query: cleaning data before you chart it

Power BI's built-in cleaning tool (Power Query Editor) lets you remove columns, fix data types, and rename fields before building any visual — the step most people skip, then wonder why their chart looks wrong.

2 Relationships (same idea as a SQL JOIN)

Load Jobs and Customers, and Power BI needs to know they're connected — draw a line between CustomerID in each table in Model view. Once that relationship exists, you can build a chart mixing fields from both as if they were one.

3 DAX basics

Total Revenue = SUM(Jobs[Price])
Total Margin = SUM(Jobs[Price]) - SUM(Jobs[Cost])
High Value Jobs = CALCULATE(COUNTROWS(Jobs), Jobs[Price] > 100)

Measures calculate on the fly based on what's filtered on screen. Calculated Columns compute a value for every row, stored permanently. When in doubt: if the number should change depending on what's filtered/selected, it's a Measure. CALCULATE recalculates a Measure under a different filter condition than what's on screen.

1 Variables, lists, and loops

job_prices = [90, 25, 450, 20, 30]
total = sum(job_prices)
print(f"Total revenue: ${total}")

for price in job_prices:
    if price > 100:
        print(f"High value job: ${price}")

A list is a column of data; a for loop repeats an action for every item — same idea as dragging a formula down a spreadsheet column.

2 pandas: Python's version of a spreadsheet

import pandas as pd

jobs = pd.read_csv("jobs.csv")
jobs["Margin"] = jobs["Price"] - jobs["Cost"]
jobs.groupby("JobType")["Price"].sum()   # the pandas PivotTable
jobs[jobs["Status"] == "Complete"]       # the pandas filter

Every line here does something you've already done by hand in Excel and SQL — pandas is just the fastest way to do it once your data gets big or repetitive.

3 Putting it together: an automated report

def summarise_jobs(csv_path):
    jobs = pd.read_csv(csv_path)
    jobs["Margin"] = jobs["Price"] - jobs["Cost"]
    return jobs.groupby("JobType").agg(
        TotalRevenue=("Price", "sum"),
        AvgMargin=("Margin", "mean"),
        JobCount=("JobID", "count")
    ).sort_values("TotalRevenue", ascending=False)

Run this on any month's export and get an instant summary — no manual PivotTable rebuilding required.