Excel Sales Dashboard: Build One That Updates Itself
Build an Excel sales dashboard that keeps itself current: one flat sales table, SUMIFS KPIs, XLOOKUP targets and FILTER drill-downs that grow with the data.
Introduction
Most sales dashboards are a one-off: someone builds twelve summary tabs, the data changes shape a month later, and the file is abandoned. This guide builds the other kind. One flat transaction table feeds a Dashboard sheet where every panel is a single formula, every figure can be drilled back to the rows behind it, and the dimension lists rebuild themselves when a new region or rep appears. The running example is a 13-line quarter worth $12,370, so you can check your own results as you follow along.
Prerequisites
- Comfort writing SUMIF or COUNTIF and filling a formula down
- Knowing how to convert a range into an Excel Table with Ctrl+T
- Excel 2021 or Microsoft 365 for the dynamic-array sections (the SUMIFS panels work in Excel 2007 and later)
1Start With One Flat Sales Table, Not Twelve Summary Tabs
Every dashboard that survives contact with real work sits on a single heartless flat table: one row per sale, no subtotals, no merged cells, no spacer rows. Everything else in the workbook is a view of that one table. The reason is arithmetic rather than tidiness - SUMIFS and FILTER can only see rows that are really there, and a subtotal row living inside your data gets counted as a sale. Convert the range to an Excel Table with Ctrl+T before you write a single formula: the table name becomes a word you can type, and rows you add next month are inside every reference automatically.
Lay out seven columns and stop
Date, Region, Rep, Product, Units, Unit Price, Revenue. One row per sale line, nothing else. Name the sheet tab Sales and resist adding a Notes column - commentary belongs anywhere but inside the data.
Date | Region | Rep | Product | Units | Unit Price | RevenueMake Revenue a calculated column inside the Table
Select the range, press Ctrl+T, tick 'My table has headers', then type this once in the first Revenue cell. Excel fills the whole column, and every row you add later gets it too.
=[@Units]*[@[Unit Price]]Name the Table Sales
On the Table Design tab, overwrite Table1 with Sales. From here on, formulas read Sales[Revenue] instead of $G$2:$G$14, so inserting a column can never silently shift your totals.
Sales[Revenue]Example
=SUM(Sales[Revenue])The grand total of the 13 sale lines used throughout this guide. Notice what is missing: a range. Because Sales is a Table, this formula keeps working when the export grows to 13,000 rows, and it is the number every reconciliation check later in this guide ties back to.
Never leave a blank row inside a Table. A blank row ends the table as far as Excel is concerned, and every SUMIFS range stops one row after it.
Keep formatting - colours, column widths, number formats - on the Dashboard sheet. The data sheet should be dull enough that you could paste a fresh export straight over it without losing anything.
If your export contains subtotal rows, delete them before the data enters the Table instead of filtering them out afterwards.
2The Four Numbers a Sales Dashboard Actually Needs
A dashboard is not a data dump; it answers four questions - how much, from where, in which period, and against what target. SUMIFS answers three of them, because it takes a sum range followed by any number of criteria pairs. Resist building a twelve-panel board on day one: total revenue, revenue by region, revenue by month and target attainment are the panels people actually open the file for. Each one below is a single formula you can read out loud.
Revenue for a single month
Two criteria on the same column bracket the month. Each date bound is an operator in quotes joined to a real date with an ampersand - never type the date inside the quotes, or it becomes text and matches nothing.
=SUMIFS(Sales[Revenue], Sales[Date], ">="&DATE(2026,3,1), Sales[Date], "<="&DATE(2026,3,31))Deal count, so a big number is never misleading
A $3,900 month made of two orders is a different business from one made of forty. COUNTIFS uses the same criteria syntax but has no sum range, so the pairs start immediately. North returns 4.
=COUNTIFS(Sales[Region], $A5)Two criteria on different columns
Add pairs to intersect dimensions. North plus Widget returns 1,625 - the product is clearly not evenly distributed across regions, which is exactly the kind of thing a totals-only dashboard hides.
=SUMIFS(Sales[Revenue], Sales[Region], $A5, Sales[Product], $B$3)Example
=SUMIFS(Sales[Revenue], Sales[Region], $A5)Criteria arguments always arrive in pairs: the range to test, then the value to match. Here Sales[Region] is tested against the region name in A5 and the matching Sales[Revenue] values are added. Filled down over the region list it produces 3,665 / 3,900 / 2,850 / 1,955 - which sums to 12,370, exactly the figure from section 1.
SUMIFS is spelled and behaves identically in Excel and Google Sheets. The only thing that does not travel is the table syntax: Sales[Revenue] has no equivalent, so in Sheets use a named range or an A1 range such as Sales!G:G.
Criteria are not case-sensitive. "north" and "North" match the same rows, which is convenient until two exports disagree about capitalisation and you never notice.
A SUMIFS criterion that returns 0 is usually a trailing space in the data, not a broken formula. Test one cell with =LEN($A5) against the same cell in the source to confirm.
3Slice by Rep and Product Without Rewriting a Single Formula
The real test of a dashboard is what happens when someone asks for a different cut. If the answer is 'give me twenty minutes', the dashboard has failed. Write the criteria so that they read from cells, and a pair of drop-down lists gives you every combination of region, rep, product and month for free. Wildcards extend the same idea to product families, so Wid* quietly covers Widget, Widget Pro and Widget Mini without a single new formula.
Drive the panel from one cell
Put a data-validation drop-down in B1 holding the region, then let the criterion follow that cell. Changing the drop-down recalculates every panel that references it.
=SUMIFS(Sales[Revenue], Sales[Region], $B$1)Average deal size for one rep
Divide the conditional sum by the conditional count. Because both formulas use the same criteria they can never drift apart, and Ben's four deals average 975 each.
=SUMIFS(Sales[Revenue], Sales[Rep], "Ben")/COUNTIFS(Sales[Rep], "Ben")Combine a drop-down with a date window
Region from the drop-down, month from hard-coded bounds. North in March alone is 1,320 - a useful sanity check that the drop-down and the date logic are both alive.
=SUMIFS(Sales[Revenue], Sales[Region], $B$1, Sales[Date], ">="&DATE(2026,3,1), Sales[Date], "<="&DATE(2026,3,31))Example
=SUMIFS(Sales[Revenue], Sales[Product], "Wid*")The asterisk matches any run of characters after Wid, so every Widget variant is caught in one total. That is 58% of the $12,370 quarter - the sort of concentration figure that usually decides next quarter's marketing budget. The question mark matches exactly one character, and to search for a literal asterisk you write ~*.
Wildcards work in SUMIFS, SUMIF, COUNTIF and COUNTIFS, in both Excel and Google Sheets. They apply to text criteria only - a numeric criterion such as ">1000" is never treated as a pattern.
Criteria pairs must all be the same height. Mixing a whole column with a 13-row range is the classic cause of a #VALUE! that appears only in some rows.
Once the criteria list passes about four pairs the formula stops being auditable. That is the point to move to a PivotTable or, on a Sheets-only team, to a single QUERY.
4Drill Down and Audit: XLOOKUP Targets and FILTER Detail
A number nobody can trace is a number nobody trusts. Two functions close that gap. XLOOKUP pulls the comparison figure - a quota, a target, last year's number - from a small reference table, and FILTER spills the actual rows behind a KPI so a sceptical sales manager can check them without asking you. Keeping targets in their own two-column table instead of typing them into the formula means a revised quota is a one-cell edit rather than a hunt through the workbook.
Look up a target instead of typing it
Targets lives on its own sheet with Rep in column A and the quarterly quota in column B, formatted as a Table named Targets. XLOOKUP finds the rep and returns the quota, and the fourth argument replaces the #N/A you would otherwise get for an unknown name.
=XLOOKUP($B$2, Targets[Rep], Targets[Q1 Target], 0)Attainment as a percentage
The same lookup, used as a divisor. An XLOOKUP in the denominator recalculates when the quota changes; a hard-typed 4000 does not. Alice reaches 91.6% of her 4,000 target.
=SUMIFS(Sales[Revenue], Sales[Rep], $B$2)/XLOOKUP($B$2, Targets[Rep], Targets[Q1 Target], 0)Sort the drill-down so the newest lines lead
Wrap FILTER in SORT. Column 1 is the date, 1 means ascending and -1 descending; point it at 2 to order by revenue instead.
=SORT(FILTER(Sales, Sales[Region] = $B$1, "No rows"), 1, -1)Example
=FILTER(Sales, Sales[Region] = $B$1, "No rows")With North selected, the filter returns the four North sale lines, and they sum to 3,665 - precisely what the SUMIFS panel reports. That is your audit trail: any KPI that cannot be reproduced by its own spilled rows is hiding something. The third argument is what Excel shows when nothing matches; leave it out and Excel throws #CALC!.
FILTER needs empty cells below and to the right. Anything in its path produces #SPILL! and nothing spills at all - a very common cause of a 'broken' dashboard that is really just a stray note in column H.
The two platforms disagree when nothing matches. Excel returns #CALC! unless you supply the third argument; Google Sheets has no if_empty argument at all and returns #N/A instead, so wrap it in IFERROR or IFNA there.
XLOOKUP needs Excel 2021 or Microsoft 365 - in Excel 2019 and earlier it returns #NAME?. Google Sheets does support XLOOKUP, so the Sheets version of this panel is a straight copy.
5Make It Self-Maintaining, Then Prove It Balances
The last two jobs are the ones people skip. First, the dimension lists should generate themselves, so a new region or rep appearing in the data does not require a dashboard rebuild. Second, the dashboard must prove that it balances: the sum of every panel equals the sum of the source table, always. Neither takes more than a minute to add, and together they are the difference between a file you still trust next year and one you quietly stop opening.
Let the region list build itself
Replace the typed region list with a spilled dynamic list. It sorts alphabetically, and picks up a new region the moment one appears in the sales table.
=SORT(UNIQUE(Sales[Region]))Reconciliation check
Subtract the sum of the region panels from the sum of the source table. This cell must read 0 forever. A non-zero answer means either a subtotal row that survived the export or a region name carrying a stray space that no criterion matches.
=SUM(Sales[Revenue])-SUM($B$5:$B$8)Count the products the dashboard covers
UNIQUE returns the distinct product list and COUNTA counts how many there are. If this number jumps unexpectedly, someone has started typing product names differently.
=COUNTA(UNIQUE(Sales[Product]))Example
=SORT(HSTACK(UNIQUE(Sales[Rep]), SUMIFS(Sales[Revenue], Sales[Rep], UNIQUE(Sales[Rep]))), 2, -1)UNIQUE produces the four rep names, SUMIFS totals each one inside a single formula, HSTACK glues the two columns together and SORT orders the block by the revenue column descending. Add a rep next month and the whole block grows by itself - no filling down, no rows to remember.
UNIQUE, SORT and HSTACK are Microsoft 365 and Excel 2021+ only. Google Sheets has UNIQUE and SORT but no HSTACK, so write the two columns as an array literal instead: {UNIQUE(...), SUMIFS(...)}.
On a Google Sheets-only team, one QUERY formula can replace most of this sheet: QUERY(Sales!A:G, "select B, sum(G) group by B label sum(G) ''", 1) returns the region totals in a single cell.
Open the workbook on the Dashboard sheet, or hide the data sheet, so nobody types over the source table by accident. An entire afternoon of dashboard work can be undone by one optimistic edit in column G.
Functions Used
SUMIFS
SUMIFS adds values that satisfy multiple criteria across one or more ranges.
SUMIF
SUMIF adds up all numbers in a range that meet a specified condition. It's the go-to function for conditional summing — like totaling sales for a specific region or summing expenses above a threshold.
XLOOKUP
XLOOKUP is the modern replacement for VLOOKUP. It searches a range or array for a value and returns the corresponding item from a second range or array. It supports exact and approximate matching, wildcards, and can search in any direction.
FILTER
FILTER extracts rows from a range that meet a condition, returning a dynamic array of matching results. It's the array-era replacement for complex IF/INDEX combinations.
COUNTIFS
COUNTIFS counts cells that satisfy multiple criteria across one or more ranges.
SUM
SUM adds all the numbers in a range of cells and returns the total.
SORT
SORT returns a dynamic array of a range or array sorted by one of its columns or rows, in ascending or descending order.
UNIQUE
UNIQUE returns the distinct values from a range or array, optionally returning only values that appear exactly once, as a dynamic array.
COUNTA
COUNTA counts the number of non-empty cells within a specified range, regardless of the cell content.
DATE
DATE builds a date serial number from separate year, month, and day components, and automatically rolls over out-of-range values such as month 13 into the next year or day 32 into the following month.
Related Guides
Excel Expense Tracker
Build an Excel expense tracker from scratch: log spending on one sheet, then use SUMIF, SUMIFS, SUM and TODAY for category totals and month-to-date spend.
Excel Budget Template
Build an Excel budget template from scratch: structure your sheets, total spending by category with SUMIF, flag overspending with IF, and track averages.
How to Use Excel Formulas: From the First Equals Sign to a Real Report
Learn how to use Excel formulas step by step: the equals sign, cell references, SUM, IF and VLOOKUP, plus the errors beginners hit and how to fix them.
Summary
A sales dashboard worth keeping is one flat Table of transactions plus a Dashboard sheet where every panel is one readable formula. SUMIFS and COUNTIFS turn the table into region, product and period KPIs; XLOOKUP pulls targets from a small reference table so a revised quota is a one-cell edit; FILTER proves each number by spilling the rows behind it. Finish with SORT and UNIQUE so the dimension lists grow themselves, and a reconciliation cell that subtracts the panel totals from the source total and must read 0 forever. Excel and Google Sheets agree on all of the formulas here except the Table syntax and HSTACK - swap those for named ranges and an array literal and the same dashboard runs on either platform.
Next Steps
- Add a chart driven by the region panel and pin it to the Dashboard sheet so the shape of the quarter is visible at a glance
- Build the same rep leaderboard with a PivotTable and compare the maintenance cost against the HSTACK/UNIQUE version
- Extend the month KPI into a rolling twelve-month column so seasonality becomes obvious before the year is out