Use Excel Copilot to Write Tax Workpaper Formulas

What This Does

Excel Copilot lets you describe a formula in plain English and have it written for you — no more spending 20 minutes debugging a complex apportionment calculation or basis tracking formula. You describe what you need, Copilot writes it.

Before You Start

• Your organization has Microsoft 365 Business Standard or higher with Copilot enabled
• You have Microsoft Excel desktop app (version 16.0+) or Excel on the web
• You have a workpaper open with your data already in the spreadsheet
• You're logged into your Microsoft 365 work account

Steps

1. Open Excel and your tax workpaper

Open the Excel file for your current engagement. Make sure your data is already in the spreadsheet — column headers are labeled, data is in the right cells. Copilot works best when it can see your actual data structure.

What you should see: Your normal Excel workpaper with data in columns.

2. Open the Copilot panel

Click the Copilot button in the Home ribbon (star/sparkle icon, usually on the far right). A panel opens on the right side of the screen.

Troubleshooting: If you don't see the Copilot button, your organization may not have Copilot enabled. Check with your IT department or firm administrator.

3. Ask for your formula in plain English

In the Copilot panel, type exactly what calculation you need. Be specific about column references and calculation logic.

Examples for tax workpapers:

• "Write a formula to calculate the three-factor apportionment percentage. Property factor is in column B, payroll factor is in column C, sales factor is in column D. Sales is double-weighted."
• "Write a formula to calculate the taxable portion of Social Security benefits for a MFJ taxpayer. Provisional income is in cell E5, total SS benefits in cell E6."
• "Create a formula that calculates the §179 deduction limit phaseout. Placed-in-service property total is in B3, the annual limit is $1,160,000, phaseout threshold is $2,890,000."

What you should see: Copilot displays the formula with an explanation of each component. A button appears to insert it directly into your selected cell.

4. Insert and verify the formula

Click "Insert" or "Add to spreadsheet." The formula goes into your selected cell. Check that it references the correct columns and returns the expected result using a test case you can verify manually.

Troubleshooting: If the formula references wrong columns, tell Copilot: "The formula used column D but the sales data is actually in column F. Correct it."

5. Ask for an explanation to document the formula

Ask Copilot: "Explain what each part of this formula does, in plain language I can add to my workpaper documentation." You'll get a step-by-step explanation you can paste as a comment or note in the workpaper.

Real Example

Scenario: You're preparing an apportionment schedule for a client with business in 5 states. You need a formula for the sales factor using destination-based sourcing.

What you type in Copilot: "Write a formula to calculate the sales factor for apportionment purposes. Total sales in column B, sales sourced to this state in column C. If total sales is zero, return 0% to avoid a divide by zero error. Show as a percentage."

What you get: =IF(B2=0,0,C2/B2) with format guidance to display as %, plus an explanation that the IF statement prevents the division error when total sales is zero.

Tips

• Give column letters or cell references explicitly — Copilot reads your actual spreadsheet structure but may guess wrong without guidance
• Ask it to "explain each part" after generating any formula you'll use on a client return — document your workpapers
• Use Copilot to check existing formulas: "Is there an error in this formula? [paste formula]" — it can identify mistakes

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