The deliverable Excel workbook is where most of the firm's hours die. Today you learn how to use ChatGPT and Claude to generate formulas, write variance commentary, build pivot tables from messy data, and produce a tie-out summary — without breaking the file the partner has to sign off on.
By the end of this lesson you will know how to ask AI to write Excel formulas without errors, how to clean unstructured data into pivot-ready format, how to produce executive variance commentary that matches your firm's voice, and the data-protection rules under AICPA confidentiality standards that govern Excel files leaving your machine.
Most accountants ask AI for a formula like this: "I need a formula that sums column B if column A is December." The model gives a plausible answer that may or may not work in your specific layout. The fix is to give the model the layout and the desired output explicitly.
# Excel formula prompt — the layout-first pattern
I have a workbook with a sheet named "TB" containing a trial
balance for Taxpayer A. The columns are:
A: Account Code (text, e.g., "4000-100")
B: Account Name (text)
C: Department (text — Sales, G&A, R&D, COGS, or blank)
D: Beginning Balance (number)
E: Activity (number, can be negative)
F: Ending Balance (number, = D + E)
Data runs from row 2 through row 412. Row 1 is headers.
In a separate sheet "Summary", I want a formula in cell B5
that returns the sum of F2:F412 from TB where:
- Account Code starts with "4" (revenue)
- Department is "Sales"
- F is non-zero
Give me the formula, then explain each piece. Use SUMIFS,
not SUMPRODUCT. Do not use array formulas. The workbook
must work in Excel 2019 (no LET, no LAMBDA, no XLOOKUP).
The "Excel 2019" constraint is critical when your client is on a corporate version that hasn't been updated. The model will default to modern Excel functions like XLOOKUP and FILTER unless you tell it not to. The "explain each piece" instruction is your verification tool — if the explanation does not match the formula, the formula is wrong.
Clients send you data in formats that defy reason. Multiple values per cell, dates as text, currency strings with mixed symbols, merged cells, blank rows between sections. AI is excellent at producing the formula or Power Query M code to fix this — if you describe the problem precisely.
# Data cleanup prompt — the before/after pattern
I received an AR aging from a client. The current format
(BEFORE) is:
Customer | Aging
-----------|---------------------------------------
ABC Corp | Current $4,200; 30 days $1,850; 60+ $0
XYZ LLC | Current $0; 30 days $920; 60+ $4,400
...
I need it converted to (AFTER):
Customer | Bucket | Amount
----------|----------|--------
ABC Corp | Current | 4200
ABC Corp | 30 days | 1850
ABC Corp | 60+ | 0
XYZ LLC | Current | 0
...
Write Power Query M code (for the Power Query Editor in
Excel or Power BI) that performs this transformation.
Walk me through each applied step in plain English.
Do not invent customer names. The customer column may
contain trailing whitespace — handle it.
Sample input is 47 rows; full input will be 1,200 rows.
Power Query is more reliable than ad hoc formulas for this kind of work because the steps are recorded and reviewable. The "walk me through each step" instruction is your verification — if the model cannot explain a step, that step is suspect.
Every firm writes variance commentary differently. Some firms are clinical ("Revenue increased 14% YoY driven by volume"); others are conversational ("The team delivered a strong quarter, with revenue up 14%"). AI's default voice is somewhere between marketing copy and a PhD dissertation — neither is right for a client-facing financial package. The fix is to give the model three examples of your firm's voice as context.
# Variance commentary prompt with voice examples
You are a senior accountant writing variance commentary for the
December 2025 management financial package for Taxpayer A. Below
are three examples of our firm's commentary voice — match it.
EXAMPLE 1 (G&A line, prior month):
"G&A spend was $412k in November, $38k above budget. The variance
was driven by a $30k professional-services true-up for the audit
engagement and an $8k Q4 software-license renewal. Both were
expected; both were timing-only and reverse against December
forecast."
EXAMPLE 2 (Revenue line, prior month):
"Revenue closed at $1.84M, +6% to plan. The growth was led by
the SMB segment (+$60k vs plan) with mid-market roughly on
plan. We expect December to mirror November given the renewal
calendar."
EXAMPLE 3 (Headcount, prior month):
"Headcount ended November at 47, one above plan. The variance
is the new senior account executive who started two weeks
ahead of schedule — no impact on full-year cost run-rate."
Now write the December commentary for these lines (data from
attached financial pack):
[paste P&L variance table with current month, prior month,
budget, prior year — anonymized]
Use 3 to 5 sentences per line. Tie each variance to a driver,
not just a number. Flag any driver you cannot identify from
the data.
The three-example pattern (sometimes called "few-shot" prompting) is the single most important technique for getting AI to write in a recognizable voice. Two examples is not enough; three is the sweet spot. Five examples often confuses the model with conflicting signals.
If your firm has Microsoft 365 Copilot for Business or Copilot for Microsoft 365, use it for any work that touches a client file. The data-protection terms are stronger, the data does not leave your tenant by default, and the integration with Excel is meaningfully better — Copilot can read the workbook directly, write formulas in place, and create new sheets without copy-paste.
Use external ChatGPT or Claude when you need the smartest model available (the frontier models in the consumer products are typically a generation ahead of what is in Copilot at any given moment) and when you can sufficiently anonymize the data. The trade-off is real: better intelligence vs. tighter data controls. There is no universally right answer; there is the right answer for the specific procedure.
An AI-generated formula in a client deliverable is your formula. If it is wrong, "ChatGPT said so" is not a defense — under AICPA Rule 1.300 (General Standards) and Rule 2.300 for non-attest services, you remain responsible for due professional care. Three verification steps catch almost all formula errors.
First, the explanation test: the model must be able to explain each piece of the formula in plain English, and the explanation must match what the formula actually does. Second, the boundary test: pick a few rows and compute the expected result manually, then compare to the formula's output. Third, the break test: deliberately introduce edge cases (empty cells, negative numbers, zero values) and confirm the formula handles them sensibly.
Before moving to Day 5, you should be able to answer: