How to Use Copilot in Excel to Spot Sales Trends and Build a Forecast

If you still pull sales numbers out of separate spreadsheets by hand, Copilot in Excel can do much of the formula writing and trend spotting for you. Here is a step-by-step approach, with prompts you can copy.

Short answer: Put your sales data in a clean Excel table, open Copilot in Excel, and ask it in plain English to write formulas, summarize sales by customer or region, flag customers trending down, and draft a forecast. Copilot works best when your data has clear column headers and one row per transaction, and you should always check its formulas before trusting the numbers.

Many sales teams we meet track numbers in several spreadsheets: one per region, one per product line, maybe one per rep. Building a monthly view means copying and pasting for an afternoon. Copilot won't fix bad data, but it removes most of the manual formula work once the data is in decent shape.

Step 1: Get the Data Into Shape

Copilot in Excel needs your data formatted as a table (select the range and press Ctrl+T) with:

• One header row with clear names: Date, Customer, Region, Product, Quantity, Revenue
• One row per order or invoice line
• No merged cells, subtotal rows, or blank rows in the middle

If you're combining several regional sheets, ask Copilot or Excel's Power Query to append them into one table first. This step takes the longest the first time and almost no time after that.

Step 2: Let Copilot Write the Formulas

Prompts that work well:

• "Add a column that shows the month and year for each order date."
• "Add a column that calculates revenue per unit."
• "Create a formula that totals revenue by customer for the current year and the prior year."

Copilot shows you the formula before inserting it. Read it. If you don't understand it, ask: "Explain this formula in plain English."

Step 3: Summarize and Find Trends

• "Summarize total revenue by region and quarter in a PivotTable."
• "Which ten customers had the largest revenue decline compared to the same period last year?"
• "Highlight customers whose orders have dropped three months in a row."
• "Show a chart of monthly revenue for the last 24 months by product line."

The "trending down" question is the one sales managers love. It turns a spreadsheet into a call list.

Step 4: Draft a Forecast

Copilot can produce a simple forecast based on history, and Excel has built-in forecasting tools it can use. A useful prompt:

"Forecast monthly revenue for the next six months by region based on the last 24 months. Explain the method you used and the assumptions behind it."

Then add the things only your team knows. A history-based forecast doesn't know that a major customer is retooling a line, that production lead times just stretched, or that payment terms changed. Use Copilot's forecast as a starting point and adjust it with that knowledge.

Step 5: Check the Work

• Spot-check totals against a number you already trust, such as last month's invoiced revenue.
• Make sure filters didn't silently exclude rows.
• Ask Copilot, "What could make this forecast wrong?" It's a surprisingly useful question.

Which Copilot Do You Need for This?

Copilot built into Excel that works directly on your workbook requires a paid Microsoft 365 Copilot license. The free Copilot Chat can help explain formulas or suggest approaches, but it doesn't work inside your spreadsheet the same way. We break down the difference in free Copilot Chat vs. Microsoft 365 Copilot.

Learn It on Your Own Numbers

In our full-day AI workshops, we build practice spreadsheets that look like your real sales data, filled with made-up numbers, so nobody has to share confidential information. Teams leave with saved prompts they can reuse every month. When a team is ready to go further, connecting separate spreadsheets into one organized source is the kind of project we take on through AI builds.

Can Copilot in Excel forecast sales?

Yes. Copilot can draft a history-based forecast and explain its method. Treat it as a starting point and adjust it for things the data doesn't know, such as lead times, customer changes, and payment timing.

Why won't Copilot in Excel work on my spreadsheet?

Copilot works best on data formatted as an Excel table with clear headers and no merged cells or subtotal rows. It also requires a paid Microsoft 365 Copilot license, and the file usually needs to be saved to OneDrive or SharePoint.

How do I find customers whose sales are declining?

Ask Copilot to compare revenue by customer for the current period against the same period last year and list the largest declines, or to highlight customers whose orders dropped several months in a row.