Run a two-way sensitivity analysis with conditional highlighting

Step 1: Set up the base modelExcel

Put Price and Volume in their own clearly labeled cells (not buried inline in a formula) and build the Operating Income formula to reference those cells directly.

What-If Analysis's Data Table feature needs exactly this structure to work, with the formula in the corner of the table referencing the two input cells it will vary.

Step 2: Ask Copilot to build the sensitivity tableCopilotExcel

A manual two-way data table is fiddly to set up correctly — let Copilot handle the Excel mechanics.

Prompt idea:

Build a two-way data table showing Operating Income for Price ranging from $18 to $28 in $1 steps, and Volume from 8,000 to 14,000 units in 1,000-unit steps. Conditionally highlight any cell where Operating Income is negative.

Step 3: Read the danger zoneExcel

Look at the highlighted region as a whole, not cell by cell — the shape of the boundary between profit and loss tells you more than any single number.

Step 4: Stress-test the assumptionsCopilotExcel

Ask what happens if a fixed cost assumption changes, and regenerate the table — a sensitivity analysis is only useful if you actually vary more than one input.

Check the work

• Re-derive one or two cells by hand to confirm the data table formula is referencing the right input cells.
• Check that the highlighting rule is capturing true negatives, not just low positives.
• Confirm the price/volume ranges you tested actually bracket your realistic scenarios.

Source: Nexacu, "Copilot for Excel (2026): Practical Prompts, Agent Mode & Time-Saving Examples" (2026)