Use Excel Copilot to Write Complex Audit Formulas

What This Does

Excel Copilot writes the exact formula you need for any workpaper calculation — including nested functions, XLOOKUP, dynamic arrays, and conditional logic — from a plain-English description, without any formula syntax knowledge required.

Before You Start

• Microsoft 365 with Copilot enabled at your firm
• A workbook open with the data you need to work with
• Know what result you want — you don't need to know the formula syntax
• Cost: Microsoft 365 Copilot subscription

Steps

1. Open the Copilot panel in Excel

Open your workbook. Click the Copilot button in the Home tab ribbon (sparkle icon on the right). The Copilot panel opens on the right side of the screen.

Tip: You can also click on any cell and use Copilot to explain what an existing formula does — useful for understanding workpaper formulas you inherited from a prior-year file.

2. Describe what you need the formula to do

In the Copilot panel, describe the formula in plain English. Be specific about:

• Which columns contain which data (exact column letters)
• What result you want
• Any conditional logic or exception handling

Example prompts for common audit scenarios:

Percent change calculation with exceptions: "Column C has prior year balances, column D has current year balances. Write a formula in column E that shows the percent change from C to D. If C is zero or blank, show 'New Account'. If both are zero, show 'N/A'."

XLOOKUP for trial balance tie-out: "Column A in Sheet2 has account numbers. I want to look up each account number in Sheet1 column A and return the balance from Sheet1 column D. If the account isn't found, show 'Not found'."

Sampling interval calculation: "Cell B2 contains the population dollar total. Cell B3 contains the sampling interval. Write a formula in B4 that calculates the required sample size by dividing B2 by B3 and rounding up to the nearest whole number."

Benford's Law first digit extraction: "Column A contains invoice amounts. Write a formula in column B that extracts the first significant digit from each amount (ignoring leading zeros and decimal points)."

3. Review and insert the formula

Copilot will display the formula with an explanation of what each part does. Review the explanation to confirm it matches what you asked for. Click Insert formula or copy it from the panel and paste it into your target cell.

What you should see: The formula inserted into the cell, calculating the correct result. Verify on a few rows before applying to the full column.

Troubleshooting: If the formula returns an error, paste the formula and the error message back into Copilot: "This formula returned [error]. What's wrong?" It will diagnose and fix the issue.

4. Extend and protect the formula

Once the formula works in one cell, extend it to the full range by:

• Selecting the formula cell
• Pressing Ctrl+D (fill down) or dragging the fill handle
• Or asking Copilot: "Apply this formula to all rows in the range A2:A500"

For critical workpaper formulas, consider locking cells to prevent accidental edits: Review → Protect Sheet → select cells to protect.

Real Example

Scenario: Building a revenue variance analysis for a retail client. You have 3 years of monthly revenue data across 12 product categories. You need: percent change vs. prior year, a flag for any category where the variance exceeds 15%, and a column indicating whether the variance is favorable or unfavorable.

What you type in Copilot: "Column A: product category names. Column B: prior year revenue. Column C: current year revenue. I need:

• Column D: percent change (current vs prior), showing 'New' if prior year is blank
• Column E: show 'FLAG' if the absolute percent change exceeds 15%, otherwise blank
• Column F: show 'Favorable', 'Unfavorable', or 'N/A' based on whether revenue increased, decreased, or is a new category"

What you get: Three formulas for columns D, E, and F in about 60 seconds. You spot-check 5 rows, confirm accuracy, and fill down. Time: 5 minutes vs. 20–30 minutes of formula building.

Tips

• If Copilot gives you a formula that works but you don't understand it, ask: "Explain what each part of this formula does." Understanding your workpaper formulas prevents review comments.
• For VBA macros (when a formula isn't enough), describe the multi-step automation and ask Copilot: "Write a VBA macro that does this instead of a formula." You'll get code to paste into the Developer tab.
• Copilot remembers the context of your workbook for the current session — if you describe the structure once ("Column A is account numbers, B is descriptions, C is prior year, D is current year"), you can ask for multiple formulas without re-explaining the layout.

Tool interfaces change — if a button has moved, look for similar AI/magic/smart options in the same menu area.