Excel with AI for Sales Teams: Sales Reports, Revenue Dashboard, Sales Forecast & Customer Analysis

Build sales reporting and forecasting in Excel using Microsoft Copilot and ChatGPT — turn raw deal data into reports, dashboards, forecasts, and customer insights.

Table of Contents

• 1. Sales Reports
• 2. Revenue Dashboard
• 3. Sales Forecast
• 4. Customer Analysis

How to Use AI in This Tutorial

Every task below follows the same two-step AI pattern:

1. Microsoft Copilot (inside Excel) — open the Copilot pane from the ribbon (or press Alt + the Copilot key). Type your request in plain English; Copilot builds the table, pivot, or chart for you.
2. ChatGPT (alongside Excel) — when you need an exact formula or logic, paste your column layout into ChatGPT and ask for the precise Excel formula, then paste it back into the cell.

Use Copilot to build and explore, ChatGPT to get the exact formula. Both are shown in every section.

1. Sales Reports

A sales report summarizes deals by period, rep, or product so leadership sees performance at a glance. The workhorse is SUMIFS — sum with multiple filters.

Total sales for a rep and month (Amount in D, Rep in B, Date in A):

=SUMIFS(D:D, B:B, "Maria", A:A, ">=01/01/2026", A:A, "<=01/31/2026")

Copilot prompt:

"Create a monthly sales report from this deal list showing total revenue per sales rep."

ChatGPT prompt:

"Write an Excel formula that sums revenue only for deals with status 'Closed Won'."

=SUMIF(StatusColumn, "Closed Won", RevenueColumn)

Simple explanation: SUMIFS adds revenue when rep, start date, and end date all match. Copilot can generate the same report as a pivot in one click.

Common mistake: Storing dates as text. If SUMIFS by date returns 0, ask Copilot to convert the Date column to real dates.

2. Revenue Dashboard

A revenue dashboard is a live view of totals, growth, and splits. Combine a few SUMIFS cards with a chart Copilot generates.

Total revenue (all):

=SUM(RevenueColumn)

Month-over-month growth:

=(ThisMonth - LastMonth) / LastMonth

Copilot prompt:

"Build a revenue dashboard with total revenue, MoM growth, and revenue by region as a chart."

ChatGPT prompt:

"Suggest four KPI cards for a sales revenue dashboard in Excel."

Best practice: Reference a single "as of" date cell so every card updates together when you change the period.

3. Sales Forecast

Forecasting projects future revenue from past trends. A simple, reliable method is linear trend using FORECAST.LINEAR.

Setup: monthly revenue in B2:B13, month numbers 1–12 in A2:A13. Forecast month 13:

=FORECAST.LINEAR(13, B2:B13, A2:A13)

Copilot prompt:

"Forecast next quarter's revenue from this 12-month history and plot the trend line."

ChatGPT prompt:

"Write an Excel formula that predicts next month's sales using the average of the last 3 months."

=AVERAGE(OFFSET(B13, -2, 0, 3, 1))

Simple explanation: FORECAST.LINEAR fits a straight line through your history and extends it. The AVERAGE(OFFSET(...)) approach is simpler — it just averages the most recent three months as a naive next-month guess.

Note: Linear forecast assumes steady trend. For seasonal data, describe the pattern to ChatGPT and ask for a seasonal formula.

4. Customer Analysis

Customer analysis reveals who buys, how much, and how often — including repeat rate and segment value.

Revenue per customer:

=SUMIF(CustomerColumn, "Acme", RevenueColumn)

Repeat purchase rate (customers with >1 order ÷ total customers):

=COUNTIFS(OrderCountColumn, ">1") / COUNTA(CustomerColumn)

Copilot prompt:

"Segment customers by total spend into High / Medium / Low and show revenue share of each segment."

ChatGPT prompt:

"Write an Excel formula that labels a customer High value if total spend exceeds $10,000."

=IF(SUMIF(CustomerColumn, A2, RevenueColumn) > 10000, "High", "Standard")

Simple explanation: SUMIF totals one customer's spend, and IF compares it to the $10,000 threshold to assign a tier you can pivot on.