Sales Dashboard with KPIs, Slicers & AI Insights

A sales dashboard turns raw order data into clear numbers and charts that anyone in the business can read. In this tutorial you build one in Excel and use Microsoft Copilot and ChatGPT to write the formulas and explain the results.

Table of Contents

• Project Overview
• Features
• Prerequisites
• Folder Structure
• Step-by-Step Development
• Complete Source Code
• Running the Project
• Testing
• Common Errors
• Improvements
• Challenge Exercises

Project Overview

• What it does: It reads a table of sales orders and shows total revenue, units sold, average order value, and breakdowns by region and product.
• Where it is used: Sales teams, small businesses, and monthly review meetings use it to track performance.
• What you learn: SUMIFS, XLOOKUP, PivotTables, charts, slicers, and how to use AI to write and fix Excel formulas.

Features

• KPI cards for total sales, units, and average order value
• Revenue breakdown by region and by product
• Interactive slicers to filter by month or region
• A trend chart showing sales over time
• AI-written summaries of what the numbers mean

Prerequisites

• Microsoft Excel (Microsoft 365) with the Copilot license if you want in-app AI
• A free or paid ChatGPT account (used to generate and explain formulas)
• Basic knowledge of typing in cells and creating a table
• The sample data file or any list of orders with Date, Region, Product, Qty, Price

Folder Structure

sales-dashboard.xlsx
│
├── Sheet: Raw_Sales    (OrderID, Date, Region, Product, Qty, Price, Total)
├── Sheet: Dashboard    (KPI cards, charts, slicers)
├── Sheet: Helper       (Region and Product lookup lists)
└── Module: SalesMacros (optional VBA to refresh)

• Raw_Sales: the source data. Every row is one order.
• Dashboard: where the KPIs and charts live.
• Helper: small lists used for dropdowns and validation.
• SalesMacros: a small VBA module to refresh the pivot with one click.

Step-by-Step Development

Step 1 – Prepare the source table

What are we building?

A clean Excel table named Sales that holds all orders.

Why do we need it?

Formulas and PivotTables only work well when the data is a real table with headers and no empty rows.

How do we build it?

Select your data and press Ctrl + T. Rename the table to Sales in the Table Design tab.

=[@Qty]*[@Price]

This formula goes in the Total column. The @ means "this row." It multiplies quantity by price for every order.

• What it does: calculates the line total for each row automatically.
• Important line: [@Qty]*[@Price] uses structured references, so it follows the table even when you add rows.
• Why needed: a Total column lets every later formula sum real money instead of recomputing.

Use AI (Copilot & ChatGPT)

Ask ChatGPT:

"I have an Excel table with columns Qty and Price. Write the formula to calculate Total for each row using structured references."

Paste the answer into the Total column. If Copilot is available, select the column and type in the Copilot pane: "Add a Total column that multiplies Qty by Price."

Step 2 – Build the KPI cards

What are we building?

Three cells that show total sales, total units, and average order value.

Why do we need it?

Managers want the headline numbers without reading the whole sheet.

How do we build it?

=SUM(Sales[Total])
=SUM(Sales[Qty])
=SUM(Sales[Total])/COUNTA(Sales[OrderID])

• First formula: adds every Total to give revenue.
• Second formula: adds all quantities sold.
• Third formula: divides revenue by the number of orders for the average order value.
• Why needed: these three cells become the dashboard's top line.

Use AI (Copilot & ChatGPT)

Ask ChatGPT:

"Write three Excel formulas: total of a column named Total, sum of a column named Qty, and average order value as total divided by count of OrderID."

Step 3 – Break sales down by region and product

What are we building?

Two small tables that show revenue per region and per product.

Why do we need it?

It shows which region or product earns the most.

How do we build it?

=SUMIFS(Sales[Total], Sales[Region], A2)
=SUMIFS(Sales[Total], Sales[Product], B2)

• First formula: sums Total only where Region matches cell A2.
• Second formula: sums Total only where Product matches B2.
• Why needed: SUMIFS filters the sum by a condition, so each region/product gets its own total.

Use AI (Copilot & ChatGPT)

Ask ChatGPT:

"Write a SUMIFS formula that sums the Total column of an Excel table named Sales where the Region column equals the value in A2."

Step 4 – Add a PivotTable and slicers

What are we building?

A PivotTable of sales by month and region, with slicers to filter.

Why do we need it?

A PivotTable groups data without writing formulas, and slicers make it clickable.

How do we build it?

1. Click inside Sales, then Insert > PivotTable.
2. Put Date in Rows (group by month), Region in Columns, Total in Values.
3. Insert > Slicer, choose Region and Product.

No formula here. The PivotTable is built from the ribbon.

• What it does: instantly groups sales by month and region.
• Why needed: slicers let a user filter the whole dashboard by clicking.

Use AI (Copilot & ChatGPT)

In the Copilot pane type:

"Create a PivotTable from the Sales table showing Total by month and region, and add slicers for Region and Product."

Step 5 – Add a trend chart and an AI summary

What are we building?

A line chart of monthly sales and a text box with an AI-written summary.

Why do we need it?

Charts show the trend; the summary explains it in words.

How we build it?

Select the PivotTable monthly totals and choose Insert > Line Chart.

Use AI (Copilot & ChatGPT)

Copy the monthly totals into ChatGPT and ask:

"Summarize this monthly sales trend in three sentences for a manager. Note the best and worst month."

Paste the answer into a text box on the Dashboard sheet.

Complete Source Code

Raw_Sales table columns

OrderID | Date | Region | Product | Qty | Price | Total

Total column formula:

=[@Qty]*[@Price]

Dashboard KPI formulas

=SUM(Sales[Total])
=SUM(Sales[Qty])
=SUM(Sales[Total])/COUNTA(Sales[OrderID])

Breakdown formulas

=SUMIFS(Sales[Total], Sales[Region], A2)
=SUMIFS(Sales[Total], Sales[Product], B2)

AI prompts used

ChatGPT: "Add a Total column that multiplies Qty by Price using structured references."
ChatGPT: "Write SUMIFS to sum Total where Region equals A2."
Copilot: "Create a PivotTable of Total by month and region with slicers."
ChatGPT: "Summarize this monthly sales trend in three sentences."

Running the Project

1. Open sales-dashboard.xlsx.
2. Make sure Raw_Sales is a real table named Sales.
3. Click a slicer button to filter; the KPIs and chart update.
4. Expected output: KPI cards show numbers, the region table lists revenue per region, and the line chart shows the monthly trend.

Testing

Test Case 1

Input: 3 orders — Region "North" Qty 2 Price 10; Region "North" Qty 3 Price 10; Region "South" Qty 1 Price 20. Expected Output: Total sales = 70; North = 50; South = 20. Explanation: SUMIFS separates the regions, and SUM adds all totals.

Test Case 2

Input: Empty table (no rows). Expected Output: KPIs show 0; average order value shows #DIV/0!. Explanation: COUNTA is zero, so division fails — see Common Errors for the fix.

Test Case 3

Input: Add a new row to Sales with Qty 5 Price 4. Expected Output: Total column auto-fills with 20; KPIs increase by 20. Explanation: Structured references expand with the table.

Common Errors

#DIV/0! in average order value

Problem: The average formula shows an error. Reason: COUNTA found zero orders, so you divided by zero. Solution: Wrap it in IFERROR:

=IFERROR(SUM(Sales[Total])/COUNTA(Sales[OrderID]), 0)

SUMIFS returns 0

Problem: The region total is blank or zero. Reason: The region name in A2 has extra spaces or wrong case. Solution: Trim the lookup cell and match exact text; use TRIM(A2).

Slicer does nothing

Problem: Clicking the slicer does not filter. Reason: The slicer is connected to a different PivotTable. Solution: Right-click the slicer > Report Connections, and tick the correct PivotTable.

Improvements

• Add a top-products bar chart next to the region table
• Connect the workbook to Power BI for live sharing
• Use Power Automate to email the dashboard every Monday
• Let ChatGPT write a full monthly report from the KPIs
• Add a forecast column using Excel's FORECAST function

Challenge Exercises

1. Add a "Profit" column and show profit (not just revenue) on the dashboard.
2. Build a second slicer for the Product column and link it to the same PivotTable.
3. Write a formula that shows the best-selling region automatically with XLOOKUP.
4. Use ChatGPT to generate a VBA macro that exports the Dashboard sheet to PDF.
5. Connect ChatGPT to your data and ask it to predict next month's sales, then chart the prediction.