You want Copilot to suggest scenarios for your Excel data using the Scenario Manager feature, but the suggestions are not appearing or are inaccurate. Copilot in Excel can analyze data tables and propose what-if scenarios, but this depends on correct data structure and specific prerequisites. This article explains how to set up your worksheet so Copilot can generate Scenario Manager suggestions and covers the limitations that prevent it from working in some cases.
Scenario Manager is a built-in Excel tool for creating and comparing sets of input values. Copilot can automate the suggestion of these scenarios by reading your data model. However, Copilot cannot suggest scenarios if your data lacks defined input cells, named ranges, or a clean table format. This guide walks you through the exact setup steps and lists the known constraints.
Key Takeaways: Setting Up Copilot for Scenario Manager in Excel
- Format data as a table with headers: Copilot requires a structured table to identify input and output cells for scenario generation.
- Use named ranges for input cells: Assign descriptive names like Interest_Rate or Units_Sold so Copilot can map suggestions correctly.
- Keep scenario count under 32: Excel Scenario Manager supports a maximum of 32 changing cells per scenario, and Copilot respects this limit.
How Copilot Generates Scenario Manager Suggestions
Copilot in Excel uses the same calculation engine as the native Scenario Manager. When you ask Copilot to suggest scenarios, it scans the active worksheet for a contiguous data table with at least one formula cell that depends on input cells. Copilot identifies the input cells as those that are not formulas and are referenced by at least one formula in the table. It then proposes up to three scenarios by varying the input cells within a reasonable range derived from the existing data.
The suggestions appear in the Copilot pane as a list of scenario names and changing cell values. You can preview each scenario before applying it. Copilot does not create the Scenario Manager report automatically; you must open the Scenario Manager dialog to add the suggested scenarios to the workbook.
Prerequisites for Copilot Scenario Suggestions
Before Copilot can suggest scenarios, your workbook must meet these conditions:
- Your data must be formatted as an Excel table using Ctrl+T or Insert > Table.
- The table must contain at least one formula cell that references input cells in the same row or column.
- Input cells must be directly in the table or in named ranges referenced by the table formulas.
- Your Microsoft 365 subscription must include Copilot for Microsoft 365. Copilot Pro does not include Excel-specific Scenario Manager features.
- Copilot must be enabled by your IT administrator in the Microsoft 365 admin center.
Steps to Enable and Use Copilot Scenario Manager Suggestions
Follow these steps to set up your worksheet and generate scenario suggestions with Copilot.
- Format your data as an Excel table
Select the range that contains your input values and formulas. Press Ctrl+T. In the Create Table dialog, confirm the range and check My table has headers. Click OK. - Define named ranges for each input cell
Select a cell that Copilot should treat as an input, such as a cell containing an interest rate. Go to Formulas > Define Name. Enter a name like Interest_Rate and click OK. Repeat for all input cells you want Copilot to vary. - Ensure formulas reference the named ranges
Edit the formula in your output cell to use the named range instead of a cell reference. For example, change=B2C2to=Interest_RateUnits_Sold. This helps Copilot identify the dependency. - Open the Copilot pane
Click the Copilot icon on the Home tab of the ribbon. If the icon is not visible, click the Show Taskpane button in the Copilot group. - Request scenario suggestions
Type a prompt such as: Suggest scenarios for this table or Create three scenarios varying the input cells. Copilot will display the suggestions in the pane. - Preview and apply a suggestion
Click on any suggested scenario name in the Copilot pane to see the temporary values in your worksheet. If you want to keep the scenario, open Data > What-If Analysis > Scenario Manager. Click Add, name the scenario, and enter the changing cell values from the Copilot suggestion.
Limitations and Things to Avoid
Copilot Scenario Manager suggestions have several constraints that affect when and how they work.
Copilot does not suggest scenarios for non-table data
If your data is not formatted as an Excel table, Copilot will respond with a message that it cannot find a suitable data structure. Convert the range to a table using Ctrl+T before asking for scenario suggestions. Copilot also ignores tables that contain merged cells or blank rows.
Maximum of 32 changing cells per scenario
Excel Scenario Manager limits each scenario to 32 changing cells. Copilot respects this limit and will not suggest a scenario that requires more than 32 input cells. If your model has more than 32 input cells, reduce the number or group inputs into a single named range.
Copilot does not create scenario summary reports
After you add a scenario manually, you can generate a summary report by clicking Summary in the Scenario Manager dialog. Copilot does not automate this step. You must select the result cells for the report manually.
Named ranges must not contain spaces or special characters
Copilot may fail to recognize named ranges that include spaces, hyphens, or symbols like # or &. Use only letters, numbers, and underscores in range names. For example, use Unit_Price instead of Unit Price.
Copilot Pro does not support Scenario Manager suggestions
Only Copilot for Microsoft 365 includes the Excel Scenario Manager suggestion feature. If you have Copilot Pro, you can still use Scenario Manager manually but Copilot will not generate scenario suggestions. Verify your license in Account > About Copilot.
Copilot Scenario Manager Suggestions vs Manual Scenario Creation
| Item | Copilot Suggestions | Manual Scenario Creation |
|---|---|---|
| Setup effort | Requires table format and named ranges | No data structure prerequisites |
| Speed of creation | Seconds to generate suggestions | Minutes per scenario |
| Number of scenarios | Up to 3 suggestions at a time | Unlimited, limited by memory |
| Customization | Copilot chooses the range of values | Full control over every input value |
| Works with formulas | Yes, only if formulas reference named ranges | Yes, any cell reference works |
| License requirement | Copilot for Microsoft 365 | Any Excel version with Scenario Manager |
Copilot can save time by generating scenario suggestions automatically, but manual creation gives you full control over input values and works with any data layout. Use Copilot suggestions when your model is simple and you want quick ideas. Use manual creation for complex models with many inputs or when you need precise scenario definitions.