Excel Audit Spreadsheet Examples and How to Build Them

How-to basics · Updated November 19, 2026

Useful Excel audit spreadsheet examples include an expense review log, a bank reconciliation, an inventory count comparison, and a user-access review. Each should record what was checked, the evidence, the result, and who followed up; simple formulas can flag missing documents, duplicate IDs, or differences for review.

What an audit spreadsheet is for

An audit spreadsheet organizes records and checks so a reviewer can trace a conclusion back to its source. It can support an internal control review, a month-end reconciliation, a sample test, or a process check. It is not automatically an audit just because it contains financial data: the purpose, scope, criteria, evidence, and review process matter too.

A practical workbook separates source information from conclusions. Preserve the original report or data extract, record its source and date, and do not silently overwrite values to make them agree. A reviewer should be able to see which items were selected, what evidence was inspected, what exceptions were found, and whether those exceptions were resolved.

For most small reviews, one workbook can contain a read-me or scope sheet, a data sheet, and a findings or summary sheet. Larger or sensitive reviews may need controlled storage, restricted access, and a documented process outside Excel.

Example 1: Expense and receipt review

An expense review log can compare transactions with the organization’s documented requirements. Useful columns include transaction ID, date, employee or department, amount, expense category, receipt status, approval status, reviewer, result, and notes. Do not include unnecessary personal or payment-card information in the workbook.

Assume column A contains the transaction ID, B the amount, C the receipt status, and D the approval status, with Yes or No entered in C and D. Put the following formula in E2 and copy it down. For a row where both C2 and D2 are Yes, it returns Complete; if either is No, it returns Follow up.

=IF(AND(C2="Yes",D2="Yes"),"Complete","Follow up")

This flag is a screening aid, not proof that a transaction complies with policy. A reviewer still needs to examine the underlying receipt and approval evidence and apply the relevant criteria. If the policy has additional conditions, such as a spending limit or specific approval level, document and test those separately rather than treating this basic formula as a complete control.

Example 2: Duplicate transaction checks

Duplicate IDs can occur when data is appended more than once, but a repeated ID is a lead to investigate, not proof of duplicate payment. Two legitimate line items might share an invoice number, while a true duplicate might have different identifiers. Compare the amount, date, supplier, and source records before deciding what happened.

Assume transaction IDs are in A2:A100. Enter this formula in F2 and copy it down. If INV-104 occurs twice in that range, the row containing that ID returns Possible duplicate; a blank ID returns a blank result.

=IF(AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1),"Possible duplicate","")

The dollar signs keep the checked range fixed as the formula is copied. In Excel, you can also use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values to visually mark repeated entries. Conditional formatting changes how cells look; it does not determine whether a repeated item is an error.

Example 3: Bank or control-account reconciliation

A reconciliation sheet can list a book balance and a statement balance for each account or period, along with the source, preparer, reviewer, and explanation of reconciling items. Keep the two balances in separate columns and make the sign convention clear. A difference is a prompt for investigation, not an automatic adjustment.

Assume B2 is the book balance and C2 is the statement balance. Enter the following in D2. If B2 is 1,200 and C2 is 1,175, the formula returns 25, meaning the book balance exceeds the statement balance by 25.

=B2-C2

Record explanations and evidence for outstanding checks, deposits in transit, timing differences, or errors as applicable. Do not hide an unexplained difference by changing a source figure or adding an unsupported adjustment. A reviewer should be able to trace each reconciling item to documentation and see how it was cleared in a later period, where relevant.

Example 4: Inventory count comparison

An inventory count template can compare the recorded quantity with the physical count. Include an item code, location, recorded quantity, count date, counted quantity, difference, recount or investigation status, and notes. For sensitive or high-value items, record who counted and who independently reviewed the result.

Assume C2 contains the recorded quantity and D2 the physical count. Put the following in E2 and copy down. If the record shows 40 units and the count is 37, it returns -3, indicating that the count is three units below the record.

=D2-C2

Explain whether positive values mean an excess and negative values mean a shortage. A variance does not reveal its cause: the reviewer may need to check unit-of-measure differences, location transfers, cut-off timing, damaged stock, or data entry. Document the investigation and any approved correction separately from the original count.

Example 5: User-access review

An access review can list the system, user or account identifier, role, owner, business justification, reviewer decision, and review date. Depending on the process, it can also note whether an account is active, whether the person still needs access, and whether elevated permissions were examined. Use an appropriate identifier instead of copying passwords, authentication codes, or unnecessary personal details into the workbook.

For each decision, record enough context to make the result understandable later. “Keep” or “remove” alone may not explain why a role is appropriate or whether an access change was completed. A useful log distinguishes the reviewer’s decision from evidence that an administrator actually made the change, and records when that evidence was checked.

Access data can be sensitive. Store the file only in an approved location, limit permissions to people who need it, and follow the organization’s retention and security requirements. A worksheet password is not a substitute for controlling access to the workbook file or its storage location.

How to structure a reliable audit workbook

Before entering data, define the question the review is intended to answer and the period or population covered. Then keep the workbook understandable to someone who did not prepare it. A straightforward layout often includes:

  • Scope and instructions: purpose, period, criteria, data source, preparation date, and the meaning of status values.
  • Testing rows: one record per item tested, with stable identifiers and separate fields for evidence, result, and notes.
  • Exceptions: a clear description, owner, due date, action, and resolution evidence where follow-up is required.
  • Summary: population size, sample size where applicable, completed checks, exceptions, and unresolved items.

Use Excel Tables for growing lists: select the data and choose Insert > Table, confirming whether the range has headers. Tables make filtering and sorting easier and generally expand as rows are added. Keep a copy of the original source data, note when it was obtained, and avoid mixing raw imported data with edited analysis in the same columns.

For drop-down values such as Yes, No, and Not applicable, select the entry cells and use Data > Data Validation. A consistent set of choices reduces spelling variations that can undermine filters and formulas. Freeze header rows, apply clear date and number formats, and use filters to locate blank or exception statuses. In Excel for Mac, these features are available too, though ribbon placement and dialog appearance can differ. Excel for the web also supports many everyday table and validation tasks, but some advanced workbook features may have different controls.

Sampling, evidence, and review notes

A spreadsheet can list every transaction, but that does not mean every transaction was tested. If the review uses a sample, state how the population was defined, how items were selected, and how many were examined. Do not present sample results as though they establish that every item is correct. Selection methods and conclusions should match the review’s purpose and the applicable professional or organizational requirements.

Evidence should be identifiable and retrievable. A note such as “receipt checked” is less useful than a reference to a controlled file name, report identifier, or document location that the authorized reviewer can access. Avoid relying on fragile links to a person’s desktop. If evidence changes or is replaced, keep a clear record of the version used for the check.

Record exceptions in neutral, specific language: what differed, which criterion applied, what evidence supports the observation, and what remains unresolved. Separate a factual observation from a suspected cause or recommendation. This helps another reviewer assess the work without mistaking an assumption for a verified fact.

Common problems and practical safeguards

Sorting only one column can disconnect values from their records; select the full table before sorting. Pasting new records outside a formula range can cause checks to miss them. Blank cells may also be mistaken for zero, and date values imported as text may sort or calculate unexpectedly. Inspect imported data and test formulas on known examples before relying on a summary.

Formulas and formatting can be changed, overwritten, or deleted. Keep a controlled source copy, use version history where available, and have another person review important calculations and conclusions. Review > Protect Sheet can limit accidental edits, but sheet protection is not strong confidentiality or security. Protect access through approved file permissions, and do not assume a protected worksheet prevents a determined user from accessing its contents.

Use formulas to identify items requiring attention, not to make unsupported conclusions. A red cell is not necessarily an error, and a green cell does not establish that evidence is authentic or complete. Check formula ranges after adding rows, verify totals against the source, and document manual overrides rather than silently replacing formulas.

Frequently Asked Questions

Is there a standard Excel audit spreadsheet template?
There is no single template that suits every audit or review. Build the columns around the objective, criteria, evidence, population, and follow-up process. An expense review, an inventory count, and an access review need different fields.

Can Excel be used for an audit?
Excel can organize data, calculations, testing, and documentation for many reviews. Whether it is suitable depends on the complexity, sensitivity, required controls, and applicable standards. A spreadsheet alone does not establish that an audit was properly scoped or performed.

How do I flag missing audit evidence?
Use a consistent field such as Evidence received with controlled values, then filter for missing or incomplete entries. Define what counts as sufficient evidence and verify the documents; a filled-in status alone does not demonstrate that evidence was reviewed.

How do I find duplicate entries in Excel?
Use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values for a visual check, or use a COUNTIF formula to flag repeated identifiers. Investigate repeated rows against their details before labeling them errors.

Should I use a separate sheet for exceptions?
A separate exceptions sheet can help manage owners, due dates, and resolution evidence, especially when many items need follow-up. Keep a stable link or identifier back to the tested transaction so the exception remains traceable to its source row.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.