Build Complex Workpaper Formulas with Excel Copilot

What This Does

Excel Copilot generates complex formulas and explains them in plain English, so you can build accurate audit schedules without Googling syntax or spending 20 minutes on trial-and-error.

Before You Start

• You have Microsoft Excel with a Microsoft 365 Copilot license
• Your data has a clear header row (a formatted table via Insert > Table helps but is no longer required)
• You know roughly what you want the formula to do
• Time needed: 5-10 minutes per formula task
• Cost: Included in M365 Copilot license

Steps

1. Open Copilot in Excel

Click the Copilot icon in the lower-right corner of the Excel window. Microsoft moved this button off the Home ribbon in 2026; press F6 to reach it from the keyboard, or right-click it and choose Move to ribbon if you prefer the old placement. The Copilot pane opens on the right.

2. Describe the formula you need

In the Copilot chat box, describe exactly what you want. Be specific about column names and edge cases:

"Write a formula for column E that calculates the percentage change from column D (prior year) to column C (current year). If prior year is zero or blank, show 'N/A' instead of an error."

3. Review and insert the formula

Copilot displays the formula with an explanation. You'll see an "Insert" button to add it directly to your spreadsheet. Click Insert to add it to the selected column.

What you should see: The formula appears in the selected cell(s) with correct syntax. Copilot explains what each part does.

4. Test it on a few rows

Check the results on 3-5 rows manually. Verify the output is what you expected.

5. Watch for the COPILOT function (advanced)

Microsoft has announced a =COPILOT() worksheet function that puts AI inside formulas, e.g. =COPILOT("classify this as...", A2) to categorize text values automatically. It is rolling out through Microsoft 365 Insider builds, so if your Excel version has it, this is useful for tagging account descriptions or transaction types.

Real Example

Scenario: You're building a debt schedule for an audit client and need a formula that calculates interest expense for each loan at different rates, handling variable-rate and fixed-rate loans differently.

What you type: "Write a formula that calculates monthly interest expense. Column B is loan balance, Column C is annual interest rate. If column D says 'variable', multiply B by (C/12). If it says 'fixed', multiply B by (C/12) as well but add a flag in the result if the rate is over 8%."

What you get: A nested IF formula that handles both scenarios, with a note explaining the logic. Paste it in 30 seconds instead of spending 15 minutes figuring out the syntax.

Tips

• Use Copilot to debug broken formulas too: paste the formula and ask "why does this return #VALUE error?"
• For very complex array formulas, describe the end result you want and let Copilot choose whether XLOOKUP, INDEX/MATCH, or FILTER is the right approach
• Copilot also explains existing formulas. paste a formula you inherited and ask "what does this do in plain English?"

Tool interfaces change, if the Copilot button has moved, look for the Copilot icon in the lower-right corner of the Excel window or similar AI/smart options nearby.