An AI assistant inside Excel is useful in audit testing for one thing above all: it turns a plain-English description of a test into a formula, a helper column or a pivot table, and explains what it built. It works only on the rows in the sheet, and it can hand you a wrong formula with complete confidence, so the auditor still owns the extract, the logic and the result.
This guide gives eight tests internal auditors run every quarter, each with the ask in plain words and the classic formula a good answer should look like. It applies to Microsoft Copilot in Excel where your organisation has it enabled, and equally to typing a formula question into ChatGPT, Claude or Gemini and pasting the answer back into your workbook.
Start here:
| You want | Use |
|---|---|
| A ready duplicate-payment test file instead of building one | Duplicate Payment Test Kit |
| Journal entry risk criteria and scoring | Journal Entry Risk Scorer |
| A sample size where you are not testing every row | Audit Sampling Calculator |
| The right SAP tables to ask for before you open Excel | SAP Audit Data Request Tables |
What an AI assistant in a spreadsheet is good for
| Job | What it does well | What you still check |
|---|---|---|
| Write a formula | Turns "flag payments on a Sunday" into a working formula | The logic, on rows where you already know the answer |
| Explain a formula | Breaks a nested formula from last year's file into steps | That the explanation matches what the formula returns |
| Build a test column or a pivot | Adds a helper column, groups by vendor or month | Ranges cover every row; no filter is hiding data |
| Spot duplicates and gaps | Suggests the cleaning steps and the count | Whether the "duplicate" is a real one after you open the voucher |
| Summarise a filtered list | "38 exceptions, 6 vendors, largest ₹4.2 lakh" | Every figure, against the sheet's own totals |
| Draft a test note | Objective, population, logic, result, in a few lines | That it describes what you did, not what it assumes you did |
None of this needs the newest feature. The formulas below are standard Excel, and a tool that proposes something far more elaborate for the same test deserves a second look.
How to ask: describe the columns, do not paste the data
A formula question needs the layout of the sheet, not its contents. This ask works in any assistant:
I am testing a vendor payment register in Excel. Columns: A vendor code,
B vendor invoice number (text), C invoice date, D amount, E payment date,
F voucher number. Data is in rows 2 to 5000, one header row.
Write one formula for cell H2, to be filled down, that [describe the test].
- Use standard functions only, such as COUNTIFS, SUMIFS, IF, WEEKDAY,
TRIM, SUBSTITUTE, UPPER, XLOOKUP or VLOOKUP.
- Use absolute references for the ranges.
- Explain each part in one line.
- Give me three test rows with the result the formula should return.
- Mark every assumption you make about my data as [ASSUMPTION].
- Do not cite any law, section or standard number.
The examples below use that layout: vendor code in A, vendor invoice number in B, invoice date in C, amount in D, payment date in E, voucher number in F, rows 2 to 5000. Replace 5000 with your last row.
Eight audit tests, the ask and the formula to expect
1. Duplicate invoice numbers after cleaning
Ask: "Flag invoices where the same vendor has the same invoice number, ignoring spaces, hyphens, slashes and capital letters."
Expect: a cleaned key in one column and a count in the next.
H2: =A2&"#"&UPPER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(B2)," ",""),"-",""),"/",""))
I2: =COUNTIFS($H$2:$H$5000,H2)
Any row where I is more than 1 is a candidate. Joining the vendor code to the cleaned number stops two vendors with the same invoice number from matching each other.
A filled check, with illustrative rows:
| Vendor code | Invoice number as entered | Key in H | Count in I |
|---|---|---|---|
| V1001 | INV-0045 | V1001#INV0045 | 3 |
| V1001 | inv 0045 | V1001#INV0045 | 3 |
| V1001 | INV/0045 | V1001#INV0045 | 3 |
| V1002 | INV-0045 | V1002#INV0045 | 1 |
This does not catch INV-0045 against INV-45 (dropped zeros) or a mistyped digit. Those need a second test on vendor, amount and date, which is test 5.
2. Gaps in a voucher sequence
Ask: "My voucher numbers should run in sequence. Show me where numbers are missing."
Expect: sort the numbers, then compare each with the one above. With voucher numbers sorted ascending in F:
G3: =IF(F3-F2>1,"Gap of "&(F3-F2-1)&" after "&F2,"")
If one voucher has several lines, build a clean list first with =SORT(UNIQUE(F2:F5000)) on a new sheet and run the comparison on that list. If the number carries a prefix such as a series or year, pull the running number into its own column first; for a fixed five-digit ending, =VALUE(RIGHT(F2,5)) does it. Test each voucher series separately. A gap is a question for the accountant (cancelled, deleted or never used), not a finding.
3. Payments on weekends or holidays
Ask: "Flag payments dated on a Saturday, Sunday or a date in my holiday list."
Expect: WEEKDAY with return type 2, where Monday is 1 and Sunday is 7, and a count against a holiday list kept on a sheet named Holidays.
H2: =IF(OR(WEEKDAY(E2,2)>5,COUNTIFS(Holidays!$A$2:$A$30,INT(E2))>0),"Check","")
If the office works on Saturdays, change >5 to =7. The INT strips a time stamp from the date so that it matches the holiday list. If the formula returns an error, the dates are stored as text and need converting first.
4. Round-sum journal entries
Ask: "Flag journal amounts that are exact multiples of ₹10,000."
Expect:
H2: =IF(AND(ABS(D2)>=10000,MOD(ABS(D2),10000)=0),"Round","")
Round amounts are normal for some entries (loan instalments, fixed retainers), so pair the flag with who posted, when, and to which account. The Journal Entry Risk Scorer lists the other criteria worth combining.
5. Same-amount payments to one vendor within N days
Ask: "For each payment, count other payments of the same amount to the same vendor within 7 days before or after."
Expect: COUNTIFS with two date conditions, minus one for the row itself. With the number of days in K1:
H2: =COUNTIFS($A$2:$A$5000,A2,$D$2:$D$5000,D2,$E$2:$E$5000,">="&(E2-$K$1),$E$2:$E$5000,"<="&(E2+$K$1))-1
Anything above zero is a pair to open. Monthly rent and retainers will show up if K1 is 30 or more; start with 7. The full set of duplicate tests, and how duplicates arise, is in the Duplicate Payment Test Kit.
6. Ageing buckets
Ask: "Put each outstanding bill into an ageing bucket by days overdue as on 30 September 2026."
Expect: on the bill-wise outstanding list, with the due date in C, the amount in D and the as-on date in K2:
J2: =$K$2-C2
L2: =IF(J2<0,"Not due",IF(J2<=30,"0 to 30 days",IF(J2<=60,"31 to 60 days",IF(J2<=90,"61 to 90 days",IF(J2<=180,"91 to 180 days","Over 180 days")))))
Then a pivot table with party in rows, bucket in columns and sum of amount in values. To tie one bucket to the pivot: =SUMIFS($D$2:$D$5000,$L$2:$L$5000,"Over 180 days"). Say in your test note whether ageing runs from the due date or the invoice date; the two give different answers and the company's own ageing report uses one of them.
7. Split purchases just under an approval limit
Ask: "The approval limit is in K3. Flag purchase orders below the limit where the same vendor's orders within 7 days add up to the limit or more."
Expect: on the purchase order register, with vendor in A, PO date in C and PO value in D:
H2: =IF(AND(D2<$K$3,SUMIFS($D$2:$D$5000,$A$2:$A$5000,A2,$C$2:$C$5000,">="&(C2-7),$C$2:$C$5000,"<="&(C2+7))>=$K$3),"Check","")
A second view counts each vendor's orders that sit between 90% of the limit and the limit:
I2: =COUNTIFS($A$2:$A$5000,A2,$D$2:$D$5000,">="&0.9*$K$3,$D$2:$D$5000,"<"&$K$3)
If the register has the requester or the approver, repeat the test by that column. Splitting is usually done by a person, not a vendor.
8. Benford-style first-digit count
Ask: "Count how many amounts start with each digit 1 to 9 and compare with the Benford expected share."
Expect: a first-digit column, a count, and the expected share from LOG10(1+1/digit).
J2: =VALUE(LEFT(ABS(D2),1))
Then a small table with the digits 1 to 9 in M2 to M10:
N2: =COUNTIFS($J$2:$J$5000,M2)
O2: =N2/SUM($N$2:$N$10)
P2: =LOG10(1+1/M2)
| First digit | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
|---|---|---|---|---|---|---|---|---|---|
| Expected share | 30.1% | 17.6% | 12.5% | 9.7% | 7.9% | 6.7% | 5.8% | 5.1% | 4.6% |
Leave out zero amounts and amounts below 1. The pattern holds for large sets of naturally occurring amounts that span several sizes; it does not hold for fixed prices, capped amounts or a small list. A digit that stands out tells you where to look (often just under an approval limit), and proves nothing by itself.
After the tests: pull the exceptions and write the note
Where your Excel has dynamic array functions, =FILTER(A2:I5000,I2:I5000>1,"None") on a new sheet lists the exceptions from test 1 and keeps the original data untouched; otherwise filter and copy. To bring a vendor name from the master, =XLOOKUP(A2,Vendors!$A$2:$A$2000,Vendors!$C$2:$C$2000,"Not in master") also shows payments to codes missing from the master. The older equivalent is =VLOOKUP(A2,Vendors!$A$2:$C$2000,3,FALSE).
Then ask the assistant for a test note with five headings: objective, source and control totals, logic (the formula itself), result, and what was done with each exception. Fill the last one yourself.
The limits
It sees the rows in the sheet, not the population. If the extract left out one company code, one voucher type or the last week of the period, every test above runs cleanly on incomplete data. Completeness is the auditor's job. Before testing, agree the row count and the total of the amount column to the ledger or trial balance, and check the earliest and latest date with =MIN(C2:C5000) and =MAX(C2:C5000). From Tally, that means a Day Book or ledger voucher export for the full period with voucher type and voucher number; from SAP, the right tables and fields, which differ between ECC and S/4HANA and with custom fields. See SAP tables for internal audit.
It can produce a wrong formula confidently. Common faults: a range that stops short of the last row, relative references that drift when filled down, dates treated as text, a count that includes the row itself, and criteria that behave differently on numbers stored as text. Test every formula on a few rows where you know the answer, including one that should be flagged and one that should not, and keep those rows in the working paper.
Spreadsheets have their own traps. Filters hide rows from your eye but not from a formula. Merged cells and subtotal rows inside the data break ranges. A very large file may be cut off on export. If a summary from the assistant does not agree with the sheet's own total, the sheet wins.
A flag is not a finding. Each test produces candidates. The observation comes after you open the voucher, speak to the process owner and rule out the ordinary explanation.
Client and employee data rules still apply. An assistant built into your organisation's Excel and a consumer chat tool are not the same thing; ask IT what is enabled and what the settings allow, and check your plan's data-use and retention settings. For a formula question you do not need to paste data at all, which is why the ask above describes columns only. Vendor bank details, PAN, salary and customer lists are personal or confidential data and stay out of consumer tools without authority.
Full coverage needs a fixed rule, not a chat. When the same eight tests have to run on every transaction, every month, across entities, a workbook maintained by hand becomes the weak point. That is the job of a system that applies fixed rules to the whole of the books and traces each exception to its voucher, which is what CORAA does. How drafting tools and testing tools differ is set out in AI in internal audit 2026.
Copilot in Excel for audit FAQ
Can Copilot in Excel be used for internal audit testing in 2026?
Yes, where your organisation has it enabled, for writing and explaining formulas, building helper columns and pivots, and summarising a list. It does not check that your extract is complete and it can return a wrong formula, so every result is verified on known rows before it goes into the file.
Which Excel formulas are most useful for audit testing?
COUNTIFS and SUMIFS do most of the work: duplicates, same-amount payments within a date window, split purchases and ageing totals. Add WEEKDAY for weekend postings, TRIM, SUBSTITUTE and UPPER for cleaning invoice numbers, XLOOKUP or VLOOKUP for master data, and a pivot table for summaries.
Can I paste audit data into ChatGPT to get an Excel formula?
You do not need to. Describe the columns and the test, and ask for the formula with test rows. Company, vendor and employee data should not go into a consumer AI tool without authority; check your plan's data-use and retention settings and your organisation's policy.
How do I know an AI-written Excel formula is correct?
Test it on rows where you already know the answer, including one that should be flagged and one that should not. Check that the ranges reach the last row, that references are absolute where they should be, and that the count of exceptions agrees with a manual filter.
Is testing in Excel the same as testing the full population?
Only if the sheet holds the full population and you have proved it with control totals. Excel tests whatever rows you extracted. Where you cannot test every row, decide the sample with a stated basis; the Audit Sampling Calculator helps with the size.