Select an audit sample in Excel with Copilot: risk and random

An audit sample has to be defensible: every item selected for a reason, and the whole selection reproducible by someone else from the same register. Copilot in Excel helps build exactly that in about 30 minutes. It writes formulas that flag transactions against your risk criteria and a random draw for the rest, and it explains how to freeze the result. You set the criteria and document the method under ISA 530.

Prepare the register

Export the transaction register for the period from the ledger into Excel. Replace counterparty names with codes, since the audit client's counterparties and account numbers go into cloud services only in coded form. Format the range as a table with Ctrl+T, headers in the first row. Copilot works only with data formatted as a table, and the file must sit in OneDrive or SharePoint with AutoSave on.

Set the criteria before asking

Decide the materiality threshold and the risk criteria yourself: large amounts, round amounts, transactions on weekends, new counterparties, and whatever your risk assessment points to. These are audit judgments; Copilot's job is turning them into formulas.

Role: you are an auditor selecting a sample under ISA 530. Context: the table has columns: {{list of columns}}. Number of transactions: {{number}}. Materiality threshold: {{amount}}. Risk criteria: {{criteria}}. Task: write Excel formulas with my locale's separators that flag transactions for each risk criterion, and a formula to randomly select {{size of the random sample}} transactions from the rest. Explain how to freeze the random values. Format: a table "criterion, formula, column to paste it into", then the steps to freeze the sample and a description of the method for the working paper. No long dashes.

"My locale's separators" matters: a formula with commas fails in a locale that uses semicolons.

Check the flags

Test each flag formula on control rows. Copilot confuses absolute and relative references and sometimes the direction of a condition, flagging amounts below the threshold instead of above. Pick a few rows you know should be flagged and a few that should not, and confirm. For round amounts, check how the formula defines "round"; the definition belongs in your method description.

Freeze the random sample

The random draw is the step that usually breaks reproducibility. Random functions recalculate whenever the workbook changes, so the sample on screen tomorrow differs from today's. Once drawn, copy the random column and paste it as values. Record how you drew it, so another auditor can see the method even if they cannot regenerate the same set. If you use the =COPILOT() function anywhere in the file, save its results as values too, since it may return a different result on recalculation.

What to do

1. Export the transaction register to Excel and replace counterparty names with codes.
2. Format it as a table and save the file in OneDrive or SharePoint with AutoSave on.
3. Set the materiality threshold and risk criteria.
4. Ask Copilot for flag formulas and a random selection formula with the prompt above.
5. Test each flag on control rows.
6. Draw the random sample and paste it as values.
7. Collect the sample on a separate sheet with a selection reason for each item and save the method description.

A defensible sample

Every transaction in the sample has a selection reason, all transactions above the threshold are included, and the method can be reproduced on the same register.

Licenses

Copilot in Excel is not sold separately. It comes with Microsoft 365 Copilot at 30 USD per seat with annual billing, where data stays inside the corporate tenant, or with Copilot Pro at 20 USD per month for individuals with Microsoft 365 Personal or Family. Audit client data belongs in the corporate tenant. See Copilot in Excel pricing, the auditor's version of this task and Copilot in Excel vs Julius AI for large exports.