What You Actually Want to See First Thing
Okay. New module, and it's the last one, so let's make it count.
We've spent this whole course building pieces. Inventory tracker. Invoice. Color rules that turn red when something's wrong. Good pieces, all of them. But right now they're scattered across tabs and files, and you have to go hunting for the truth about your business every time you want to know something.
So the last thing I want to give you is a simple dashboard. One tab. The stuff you actually check.
Sooo, before we build anything, let's talk about what "dashboard" even means, because I think the word does us dirty.
The word "dashboard" is lying to you a little
When people hear dashboard, they picture charts. Pie graphs. Little colored bars. Something that looks like it belongs on the news.
Here's my actual opinion, and I've said it before in this course but it matters most right here: conditional formatting is worth more than any chart. A cell that turns red when you're low on stock does more real work for you than a pie chart you glance at once and never look at again. Color that means something beats a picture that's just pretty.
So your dashboard isn't going to be fancy. It's going to be honest. A handful of numbers, updated automatically from the sheets you already built, with color doing the work of flagging what needs your attention.
Let me tell you about the garage sale dashboard
I have to tell you about this because it's the whole reason I teach it this way now.
A while back I built a dashboard for a garage-sale fundraiser. Not for work, just to help out. And I mean I built it. Full Saturday. Charts, color-coded categories, a little running total that updated live, the works. I was proud of that thing.
Nobody looked at it. Not one person. They just wrote numbers on a legal pad like they always had and moved on with their lives.
I was quiet the rest of that day, not gonna lie. It stung a little.
But here's what I actually learned, and it's the thing I want you to walk away from this whole course with: the fancy version isn't the point. The person using it is. If a dashboard doesn't match how you actually think and actually work, you won't open it. It doesn't matter how good it looks.
So the dashboard we're building today is going to be your dashboard. Not mine. Not something impressive. Something you'll actually open on a Monday morning with coffee in your hand.
Building it: the five things you probably want to see
Open a new tab in your workbook. Call it "Overview" or "Dashboard," whatever you'll remember to click on.
Here's my starter list. Pick from it, don't feel like you need all five:
- Total inventory value right now. Pull this from your inventory tab with a SUM formula. Not fancy, just adds up quantity times price across your products.
- Items below your low-stock threshold. You already built the red highlighting for this. Add a cell up top that counts how many rows are currently flagged red, using COUNTIF. Now you don't even have to scroll, the number's right there.
- Unpaid invoices, total dollar amount. SUM again, pulling from your invoice tracker, filtered to anything marked "unpaid" or "overdue."
- Overdue invoices, count. Same idea as your low-stock count. One number, COUNTIF, done.
- This month's total sales. If you're tracking that anywhere, pull it in. If you're not tracking it yet, that's fine, that's a project for future you.
That's it. Five numbers, maybe six. Every one of them pulled automatically from work you already did in earlier lessons. You're not retyping anything. The formulas just reach into your other tabs and grab what's there.
Check it against reality once
Here's the step you're gonna want to skip. Don't.
Once your dashboard numbers show up, go verify at least one of them by hand. Count actual boxes on the shelf. Look at your actual bank balance. Compare it to what the sheet says.
A spreadsheet will tell you a confident wrong answer just as easily as a right one. It doesn't know the difference. Only you catch that, and only if you check. I learned this one the hard way early on with a formula that overcounted a whole product line by about 300 units, and my boss caught it before it cost us anything. I have not stopped double-checking totals against a physical count since. That afternoon was not fun and I'd like to keep it that way.
Make it pretty last, like always
Once your numbers are pulling correctly and your counts match reality, then, and only then, go make it look decent. Bigger font on the totals. A little color. Maybe a border so it feels like its own little space instead of a random tab.
Don't do this first. You know this by now.
Before next time: open your new dashboard tab once a day for the rest of the week, right when you'd normally start work, and see if it actually tells you what you need to know. If it doesn't, that's useful information, not a failure. Adjust it.