top of page
Search

Excel Copilot’s “Suggest a Formula” with Step-by-Step Explanation

Copilot has removed one of Excel’s biggest pain points—remembering complex syntax or hunting through function lists. The Suggest a Formula command translates a plain-English request (“total revenue for North region in Q4”) into the exact Excel formula you need and then walks you through every element so you know why it works. Below is a practical overview of the feature as it stands in May 2025, plus tips to make each suggestion rock-solid.

1 · What the Formula-Suggestion Feature Does

When you select a blank cell and type a query in the Copilot pane, Excel responds with:

  • A recommended formula—for example, =SUMIFS(F:F, A:A, "North", C:C, ">=2025-10-01", C:C, "<=2025-12-31").

  • A step-by-step breakdown explaining each argument, the range it references, and the filter logic.

  • Inline tips for adjusting cell references to absolute or structured table styles.

You can accept the formula, regenerate an alternative (e.g., a dynamic array version), or ask follow-up questions like “show me a pivot table instead.”


2 · Step-by-Step: Getting Your First Suggested Formula

Step

What to Do in Excel

1

Highlight the destination cell where the result should appear.

2

Open the Copilot pane and click Suggest a formula.

3

Describe the calculation in natural language, such as “average lead time by supplier.”

4

Review the proposed formula and the explanation panel—each argument is labeled with its meaning.

5

Click Insert to place the formula, or choose Regenerate if it references the wrong columns.

Tip  If your data lives in an Excel Table, mention the table name (“use tblSales”) so Copilot produces structured references like tblSales[Amount].

3 · Inside the Explanation Pane

The side panel breaks the formula into three parts:

  1. Purpose – a sentence in plain English: “Adds Amount where Region equals North and Date falls in Q4 2025.”

  2. Argument map – a numbered list mapping each argument to the highlighted sheet range.

  3. Next actions – buttons to test with sample data, convert to dynamic array (e.g., SWITCH or FILTER), or wrap the result in LET for readability.

Understanding this anatomy turns Copilot’s suggestion into a mini-lesson, reinforcing best-practice formula design.


4 · What’s New in 2025

  • Array-first mindset – Copilot now prefers dynamic functions such as FILTER, BYROW, and MAP when your version of Excel supports them.

  • Error forecaster – Before inserting, Copilot flags likely issues (mismatched data types, blank cells) and offers a fallback like IFERROR.

  • Explain in your language – Explanations can be output in 28 locales, including Italian, Japanese, and Brazilian Portuguese.

  • Formula refactor – Select an existing legacy formula and ask Copilot to modernize it with TAKE, DROP, and modern functions.


5 · Five High-Impact Use Cases

  1. Financial analysis – Generate a multilevel SUMIFS to subtotal revenue by product line, quarter, and currency—no manual nesting required.

  2. Inventory aging – Ask “count items older than 90 days” and receive a COUNTIFS with TODAY()-Date math built in.

  3. Customer churn tracking – Turn “customers with no purchase in six months” into a MATCH and ISNUMBER combo wrapped in NOT.

  4. Rolling averages – Request “7-day rolling average of daily sales” and Copilot proposes AVERAGE(OFFSET…) or a dynamic AVERAGE with TAKE.

  5. Quality control dashboards – Describe “defect rate by shift” and Copilot suggests a DIVIDE between defect counts and total units, then wraps it in TEXT for a percentage format.


6 · Best Practices for Reliable Results

Do

Don’t

Describe the data shape (“three columns: Date, Region, Sales”).

Assume Copilot knows which sheet you’re on if several look alike.

Use Excel Tables—structured names boost formula clarity.

Leave ambiguous prompts like “figure this out”; be specific.

Iterate—ask follow-ups (“now show a year-to-date version”).

Skip validation—always eyeball the ranges before accepting.

Leverage explanations—they double as training material for junior analysts.

Overcomplicate: shorter, focused prompts yield better suggestions.


7 · Looking Ahead

Microsoft’s public roadmap signals upcoming “Formula Chat,” where you tweak an inserted formula conversationally (“swap Region for Country, change sum to average”). A pilot group is also testing auto-generated LET wrappers that name key arguments for traceability, plus hybrid Power Query-Copilot steps to pull external data and write the formula in one go.

bottom of page