How to Build a Simple Sales Dashboard Without Add-Ins
A dashboard is three sheets: the data, the calculations, and the view. Keep them separate and the whole thing takes an hour and needs no add-ins, no macros and no PivotTables.
⬇ Download the Sales Dashboard (.xlsx)
Free, no email required. Works in Excel 2016 and later. Prefer Google Sheets? Open a copy in your own Drive.
The guide below builds it from scratch, and spends most of its time on the two decisions that separate a dashboard people use from one they glance at once.
Four tiles, two tables, two charts, one drop-down. No add-ins, no macros, and one colour used throughout.
The three-sheet rule
| Sheet | Holds | Never holds |
|---|---|---|
| Data | One row per transaction, one column per field | Totals, formatting, blank spacer rows |
| Calcs | Every SUMIFS and COUNTIFS, in small tables | Anything anyone is meant to look at |
| Dashboard | Tiles, tables and charts referencing Calcs | Any calculation of its own |
The temptation is to put the SUMIFS directly into the dashboard tiles and skip the middle sheet. Resist it. Charts need their source data in a contiguous block somewhere, so you'd end up building Calcs anyway — just scattered around the edges of the thing you're trying to keep tidy.
The separation also means you can rebuild the view entirely without touching a formula, which you will want to do.
Interactivity from one cell
Put a drop-down on the dashboard listing the months plus an "All year" option. Then convert what it says into a number, once, in a single cell on Calcs:
=IF(Dashboard!$C$4="All year", 0,
IFERROR(MATCH(Dashboard!$C$4, $A$2:$A$10, 0), 0))Every figure on the dashboard then reads that one cell:
=IF($K$1=0,
SUMIFS(Amount, Region, $D2),
SUMIFS(Amount, Region, $D2, MonthNo, $K$1))Zero means no month filter, so total everything. Anything else filters to that month. Both charts, all four tiles and the region table read the same cell, so one drop-down moves the entire dashboard at once.
This is the same principle as keeping a threshold in one referenced cell rather than typing it into every formula — and here it buys you interactivity that most people reach for a macro to get. Building the drop-down itself is covered here.
⚠️ Convert the month name to a number rather than matching on the name directly. Text comparison in SUMIFS is fine until someone opens the file with different regional settings and "March" arrives as something else. A number is a number everywhere.
Headline numbers are not charts
Four figures at the top: total sales, order count, average order, top region. The instinct is to chart them. Don't.
A single number, shown large, is read instantly. The same number as one bar in a four-bar chart takes two seconds and an axis to decode, and the axis is the part nobody reads. If the reader's job is "know the number", the answer is the number.
Total sales =IF($K$1=0, SUM(Amount), SUMIFS(Amount, MonthNo, $K$1))
Orders =IF($K$1=0, COUNT(Amount), COUNTIFS(MonthNo, $K$1))
Average order =IFERROR(IF($K$1=0, AVERAGE(Amount),
AVERAGEIFS(Amount, MonthNo, $K$1)), 0)
Top region =INDEX(Regions, MATCH(MAX(RegionSales), RegionSales, 0))That last one deserves a look: MAX finds the largest number, MATCH finds where it sits, INDEX returns the name beside it. A label derived from the data rather than typed — so it updates when the answer changes, which is exactly when you'd forget to update it by hand.
Format them as tiles: a pale fill, a small grey label above, the number at 18pt or larger. Round to whole units. Nobody makes a decision on the pennies.
The colour decision most dashboards get wrong
Four categories, four bars. Excel's default gives each bar its own colour, and almost every dashboard leaves it that way.
It's the wrong signal. Different colours say these things differ in kind. Four product categories on a sales chart don't differ in the way that matters here — they differ in size, and size is already shown by the length of the bar. The colour adds nothing and costs the reader a legend lookup to confirm what the labels already said.
One hue for the whole series. The chart gets quieter, the comparison gets easier, and you get a spare colour to use when something genuinely does need pointing at.
Keep colour for things that carry meaning: red for a figure below target, one accent bar when you want to draw the eye to a specific month. If everything is coloured, nothing is emphasised.
The same reasoning kills two other defaults:
- No pie charts. People compare angles badly. A four-row table with data bars says the same thing and is readable to the pound.
- No second axis. Two measures on one chart with different scales is the most misread chart there is — the crossing point is an artefact of the scales you chose. Two charts, or index both to a common base.
Bars inside cells, no chart required
The region and rep tables use conditional formatting data bars: select the numbers, then Conditional Formatting → Data Bars. Gradient and solid fills are both offered; solid reads slightly more precisely, gradient sits more quietly behind the numbers, and either is fine.
The setting that isn't optional is under More Rules: change the minimum from "Automatic" to Number, 0. Automatic scales the shortest bar from the smallest value in the range, so your smallest row renders as almost nothing regardless of what it actually is — which misstates the comparison the bars exist to make.
These cost nothing, need no chart object, and stay attached to the numbers they describe. For a short breakdown they're better than a chart — which is why the regions and reps here are tables with bars, and the two chart objects are spent on things the tables don't already show.
That's a rule worth applying generally: never show the same breakdown twice in two forms. A table and a chart of the same four numbers is one of them wasted.
Excel also has real sparklines — Insert → Sparklines → Line — which draw a tiny trend inside a single cell. One per region, showing nine months of history beside the current figure, is the highest information density available anywhere in Excel. Google Sheets has them as a formula: =SPARKLINE(B2:J2).
Making the sheet look built rather than assembled
- Turn off gridlines. View → Gridlines. This single change does more than anything else on the list.
- One font, three sizes. Title, labels, numbers. Nothing else.
- Set the column widths deliberately. Narrow spacer columns between blocks beat merged cells, which break sorting and copying forever.
- Align everything to a grid. Tiles the same height, charts the same width, edges lined up.
- Give charts titles that say the finding, not the field name. "Sales by month" is a label; "Sales peaked in March" is information — though only write that if it stays true when the filter moves.
- Delete the chart legend when there's one series. The title already says what it is.
Formulas or a PivotTable?
Honest answer: PivotTables are better at some of this, and this article isn't pretending otherwise.
| Use a PivotTable when | Use formulas when |
|---|---|
| You're exploring and don't know what you're looking for | The layout is fixed and you'll produce it every month |
| The grouping keeps changing | Someone else has to open it and not break it |
| You want slicers with no formula work | You need the exact figures in exact cells for a report |
| The data has tens of thousands of rows | You want to see how every number was arrived at |
A PivotTable moves when you click it, which is what makes it good for exploring and bad as the foundation for a fixed layout — one stray drag and the cell your chart referenced holds something else.
Does this work in Google Sheets?
Yes. SUMIFS, COUNTIFS, AVERAGEIFS, INDEX, MATCH, MAX, data validation, data bars and charts all have direct equivalents.
Sheets is better in two places: SPARKLINE is a formula rather than a menu item, and the chart editor is easier to get a clean result from. It's worse in one — data bars are under Format → Conditional formatting → Colour scale, which is a gradient rather than a bar.
When it goes wrong
Every tile shows the same number whatever month you pick
The formulas reference the drop-down cell's text rather than the converted number, or the converted cell isn't recalculating. Check that cell first — it's the single point of failure by design.
The charts don't move with the filter
They're pointing at the Data sheet instead of the Calcs tables. Charts should only ever reference Calcs.
The chart lost its formatting after a refresh
Excel reapplies theme colours when the series changes. Set the fill on the series rather than on individual points, and it survives.
Data bars make the smallest value invisible
The minimum is set to Automatic. Change it to Number, 0.
Adding new rows doesn't update anything
The ranges are fixed and the new rows fall outside them. Either build the ranges with room to spare, or make the Data sheet a Table with Ctrl+T so ranges grow on their own.
It's slow
SUMIFS over whole columns is expensive when repeated across a dashboard. Reference a bounded range, and if it's still slow you've outgrown formulas.
Common questions
How much data can this handle?
Comfortably to about 50,000 rows with bounded ranges. Beyond that, Power Pivot or a PivotTable over the data model, and past a few hundred thousand it isn't a spreadsheet job at all.
Can I add more filters — by region as well as month?
Yes. A second drop-down, a second converted cell, and a third condition in each SUMIFS. Past two or three filters the formulas get unwieldy and a PivotTable with slicers is the better tool.
How do I stop people breaking it?
Protect the Dashboard sheet leaving only the drop-down cell unlocked, and hide the Calcs sheet. Both are under Review and take a minute.
Can it refresh automatically from a system?
Not with formulas. That's Power Query — Data → Get Data — which connects to a file, folder or database and refreshes on demand. It sits underneath everything here without changing any of it.
Should the dashboard show targets?
If you have them, yes, and show the gap rather than both numbers. "12% behind" is read faster than two figures the reader has to subtract.
The short version
- Three sheets: Data, Calcs, Dashboard. The middle one is not optional.
- One drop-down drives one cell; every figure reads that cell.
- Convert the month name to a number — text comparisons travel badly.
- Headline numbers are tiles, not charts.
- One hue per chart. Different colours claim a difference in kind that isn't there.
- No pie charts, and never two axes on one chart.
- Data bars set to a fixed minimum of 0, or the smallest row reads as nothing.
- Gridlines off. It does more for the look than anything else.
- PivotTables are better for exploring; formulas are better for a layout that must not move.
Next steps: the totals behind every tile are SUMIFS and COUNTIFS, the filter is a drop-down list, and the Top region tile is INDEX MATCH. If your source data came out of another system, clean it before you build on it — a dashboard over unclean data is a confident wrong answer.
Comments
Post a Comment