How to Build a Monthly Sales Dashboard in Excel (Step by Step)

Most online shops can export their orders to a spreadsheet, but a long list of orders does not tell you much. A dashboard turns that list into a single page that answers the questions you actually ask: Are sales growing? Which products sell best? Is the average order getting bigger? This guide builds one step by step using standard Excel features.

What the dashboard will show

1. Revenue this month and last month
2. Number of orders
3. Average order value (AOV)
4. Revenue by month (trend chart)
5. Top 10 products by revenue
6. Revenue by category or sales channel

Step 1: Prepare the data sheet

Export your orders (most shop systems offer CSV export) and paste them into a sheet called Data. Keep only the columns you need, for example:

If the export has one row per order rather than per product, that works too; you will just not get product-level charts.

Click inside the data and press Ctrl + T to make it a Table. Name it Orders (Table Design → Table Name). Tables grow automatically when you paste new rows, so everything built on them updates.

Add two helper columns:

• Month: =TEXT([@[Order date]],"yyyy-mm")
• Counted: =IF([@Status]="Completed",1,0), so refunded or cancelled orders do not inflate your numbers

Step 2: Create a settings cell

On a new sheet called Dashboard, put the month you want to report in cell B2, for example 2026-10. Every figure on the page will read from this cell, so you can change one value to see any month.

In B3, calculate the previous month:

=TEXT(EDATE(DATEVALUE(B2&"-01"),-1),"yyyy-mm")

Step 3: Key figures (the KPI row)

Revenue this month:

=SUMIFS(Orders[Line total],Orders[Month],B2,Orders[Counted],1)

Orders this month (counting unique order numbers is easier in a PivotTable; for a quick formula in Microsoft 365):

=COUNTA(UNIQUE(FILTER(Orders[Order number],(Orders[Month]=B2)*(Orders[Counted]=1))))

Average order value: revenue ÷ orders.

Repeat each formula with B3 for last month, then add a change % cell: =(This − Last) / Last. Format it with conditional formatting: green when positive, red when negative.

Place these as large numbers across the top of the dashboard. Bigger font, fewer decimals. Three or four key figures are enough.

Step 4: Revenue trend

Insert a PivotTable from the Orders table on a helper sheet:

• Rows: Month
• Values: Sum of Line total
• Filter: Counted = 1

Insert a PivotChart (line or column) from it and move the chart onto the Dashboard sheet. Twelve months of trend is usually more useful than a single month in isolation, because it shows seasonality and growth.

Step 5: Top products

Create a second PivotTable:

• Rows: Product
• Values: Sum of Line total
• Filter: Month (select the report month) and Counted = 1

Then sort descending and use Value Filters → Top 10. A horizontal bar chart of this table makes the best sellers obvious.

Step 6: Revenue by category or channel

A third PivotTable with Category in rows gives a breakdown of where revenue comes from. Use a bar chart rather than a pie chart; bars are easier to compare when there are more than three categories.

Step 7: Add slicers

Select a PivotTable and choose Insert → Slicer for Month and Category. Connect each slicer to all PivotTables (Slicer → Report Connections). Now one click filters every chart on the page.

Step 8: Tidy the layout

• Hide gridlines on the Dashboard sheet (View → untick Gridlines).
• Align charts to a grid and keep the same width.
• Use one or two colours consistently; highlight only what needs attention.
• Put the report month and “last updated” date at the top.
• Remove chart clutter: no 3D effects, minimal axis labels.

Step 9: Refresh each month

Your monthly routine becomes:

1. Export new orders and paste them under the existing rows in the Orders table.
2. Change the month in B2.
3. Data → Refresh All to update the PivotTables.

Five minutes, and the dashboard is current.

What to look for each month

• Revenue down but orders steady? Average order value fell; check discounts and product mix.
• Orders down? Look at traffic and conversion rate.
• One product carrying the month? Good news, but also a risk if it depends on a single supplier or trend.
• A category growing fast? Consider adding related products there.

The takeaway

A dashboard does not need special software. With a clean data table, a few SUMIFS formulas, three PivotTables and some slicers, you get a one-page view of your sales that updates in minutes. The real value comes from looking at it every month and asking what changed and why.

Related kits

Ready-made Excel workbooks with a step-by-step guide:

• Sales & Revenue Tracker templates
• Profit & Loss Statement templates