Excel & Spreadsheets · Lesson 4 of 6
Building Dashboards and Reports
A dashboard turns rows of data into answers to questions.
After This Lesson, You Will Be Able To
Build a simple dashboard in Google Sheets that displays your key metrics visually — pulling from a data sheet automatically.
What Makes a Good Dashboard
A bad dashboard is a spreadsheet with a chart on it. The difference: a dashboard is designed around decisions, not data. Every element answers a specific question you actually make decisions from.
The Dashboard Building Process
Step 1: Define your 3–5 key metrics
What numbers do you check weekly to know if your business is healthy? Total revenue this month. Number of active clients. Unpaid invoices. Tasks completed vs overdue. Write these down before opening the spreadsheet. Your dashboard is built around these.
Step 2: Set up a separate data sheet
Your raw data lives in one sheet (Sheet1 or 'Data'). Your dashboard pulls from it using formulas. This separation means your dashboard updates automatically when you add data — you never manually update the numbers.
Step 3: Use summary formulas in the dashboard
On your Dashboard sheet, use SUMIF, COUNTIF, and basic arithmetic to pull each key metric from the data sheet. Format these prominently — large font, color, clear label. No tables of data on the dashboard — just the answers.
Step 4: Add one chart
One chart is usually enough for a simple dashboard. Revenue over time (line chart) or revenue by client/category (bar chart) adds visual context that numbers alone can't. Don't add charts for every metric — focus on the one that shows trend or comparison most usefully.
Step 5: Add conditional formatting
Color cells automatically based on their value: green if the number is above target, red if below, yellow if borderline. This is the feature that makes dashboards truly 'at a glance' — you see the status without reading numbers.
Exercise
~10 minutes · ChatGPT or Claude
Prompt to use
I want to build a dashboard in Google Sheets for my [describe your use case — freelance business / content creation / sales tracking / personal finances]. My key metrics are: [list the 3–5 numbers you check most often]. My data lives in Sheet1 with these columns: [describe your column structure]. Help me: 1) The formula for each metric to pull it from Sheet1 to the Dashboard sheet, 2) How to set up conditional formatting for each metric (what counts as good/bad), 3) What type of chart would be most useful and how to create it, 4) What the dashboard layout should look like (where to put each element).
The best dashboard you can build is the one you actually look at every week. What's the one number that, if it went wrong, would tell you immediately that your business had a problem?
Key Takeaways
A dashboard answers specific questions — it's not just data with a chart on top.
Raw data in Sheet1. Dashboard in Sheet2. Formulas connect them. Never mix data and dashboard on the same sheet.
3–5 key metrics is enough. Every additional metric dilutes attention. Focus on the ones you actually decide from.
Conditional formatting is what makes a dashboard actually 'at a glance'. Add it to every key number.
Build your first dashboard from your existing data.
Open the spreadsheet you've been building in this track. Create a new sheet called 'Dashboard'. Define your 3 most important questions. Use Claude to write the formulas that answer them, pulling from your data sheet. Add conditional formatting. Add one chart. Send yourself the link to open it on your phone every Monday morning.
Next Lesson
Pivot Tables and Data Analysis
Excel & Spreadsheets · Lesson 5 of 6 · 10 min