Master Excel Copilot: Build & Decode Complex Formulas Step‑by‑Step

Usually, most tutorials stop at “ask Copilot to write a formula.” This is useful, but the real value is this: you can describe a messy business scenario in plain English, let Copilot draft the formula, and then use Excel’s Explain this formula feature to understand exactly what it built and why. This will help you use the real power of Copilot, which is teaching yourself how genuinely complex formulas work.

In this tutorial, we will show how to use Copilot in Excel to build and understand complex formulas. You’ll describe the business goal in everyday language and let Copilot generate the complex formula.

Prerequisites: Microsoft 365 subscription with Copilot enabled (the paid Copilot license gives the full in-grid and explanation features shown here).

Step 1: Prepare Your Data

It is important to structure your data before using Copilot. Your data should be in a table or a supported range so Copilot can understand it properly. Microsoft specifically recommends formatting data in a table or supported range before using Copilot in Excel.

• Select the entire range
• Go to the Insert tab >> select Table or press Ctrl+T
• Check “My table has headers”
• Click OK

• Go to the Table Design tab >> select Table Name Box >> name the table SalesData

You now have structured data that Copilot can reference easily.

Step 2: Describe Your Goal in Plain English (No Formula Syntax Needed)

Don’t start with “write me a formula.” Start with the goal. Suppose you want a spill formula (one formula that automatically expands) that answers a real business question. Let’s explore the following business question:

"I want a formula for the Sales Log column E that looks up the rep's Region,
then checks if their Revenue is above $10,000 — if yes, use the 'High' tier rate
from the Commission table; if no, use the 'Standard' tier rate. Both Region and Tier
need to match."

Notice what you did there: you described the logic, not the syntax. You told Copilot about your data structure, the condition, and the expected outcome — exactly the kind of thing that used to take 10–15 minutes of manual formula writing.

How to ask Copilot:

• Go to the Home tab >> select Copilot
• Type or paste the exact plain-English prompt above
• Press Enter or click the suggestion button
• Copilot instantly generates the formula and inserts it into an Excel cell
• Shows a preview of the results on the grid

Formula Copilot Returns:

=XLOOKUP(C2&IF(D2>10000,"High","Standard"),'Commission Table'!$A$2:$A$9&'Commission Table'!$B$2:$B$9,'Commission Table'!$C$2:$C$9)

Exact column numbers may vary slightly depending on your table; Copilot usually gets them right.

Step 3: Make Copilot Explain the Formula (The Real Learning Moment)

Now, explore the powerful part that most people usually skip.

• Click any cell that contains the spilled formula
• Right-click >> select Copilot Suggestions >> select “Explain this formula” from the dropdown

Copilot immediately opens an explanation card (or pane) with a clear, step-by-step breakdown in plain English.

Typical Copilot explanation you’ll see:

The formula =XLOOKUP(C2&IF(D2>10000,”High”,”Standard”),’Commission Table’!$A$2:$A$9&’Commission Table’!$B$2:$B$9,’Commission Table’!$C$2:$C$9) finds the commission rate for the sales record in row 2 by matching the region and revenue tier to the “Commission Table”.

• It combines the region in cell C2 (“North”) with a tier determined by whether the revenue in D2 (12,500) is greater than 10,000 (“High” if true, “Standard” if false).
• It searches for this combined value (“NorthHigh”) in the “Commission Table” by joining the Region column (A2:A9) and Tier column (B2:B9) for each row.
• When it finds a match, it returns the corresponding commission rate from column C2:C9 of the “Commission Table”.

For example, since D2 is 12,500 (>10,000), the formula looks for “NorthHigh” and returns the rate from C3, which is 0.08 (8%).

Ask Copilot to Explain It Layer by Layer

If you don’t follow the formula from the explanation above, don’t worry. In the same Copilot chat, type:

"Can you explain this formula to me one part at a time, starting from the inside out?"

Copilot will walk you through it. Here’s what that explanation looks like and what it actually means:

Step-by-Step Breakdown (Inside → Out)

Step 1: D2>10000

This is the innermost comparison.

Step 2: IF(D2>10000,”High”,”Standard”)

Wraps the comparison in an IF statement to return a tier name.

Step 3: C2&IF(D2>10000,”High”,”Standard”)

Concatenates the Region with the Tier to create a lookup key.

This is what we’re searching for in the Commission Table.

Step 4: ‘Commission Table’!$A$2:$A$9&’Commission Table’!$B$2:$B$9

Creates the lookup array by concatenating Region + Tier from the Commission Table.

This is what we’re searching in.

Step 5: ‘Commission Table’!$C$2:$C$9

This is the return array — the commission rates.

Step 6: The Full XLOOKUP(…)

Puts it all together!

=XLOOKUP( “NorthHigh”, {“NorthStandard”;”NorthHigh”;…}, {0.05;0.08;…} )

Business Scenario with a Complex Formula

You can use different types of prompts. The more precisely you mention column names and criteria, the higher the likelihood of getting an accurate formula. Let’s explore the following business question:

“Show me only the East-region Computers sales. List each unique salesperson, their total sales amount in that period, and a performance status: ‘High Performer’ if over $500, ‘Average’ if $100–$150, otherwise ‘Needs Improvement’. Sort the list by total sales descending, with clear headers.”

This is complex because it requires filtering on multiple conditions (region + category), aggregating per person, applying tiered logic, and sorting — exactly the kind of thing that used to take 10–15 minutes of manual formula writing.

• Type or paste this exact plain-English prompt:

"Create a dynamic spill formula for table SalesData. Filter to Region = "East",
Product Category = "Computers". Return unique Salesperson, their Total Sales Amount,
and a Performance Status column ("High Performer" if total > 2000, "Average" if >= 1000,
otherwise "Needs Improvement"). Sort by Total Sales descending. Include headers:
Salesperson, Total Sales, Performance Status."

• Press Enter or click the suggestion button

Formula Copilot Returns:

=LET(
filtered, FILTER(SalesData, (SalesData[Region]="East")*(SalesData[Category]="Computers"), ""),
salespersons, UNIQUE(INDEX(filtered,,4)),
totals, MAP(salespersons, LAMBDA(sp, SUMIFS(SalesData[SalesAmount], SalesData[Region], "East", SalesData[Category], "Computers", SalesData[Salesperson], sp))),
status, MAP(totals, LAMBDA(t, IFS(t>2000, "High Performer", t>=1000, "Average", TRUE, "Needs Improvement"))),
combined, HSTACK(salespersons, totals, status),
sorted, SORT(combined, 2, -1),
VSTACK({"Salesperson","Total Sales","Performance Status"}, sorted)
)

• Click Done
• Automatically inserts the formula into the Excel cell

Copilot automatically shows the formula explained in How this formula works:

How the formula works:

1. FILTER – Gets all rows where Region = “East” AND Category = “Computers”
2. UNIQUE – Extracts distinct salesperson names from filtered data
3. MAP + SUMIFS – Calculates total sales for each unique salesperson
4. MAP + IFS – Assigns performance status based on the thresholds
5. HSTACK – Combines the three columns horizontally
6. SORT – Orders by Total Sales (column 2) in descending order (-1)
7. VSTACK – Adds the header row on top

The formula is fully dynamic and will automatically recalculate if you add more East/Computers sales data to the table!

You can also use the Explain this formula feature for this formula. Either way, the goal is the learning — choose whichever approach works best for you.

Step 4: Iterate and Refine (Build Even More Complexity)

Stay in the Copilot pane (or type = again) and give follow-up prompts like:

• “Modify the formula to also show the number of orders per salesperson as a fourth column.”
• “Make the $2000 and $1000 thresholds reference cells K1 and K2 so I can change them easily.”
• “Add a column that shows the average sales per order using the Order ID count.”

Each time, Copilot updates the formula, and you can ask for a formula explanation again to see exactly what changed. This iterative loop is how professionals go from basic formulas to production-grade dynamic dashboards.

Pro Tips for Maximum Value

• Be specific in your plain-English prompt: name the table, list exact conditions, and say “dynamic spill formula” or “include headers.”
• Always verify the result against your data. Copilot is extremely capable but not infallible.
• Learn by copying the explained pieces into your own formulas later; you’ll internalize LET, BYROW, LAMBDA, etc. faster than any course.
• Works beautifully on large datasets (tens of thousands of rows) because these are optimized dynamic-array functions.

Conclusion

By following the steps above, you can use Copilot in Excel to build and understand complex formulas. You can use it to translate business logic into formulas, then use Explain this formula to understand the output. Copilot can be one of the easiest and most effective ways to learn Excel formulas while getting real work done. Through this process, you won’t just end up with a complex formula — you’ll understand why every piece exists and how to tweak it for any future scenario. That’s the real power of Copilot in Excel. Try it right now with the sample data above. If your data is different, just describe your own goal in the same plain-English style; the process is identical.