Excel Dashboard Templates: 6 Layouts for Small Businesses and How to Build Each One
Six Excel dashboard templates for small businesses (sales, finance, cash, inventory, projects, marketing), what each should show, and a step-by-step build: data table, calculation sheet, KPI cards, charts, slicers and a design checklist, with a worked sales example.
Quick answer: the most useful Excel dashboard templates for a small business are a sales dashboard, a financial (P&L) dashboard, a cash dashboard, an inventory dashboard, a project status dashboard and a marketing dashboard. Every one of them uses the same three-layer build: a clean data table, a calculation sheet with SUMIFS/COUNTIFS or PivotTables, and a dashboard sheet that only displays KPI cards, a few charts and alerts. Pick the layout that answers the decision you make most often, build it once, and refresh it with new data.
Below are the six layouts with the metrics and charts each one needs, followed by a step-by-step build of a sales dashboard. Example figures are illustrative.
Six dashboard layouts at a glance
| Dashboard | Question it answers | KPI cards | Charts | Data source |
|---|---|---|---|---|
| Sales | Are we selling more, and what? | Revenue, orders, average order value, % of target | Monthly revenue line, top products bar, sales by channel | Order or POS export |
| Financial (P&L) | Are we profitable? | Revenue, gross margin %, operating expenses, net profit | Revenue vs expenses columns, expense breakdown bar | P&L template, expense tracker |
| Cash | Will we have enough cash? | Cash balance, receivables overdue, payables due in 30 days, runway in months | 13-week cash balance line, receivables aging bar | Bank balance, invoice and bill lists |
| Inventory | What will run out, what is stuck? | Stock value, items below reorder point, turnover, days of cover | Top items by value, slow movers list | Inventory template |
| Project status | Are projects on time and budget? | Progress vs plan, overdue tasks, budget used, open risks | Progress bars by project, burn-up chart | Project management template |
| Marketing | Which channels bring customers? | Spend, leads, cost per lead, conversion rate, CAC | Spend vs leads by channel, monthly trend | Ad platform exports, CRM |
Start with one. A single dashboard that is updated every week is worth more than six that are abandoned after a month.
The three-layer build (works for every layout)
- Data: one Excel table per source, one row per transaction, no merged cells or subtotal rows. Add helper columns such as Month:
=EOMONTH([@Date],-1)+1. - Calculations: a hidden or separate sheet where every KPI and chart series is calculated. This keeps formulas in one place and makes them easy to audit.
- Dashboard: references only. No typed numbers, no raw data.
Build example: a sales dashboard
Step 1: The data table
Export orders into a table named Sales with Date, Order ID, Customer, Product, Category, Channel, Quantity, Revenue and Cost. Add a Month column and, if you track targets, a separate Targets table with Month and Target.
Step 2: The period selector
On the Dashboard sheet, cell C2 holds the selected month (a dropdown fed by a list of months). The previous month is =EDATE(C2,-1) and the same month last year =EDATE(C2,-12). For a quarter view, store the quarter start in C2 and use EDATE(C2,3) as the end boundary.
Step 3: Calculations
| KPI | Formula |
|---|---|
| Revenue | =SUMIFS(Sales[Revenue],Sales[Month],Dashboard!$C$2) |
| Orders | =COUNTIFS(Sales[Month],Dashboard!$C$2) (one row per order) or =ROWS(UNIQUE(FILTER(Sales[Order ID],Sales[Month]=Dashboard!$C$2))) |
| Average order value | =IFERROR(Revenue/Orders,"") |
| Gross margin % | =IFERROR(1-SUMIFS(Sales[Cost],Sales[Month],Dashboard!$C$2)/Revenue,"") |
| % of target | =IFERROR(Revenue/XLOOKUP(Dashboard!$C$2,Targets[Month],Targets[Target]),"") |
| Top 5 products | =TAKE(SORTBY(UNIQUE(Sales[Product]),SUMIFS(Sales[Revenue],Sales[Product],UNIQUE(Sales[Product]),Sales[Month],Dashboard!$C$2),-1),5) |
Dynamic array functions (UNIQUE, FILTER, SORTBY, TAKE) require Excel 365 or Excel 2021 and later; in older versions, use a PivotTable with a Top 10 filter instead.
Worked example: third quarter
| Month | Revenue | Orders | Average order value |
|---|---|---|---|
| July | $42,000 | 1,120 | $37.50 |
| August | $45,500 | 1,190 | $38.24 |
| September | $51,800 | 1,310 | $39.54 |
| Q3 | $139,300 | 3,620 | $38.48 |
Check: 42,000 + 45,500 + 51,800 = 139,300; 1,120 + 1,190 + 1,310 = 3,620; 139,300 / 3,620 = $38.48. Against a Q3 target of $135,000, the quarter reached 103.2%. September grew 13.8% over August, driven by both more orders and a higher average order value. Those three sentences are what the dashboard should make obvious at a glance.
Step 4: KPI cards
Each card is a small block of merged-looking cells (use Center Across Selection instead of merging): a label in small grey text, the value in a large bold font, and a comparison line. The comparison uses a custom number format such as ▲ 0.0%;▼ 0.0%;0.0% and conditional formatting for color. Remember that for costs, an increase is bad news: set the colors per card.
Step 5: Charts
- Trend: a line chart of revenue for the last 12 months, with target as a second line.
- Ranking: a horizontal bar chart for top products, sorted descending.
- Mix: a 100% stacked column for sales by channel, which is easier to compare month to month than a pie chart.
- Sparklines (Insert > Sparklines) next to each KPI card for a tiny trend.
Base every chart on a range in the calculation sheet, not on the raw table, so the chart updates when the selected month changes.
Step 6: Interactivity
If you build with PivotTables, add Slicers for channel and category and a Timeline for dates, then use Report Connections so one slicer filters every PivotTable. If you build with formulas, add dropdown cells for channel and category and include them as extra criteria in every SUMIFS.
Design checklist
- One screen, no scrolling; five to eight KPIs at most.
- Most important number top left.
- Two or three colors; red only for problems.
- Gridlines off (View > uncheck Gridlines), no 3D charts, no chart borders.
- Large numbers in thousands with a custom format such as
$#,##0,"K". - A “data as of” date and the data source in the footer.
- Formulas locked, with only the selector cells editable; see how to protect formulas in Excel.
Keeping the dashboard fast
- Refer to table columns (
Sales[Revenue]) instead of entire columns (A:A). - Avoid volatile functions like INDIRECT and OFFSET on large data; prefer INDEX and XLOOKUP.
- When the data grows into hundreds of thousands of rows, load it with Power Query and summarize with PivotTables or the Data Model.
- Archive old years to a separate file and keep summary rows for history.
Common mistakes
- Typing numbers into the dashboard: they never update and quietly go wrong.
- No comparison: a number without target, last month or last year tells you little.
- Too many charts: if everything is highlighted, nothing is.
- Messy source data: inconsistent product or channel names split totals. Use dropdowns in the source.
- No owner: someone must refresh the data on a fixed day each week or month.
FAQ
PivotTables or formulas?
PivotTables are faster to build and handle large data well. Formulas give full control over layout and make targets and alerts easy. Many good dashboards use PivotTables for heavy lifting and formulas for the final cards.
Can I build these in Google Sheets?
Yes. SUMIFS, COUNTIFS, FILTER, UNIQUE, SORT and SPARKLINE all exist, and Google Sheets has slicers. Looker Studio is an option when you need to combine several sources.
When should I move to Power BI?
When data comes from many systems, exceeds what Excel handles comfortably, or needs to be shared with many people with access control. The three-layer thinking stays the same.
More guides: Spreadsheet & Dashboard Design.