Every Monday you open four different reports, scroll through two spreadsheets, and check your bank app, just to answer one question: how are we doing? By the time you've pieced it together, the morning is gone and you're not totally sure the numbers agree. A simple dashboard puts the answer on one page. You open it, glance at it, and know where to focus… in about thirty seconds.
You don't need special software or a data team. Here's how to build one in Excel.
What makes a good small business dashboard
A dashboard isn't a report with every number you have. It's a short list of the numbers that drive decisions, shown clearly. Good dashboards:
- Fit on one screen
- Show four to six key numbers, not forty
- Compare to something, like last month or a goal
- Update with little or no manual work
Step 1: Pick your key numbers
Start by writing down the questions you ask most often. Then turn each into a metric. Common ones:
- Revenue this month vs. last month
- Number of orders or jobs
- Average order value (revenue divided by orders)
- Top products or services
- Expenses by category
- Open invoices (money owed to you)
Pick four to six. You can always add more later, but starting small keeps it useful.
Step 2: Set up your workbook
Use three sheets:
- Data: your raw export, pasted in or connected. Turn it into a Table with Ctrl+T and name it, like
tblSales. - Calc: the formulas and PivotTables that summarize the data.
- Dashboard: the clean page you actually look at.
Separating these keeps the dashboard tidy and makes it easier to update.
Step 3: Build the calculations
On the Calc sheet, set up a cell for the current month's start date and the next month's start date. Then use SUMIFS and COUNTIFS:
- Revenue this month:
=SUMIFS(tblSales[Amount], tblSales[Date], ">="&B1, tblSales[Date], "<"&B2) - Orders this month:
=COUNTIFS(tblSales[Date], ">="&B1, tblSales[Date], "<"&B2) - Average order value: revenue divided by orders, wrapped in IFERROR in case there are no orders yet
Repeat the same formulas for last month using different date cells, so you can compare.
For top products, insert a PivotTable from tblSales with Product in Rows and Amount in Values, sorted largest to smallest. Use a filter or slicer for the current month.
Step 4: Design the dashboard page
On the Dashboard sheet:
- Turn off gridlines (View tab) for a cleaner look.
- Create "cards" for your key numbers. Each card is a few merged or bordered cells with a label ("Revenue this month") and a large number linked to the Calc sheet, like
=Calc!C4. - Add a comparison under each number, like "vs. last month: +8%," calculated as
=(this month - last month) / last month. - Add one or two charts. A line or column chart of monthly revenue shows trends. A bar chart of top products shows what's selling.
Keep the colors simple. One accent color plus gray works well.
Step 5: Add visual signals
Conditional formatting turns numbers into signals:
- Make the comparison text green when positive and red when negative
- Use data bars in a small table of expense categories
- Highlight open invoices older than 30 days
The goal is to see what needs attention without reading every number.
Step 6: Make updating easy
The best dashboard is one you'll actually update. To keep it low effort:
- Paste new data at the bottom of the Data table, or replace it with a fresh export
- Change the month date cell on the Calc sheet
- Click Data > Refresh All to update PivotTables
If your exports always look the same, consider Power Query to load and clean them automatically each time.
Example: a small HVAC company
An HVAC company owner builds a dashboard with five cards: jobs completed this month, revenue, average job value, open invoices, and repeat customers. Below are two charts: revenue by month for the past year, and revenue by service type.
On Monday mornings she pastes in the latest export, refreshes, and in seconds sees that maintenance plans are growing while repair jobs are steady. That shapes what she promotes next month.
Common mistakes to avoid
Too many numbers. If everything is on the dashboard, nothing stands out.
No comparison. "$42,000 in revenue" means little without last month or a goal beside it.
Hard-coded values. Typing numbers onto the dashboard defeats the point. Link everything to formulas.
Fancy charts that confuse. 3D pie charts and busy designs make numbers harder to read. Stick with simple column, bar, and line charts.
Forgetting to refresh. Add a "Last updated" cell so you always know how current the numbers are.
Start with version one
Your first dashboard doesn't need to be perfect. Build a rough version this week with three numbers and one chart. Use it for a month, then improve it based on what you actually look at.
Learn the skills behind great dashboards
Dashboards bring together Tables, SUMIFS, PivotTables, charts, and conditional formatting. Our Advanced Excel for Business Analysis course teaches each of these step by step and shows how to combine them into a reusable workbook for your business.