Hey Reader,

Today we're building a beautiful sales dashboard.

And here's what I love about this one.

It doesn't matter if I'm talking to the CEO, the board of directors, investors, or department heads.

Everyone loves sales.
And presenting it in a beautiful dashboard?

That adds tremendous credibility.

But here's the thing.

We're not just building a dashboard today.

There are Excel techniques in here that you can use across any template you'll ever build.

And one of them is the reason most dashboards look amateur — and most people have no idea it's even a thing.

---

---

Here's the most important thing I can tell you about building dashboards.

Start with the front end. Then build the back end.

This is a popular concept in software engineering.
Your Excel dashboards are no different.

What that means in practice: design the entire thing with fake dummy data before writing a single formula. Get the layout right. Get the colors right. Get the fonts right. Then — and only then — do you wire up the actual data.

Speaking of colors, I tried something a little different this time.

Instead of manually picking colors with a color picker tool, I screenshotted the design and asked ChatGPT to extract the hex codes and font family for me.

It mostly worked - but there were som clear differences..
Maybe this is somewhere AI isn't so strong with...
I'll let you be the judge on that one.

Now here's the detail that most people skip and it's the one that makes the biggest difference.

Sharp edges versus rounded edges on your KPI boxes.

I overlay a rounded rectangle shape — no fill, just a colored outline — on top of each box.

This may seem like a small nitpick, but it's really design elements like this that make you go from amateur to pro.

One small shape. Completely different feel.

For the fonts: title at 34pt bold, section headers at 18pt, KPI numbers at 28pt. Lock those in and your dashboard immediately looks intentional.

Now let's start filling in some fake dummy data.
And with that, the front end is done.

---

Once the design is locked, it's time to build the engine underneath it.

And there are three things you need before a single real number appears.

First: the values themselves.
Second: the dates that drive those values.
Third: dynamic text that updates automatically when the period changes.

Let's start with the dropdown.

I use UNIQUE to pull all the years from the data automatically. Then I use VSTACK to add Trailing Twelve Months at the end of the list.

Awesome. Now we have all the years — plus trailing twelve months — in one clean dropdown.

One dropdown. Every period covered. No manual updates ever.

Here's why that matters. When you change the period from 2026 to trailing twelve months, everything on the dashboard updates. The titles, the numbers, the chart.

Speaking of titles — the period header updates dynamically too. Select 2026 and it shows "Q4 2026." Select trailing twelve months and it shows "Nov 2025 - Jan 2026."

All of that is just an IF and TEXT function working together.

No VBA. No macros. Just clean, readable formulas.

---

This is actually really simple once you understand how SUMIFS works.

We already have all the dates we need.

Now we just need to sum the values that fall within those dates.

Watch how simple this is.

Two conditions: start date greater than or equal to the period start, end date less than or equal to the period end. Lock the revenue range entirely so it doesn't shift when you copy.

That's it.

I check the number against the income statement.
It looks like we did.
The formula works.

For the variance: current minus prior.

For the percentage variance: wrap it in IFERROR so you don't get a divide-by-zero error when the prior period is blank.

And for the up and down arrows next to each KPI?

Alt + 30 for the up arrow. Alt + 31 for the down arrow.

Combine those with a TEXT function that shows the percentage and dollar variance side by side and you have a professional-looking KPI card that tells the whole story in one line.

Alright, looks like we're rock and rolling.

One more thing. For average sales, use AVERAGEIFS — not SUMIFS. I caught that mistake myself mid-build. Easy to miss, makes a big difference.

---

Final step.

Select the data, click Insert, chart. Reformat the axis dates. Match the fill color to your dashboard color scheme.

Then the rounded border trick one more time.

A rounded rectangle shape sent to the back. No fill on the actual chart. The chart now sits inside a professionally rounded container that matches the rest of the dashboard.

Change the period. Everything updates.
Trailing twelve months. 2026. 2027.

All live. No manual work.

This is just one of many dashboards you should know how to build in your toolkit.

And if you made it this far, it's clear you're serious about dashboard building.

Now, setting all of this up from scratch takes time — especially when you're connecting it to real accounting data.

That's exactly why I built Model Wiz. It's an Excel add-in that connects directly to your QuickBooks Online account and gets all of this wired up with just one click.

Skip the setup. Get straight to the insights.

Which part of this dashboard are you going to build first?

Hit reply and let me know.

Josh
Your CFO Guy

Quick note: While I love sharing my finance & accounting knowledge, remember I'm Your CFO Guy, not Your Personal CFO. Everything I share comes from my experience, but each business is unique. My content is educational, not professional advice - always consult with your own qualified advisors for decisions about your specific situation.