You want to test different sales scenarios, such as changing a discount rate or a growth percentage, without manually recalculating every formula. Copilot in Excel can generate these what-if tables and charts using plain English prompts, so you do not need to write complex formulas or use the Goal Seek tool manually. This article explains how to set up your sales data, ask Copilot to create a what-if analysis table, and interpret the results. You will learn the exact prompts to use and the data structure Copilot requires for accurate output.
Key Takeaways: Using Copilot for Sales Scenario Testing
- Copilot pane > Data tab > What-If Analysis: Opens the scenario generation interface after you select your sales data range.
- Prompt example: “Create a what-if table showing profit when discount is 10%, 15%, and 20%”: Copilot builds a two-variable data table with the formulas already inserted.
- Copilot pane > Insert chart > Recommended chart: Generates a line or column chart comparing scenarios directly from the what-if table.
What Copilot Needs for What-If Analysis on Sales Data
Copilot in Excel uses the Data Table feature from Excel’s What-If Analysis group to create scenario tables. It requires a structured data range with at least one input cell, such as a discount percentage or a growth rate, and one formula cell, such as total revenue or net profit. The sales data must be in a table format with headers in the first row. Copilot does not create formulas from scratch; it reads existing formulas in your workbook and substitutes values you specify in the prompt. For example, if your sales table has a column for Quantity and Price, and a formula for Revenue, Copilot can vary the Price and show how Revenue changes.
Before you start, ensure your workbook contains at least one formula that references the input cell you want to vary. If you do not have a formula, Copilot will prompt you to add one. The feature works with Excel for Microsoft 365 version 2402 or later. You also need a Copilot for Microsoft 365 license assigned to your user account. If you see a grayed-out Copilot button, check your license status in the Microsoft 365 admin center.
Data Structure Example
A typical sales table for what-if analysis has these columns:
- Product Name
- Units Sold
- Unit Price
- Discount (as a percentage)
- Revenue (formula: Units Sold Unit Price (1 – Discount))
Copilot reads the Discount cell as the input and Revenue as the result. When you ask for a what-if table, Copilot creates a two-variable table where the row input is Discount and the column input is Units Sold, or any combination you specify in the prompt.
Steps to Create a What-If Analysis Table with Copilot
Follow these steps to generate a what-if table for your sales data. The instructions assume your data is already in an Excel table with headers.
- Open the Copilot pane
Click the Copilot button on the right side of the Excel ribbon. If you do not see it, go to the Home tab and click Copilot. The pane opens on the right side of your Excel window. - Select your sales data range
Click and drag to highlight the entire table including headers. For example, select cells A1 to E20 if your table has 20 rows. Copilot uses the selected range to understand the column names and values. - Type a what-if prompt
In the Copilot text box at the bottom of the pane, type a prompt like “Create a what-if table showing revenue when discount is 5%, 10%, and 15%.” Press Enter. Copilot analyzes the table and generates a new table starting two rows below your original data. - Review the generated table
Copilot inserts a two-variable data table. The first column lists the discount values you requested. The first row shows the original revenue value. The intersecting cells show the recalculated revenue for each discount. The table uses the Excel DATA TABLE formula, so the values update automatically if you change the original input cells. - Create a chart from the what-if table
Select the generated what-if table. In the Copilot pane, type “Insert a line chart showing revenue at each discount level.” Copilot adds a chart to your worksheet with the discount values on the x-axis and revenue on the y-axis.
Common Mistakes and How to Avoid Them
Copilot’s what-if analysis depends on correct data structure and clear prompts. These are the most frequent issues users encounter.
Copilot says “I cannot create a what-if table from this data”
This message appears when your selected range does not contain a formula that references an input cell. To fix this, add a formula in a new column. For example, if your table has Units Sold in column B and Unit Price in column C, create a new column named Revenue with the formula =B2C2. Then select the entire table again and repeat the prompt.
The what-if table shows #REF! or #VALUE! errors
These errors occur when the original formula references cells outside the selected range or when the input values are not numeric. Verify that your discount values are entered as percentages without the percent sign, for example 0.10 instead of 10%. Also check that the formula in your table does not reference merged cells or external workbooks.
Copilot inserts the table in the wrong location
Copilot places the what-if table two rows below the last row of your selected data. If you have data in that area, Copilot may overwrite it. Before running the prompt, ensure the two rows below your table are empty. You can also move the generated table manually after creation.
Copilot What-If Analysis vs Manual Data Table: Key Differences
| Item | Copilot What-If Analysis | Manual Data Table |
|---|---|---|
| Setup time | Less than 30 seconds with a prompt | 2 to 5 minutes writing formulas and setting up table layout |
| Formula knowledge required | None; Copilot reads existing formulas | Must know DATA TABLE syntax and array formulas |
| Input value entry | Type values in the prompt | Must type values in a separate range manually |
| Chart generation | Ask Copilot to insert a chart from the table | Must insert chart manually and select data |
| Update when source data changes | Table updates automatically because it uses DATA TABLE | Table updates automatically because it uses DATA TABLE |
You can now use Copilot to create what-if analysis tables for any sales scenario without writing a single formula. Start with a simple two-variable table for discount and revenue, then expand to three variables by asking Copilot to add a second input cell. For advanced scenarios, combine Copilot with Excel’s Goal Seek tool for reverse what-if analysis where you specify a target revenue and let Excel find the required discount.