Build a Simple Sales Dashboard in Excel

A sales dashboard in Excel is one sheet that shows how sales are going at a glance: a few headline numbers, a chart and a small table, all driven by one selector. You can build a simple one in under an hour with SUMIFS, COUNTIFS and a chart, or with a pivot table and slicers. This guide builds both on the same small set of orders, so you can pick the method that suits you.

What goes on a simple sales dashboard?#

A useful dashboard answers a few questions quickly and leaves the detail to the data sheet. For a small team, that usually means:

• A selector, such as a region drop-down, so one person can look at their own numbers.
• KPI cards: total revenue, number of orders and average order value. A KPI, or key performance indicator, is a number that shows whether things are on track.
• A trend: revenue by month, as a column or line chart.
• A breakdown: revenue by rep or product, as a small table.

Resist adding everything. Each number on the dashboard should lead to a decision or a question.

Step 1: Put the orders in a table#

Keep the raw data on its own sheet, called Data, with one row per order and no blank rows or merged cells:

Click inside the data and press Ctrl+T (Mac: ⌘T) to turn it into an Excel table. Tables grow when you add rows, so formulas and pivot tables that point at them pick up new orders by themselves. For a fuller walk-through of reporting on order data, see the guide to building a simple sales report.

Step 2: Add a region selector#

Put the selector at the top of a new Dashboard sheet. Type "Region" in G1, then make H1 a drop-down:

1. Select H1 and go to Data > Data Validation.
2. In Allow, choose List.
3. In Source, type All,North,South,East and click OK.

Now every formula on the dashboard will read H1. Change it, and the whole sheet updates. The examples below use the cell references from the Data sheet for clarity; in your file, add the sheet name (for example Data!E2:E8) or use the table's column names.

Step 3: Build the KPI cards with SUMIFS and COUNTIFS#

Each KPI is one formula that reads the selector. With North in H1:

SUMIFS adds the revenue for rows that match every condition, and COUNTIFS counts them. The average order formula assumes revenue is in H2 and orders in H3, with the other KPIs below them. Wrap it as =IFERROR(H2/H3,0) so a region with no orders shows 0 instead of #DIV/0!. The SUMIFS function guide explains each argument, and the guide to COUNTIFS and SUMIFS with multiple conditions has more examples.

Make "All" work#

As written, "All" matches no region and every KPI shows 0. Swap the criteria for an IF that turns "All" into the wildcard *, which matches any text:

=SUMIFS(E2:E8,B2:B8,IF(H1="All","*",H1))

With All selected, this returns 8,250, the total for every region. Use the same IF in each KPI formula. For targets, progress bars and percent change next to these numbers, see how to build a KPI summary sheet.

Step 4: Add revenue by month and a chart#

Build a small table that the chart can read. Type a header in G8, the first day of each month as real dates in G9:G11 (1 Jan, 1 Feb and 1 Mar 2026), then in H9:

=SUMIFS($E$2:$E$8,$A$2:$A$8,">="&G9,$A$2:$A$8,"<="&EOMONTH(G9,0),$B$2:$B$8,IF($H$1="All","*",$H$1))

Fill it down. With All selected, the results are 3,450 for January, 3,900 for February and 900 for March. EOMONTH(G9,0) returns the last day of the month in G9, so each row adds one month. The $ signs keep the data ranges and the selector fixed as you fill.

To chart it, select G8:H11 (including the header row) and go to Insert > Recommended Charts, then pick a clustered column chart. Give it a clear title, such as "Revenue by month", and delete the legend if there is only one series.

Step 5: Or build it with a pivot table and slicers#

A pivot table builds the same dashboard without formulas. A pivot table is a summary table you build by dragging fields into place.

1. Click inside the data table and go to Insert > PivotTable. Choose a new worksheet.
2. Drag Date to Rows. Recent versions of Excel group it by month for you; if not, right-click a date and choose Group, then Months.
3. Drag Revenue to Values. It shows as Sum of Revenue.
4. With the pivot table selected, go to PivotTable Analyze > PivotChart to add a chart.
5. Go to PivotTable Analyze > Insert Slicer and tick Region. A slicer is a panel of buttons that filters the pivot table and its chart.

Click North on the slicer and the table and chart show North only. Add a second pivot table for revenue by rep, and connect the same slicer to both with Report Connections. After you add new orders, refresh with Data > Refresh All.

Pivot tables are faster to build, while formulas give you more control over layout and labels. Many dashboards mix both.

Tips for a dashboard people will read#

Small design choices make a big difference:

• Put the KPIs at the top left, where people look first, and the detail below.
• Format numbers simply: thousands separators, no decimals for revenue, one decimal for percentages.
• Turn off gridlines on the dashboard sheet (View, untick Gridlines) so it looks like a report.
• Label the selector and the date range so a printout still makes sense.
• Keep it to one screen. If it needs scrolling, split it.

If you track deals rather than closed orders, the sales pipeline tracker is a better starting point. Building a dashboard like this also covers most of the intermediate Excel skills employers test for.

Practise it#

The formulas behind a dashboard are skills you can drill. Practise COUNTIFS and SUMIFS in a live spreadsheet to build multi-condition totals like the KPI cards, then take the pivot tables lesson to build the no-formula version. Each step is checked instantly. Try the region total exercise on this page first.