The short version: a working Excel dashboard needs three tabs, not one, a raw data tab you never touch by hand, a calculations tab full of SUMIFS and XLOOKUP, and a dashboard tab that only has charts, numbers and slicers on it. Build it in that order and it will survive contact with your actual business. Build it any other way and you'll have a pretty sheet that's wrong by week three.
Why Excel and not a dashboard tool
I get asked this most weeks by people who have just paid £30 a month for a piece of software that promised to "connect all their data" and now can't work out why their numbers don't match their bank account. Here's my honest view after building these for my own business and for clients for the best part of fifteen years: if you have fewer than about 20 staff and your data lives in three or four places, plain Excel beats almost every dashboard tool on the market. It's not because Excel is fancier. It's because you already own it, you already know how to open it, and there's no login screen between you and your numbers on a Sunday night when you're worried about cash.
The dashboard tools earn their keep once you've got real volume, real staff pulling from live systems, and a genuine reason to pay someone monthly to stitch it together. Before that point, most small business owners are paying for complexity they don't need yet. If you're weighing that decision, I've written about what to look for in a productivity template before you buy anything at all, and it applies just as much to dashboards as it does to project trackers.
The three-tab structure that holds up
Every broken dashboard I've been asked to fix has the same root problem, one giant tab with data, formulas and charts all mixed together. The moment you delete a row to "tidy up," a chart breaks, a formula points at the wrong cell, and nobody notices for a month. So the fix is boring but it works every time.
- Tab 1, Raw Data. This is where numbers land. Nothing else lives here. No formulas, no formatting, no charts. Just columns of dates, amounts, categories and names, pasted or imported in.
- Tab 2, Calculations. This is where you turn raw data into meaning, totals by month, conversion rates, averages, running totals. Every formula lives here.
- Tab 3, Dashboard. This is the only tab anyone else ever sees. Charts, big numbers, traffic-light colours, a date filter. Nothing manual, nothing typed directly in.
Once you separate these three jobs, you can rebuild, fix or expand any one of them without wrecking the other two. That's the whole trick. It's not clever. It's just discipline, and discipline is the bit most guides skip because it's less exciting than telling you which chart type looks best.
Step by step: building it from a blank workbook
Step 1: decide what "performance" means for your business
Before you open Excel, write down five to seven numbers on paper. Not fifteen. Five to seven. For an agency that might be new leads, proposals sent, proposals won, monthly recurring revenue, average project value, and days-to-payment. For an ecommerce shop it's more likely revenue, orders, average order value, return rate, and ad spend as a percentage of revenue. Pick the numbers that would change what you do on Monday morning if they moved. Everything else is noise dressed up as insight.
Step 2: build the Raw Data tab
Set up column headers across row 1, one column per field, for example Date, Category, Amount, Client, Channel. Format the whole thing as an Excel Table (select the range, press Ctrl+T). This one habit alone saves you hours later because tables auto-expand when you add rows, and every formula referencing a table column updates itself. Never merge cells here. Never leave blank rows. If your data comes from your accounting software, your CRM, or a payment processor like Stripe, export it as a CSV and paste the values into this tab rather than typing anything by hand.
Step 3: build the Calculations tab
This is where SUMIFS, COUNTIFS and XLOOKUP do the actual work. A formula like this pulls total revenue for a given month from your raw data table:
=SUMIFS(RawData[Amount], RawData[Date], ">="&DATE(2026,1,1), RawData[Date], "<"&DATE(2026,2,1))
Build one row per month down the sheet, and one column per metric across it. By the time you're done you should have a clean grid, twelve rows for the year, five to seven columns for your chosen metrics, with every cell driven by a formula rather than typed by hand. If a number is wrong, you fix the formula once, and every chart downstream corrects itself automatically.
Step 4: build the Dashboard tab
Now the fun bit. Insert a line chart for revenue over twelve months. Insert a bar chart for leads by channel. Add two or three big single numbers using large font, cell borders and a fill colour, current month revenue, year to date total, current conversion rate. Use conditional formatting to turn a cell red if a target is missed and green if it's hit, that single visual cue does more work than a paragraph of commentary ever will. Add a slicer (Insert, Slicer) connected to your table so you or your team can filter by month or channel without touching a formula.
Step 5: automate the refresh
This is where most homemade dashboards quietly die. You build a beautiful thing in a weekend, it's accurate for two weeks, and then life happens and nobody updates the raw data. Set a recurring 20-minute slot in your calendar, every Monday morning, and treat it with the same seriousness as payroll. If you can connect your source data through Power Query (Data tab, Get Data) rather than copy-pasting, do it, because then "refresh" is one click rather than a manual export. I now use AI tools to pull and tidy exports before they land in my raw data tab, which I've written about in detail in the AI tools I use to run my marketing business and what each replaced, and it's cut what used to be an hour of copy-paste down to about ten minutes.
The metrics worth tracking, and the ones to cut
I see a lot of dashboards with 20 or 30 metrics on them, colour-coded to within an inch of their life, and completely unused after the first month. Here's the uncomfortable bit nobody wants to say out loud, a dashboard with too many numbers on it gets looked at less often than one with five. Choice overload is real. When I trimmed my own tracking sheet from 14 metrics down to six, I checked it more, not less, because I stopped dreading opening it.
Keep the ones that change a decision. Cut vanity metrics, page views without conversions attached, follower counts, "engagement" without a pound sign next to it. If a number wouldn't make you pick up the phone or change your ad spend, it doesn't belong on the dashboard tab. It can live in raw data if you want it archived, just don't chart it.
A real example from rebuilding my own business
When I started rebuilding my consultancy after a rough patch, I built myself a dashboard in a single Saturday afternoon, about six hours including two cups of tea and one argument with a VLOOKUP that turned out to be pointing at the wrong sheet entirely. Three tabs, five metrics, nothing clever. Discovery calls booked, calls that turned into proposals, proposals that turned into signed work, monthly revenue, and cash in the bank at month end.
The mistake I made, and I see other people make it constantly, was linking my dashboard tab directly to my bank export instead of routing it through the raw data and calculations tabs first. The first time the bank changed its export format slightly, three charts broke overnight and I didn't notice for eleven days. Eleven days of looking at a dashboard that was quietly lying to me. That's the lesson that made me insist on the three-tab structure with everyone I've built one for since, the extra thirty minutes of setup buys you months of not being misled by your own spreadsheet.
Want AI doing the heavy lifting in your marketing?
I build the systems that handle the boring 80 percent, so you get your week back. Done properly, with the human kept in.
Since then that same sheet, expanded slightly, is still what I open every Monday. It's not pretty by software standards. It's five charts and two big numbers on a white background. But it's told me the truth every single week for over a year, which is more than I can say for some of the paid tools I tried and dropped along the way.
Where Excel runs out of road
I'll say the thing most people writing about this avoid, because they're either selling you a dashboard template or selling you a piece of software. Excel dashboards fail for one reason far more often than any technical one, and it isn't formulas or chart types. It's that nobody updates the raw data. I have built beautifully structured, technically flawless dashboards for clients who stopped feeding them numbers after six weeks because updating a spreadsheet felt like admin rather than running the business. The tool was never the problem. The habit was.
So before you spend a Saturday on charts and conditional formatting, ask yourself honestly whether you or someone on your team will sit down every Monday and put the numbers in. If the answer is no, the fix isn't a fancier dashboard, it's connecting your data source directly through Power Query so the update happens with one click rather than a decision you have to make every single week. And if you're at the point where your data lives across five systems and connecting them yourself feels like more trouble than it's worth, that's the point where bringing in help pays for itself faster than most owners expect, which is exactly the kind of setup work an AI consultant for small business tends to sort out in a day rather than the three weekends it would take you to work it out alone.
Keeping it alive after the first month
Build a rhythm around it, not just a sheet. Every Monday, ten minutes to update raw data. Every month end, five minutes to glance at the dashboard tab and write one sentence about what changed and why. That sentence matters more than people think, because a number moving without a reason attached to it teaches you nothing. Revenue up 12 percent means little on its own. Revenue up 12 percent because you closed two retainer clients from referrals tells you exactly what to do more of.
Review the metrics themselves every quarter too. The five numbers that mattered when you had no recurring revenue are not the five that matter once you've got it. I've swapped out at least one metric on my own dashboard every quarter for the past year, because the business kept changing shape and the numbers needed to keep up with it. A dashboard that never changes is usually a sign the business owner stopped paying attention, not that everything's fine.
If you want a wider view of where AI is making this kind of manual reporting faster across small businesses generally, I cover the tools and updates worth knowing about most weeks in my AI news roundup, and the pattern is consistent, less time spent on data entry, more time spent deciding what to do about what the numbers say.
Frequently asked questions
How long does it take to build an Excel dashboard from scratch?
For a small business with one or two data sources, budget four to six hours the first time, spread across setting up the raw data tab, writing the calculation formulas, and building the charts. Every version after that takes under an hour because you're copying the structure rather than inventing it.
What formulas do I need to know?
SUMIFS and COUNTIFS cover most reporting needs, adding or counting values that meet certain conditions like date range or category. XLOOKUP replaces the old VLOOKUP for pulling matching values across sheets. Beyond those two, you rarely need anything more advanced for a business performance dashboard.
Should I use Excel or Power BI for tracking business performance?
Use Excel if your data sits in two or three places and one person owns updating it. Move to Power BI once you have multiple live data sources, several people needing access, or data volumes that make Excel slow to open. Most businesses under 20 staff never reach that second point.
Why does my dashboard keep breaking or showing wrong numbers?
Almost always it's because charts or formulas are pointing directly at raw, unformatted data rather than a stable calculations tab in between. Separate your workbook into raw data, calculations, and dashboard tabs, and this stops happening because a change in one place no longer breaks the others.
Further reading
Related reading: How to keep track of your entire business with one smart dashboard and How Can You Tell If a Marketing Dashboard Tool Is Worth Paying For.
