Audit Checklist Excel: Build and Use a Reliable Template

How-to basics · Updated November 21, 2026

Yes—an Excel audit checklist can organize audit questions, evidence, results, owners, due dates, and corrective actions in one workbook. Set it up as a structured table with a dropdown for each item’s status, then use filters and simple formulas to monitor findings and progress.

What an Excel audit checklist is for

An audit checklist is a working document for recording whether defined requirements or control points have been examined and what evidence supports the result. In Excel, it can serve as a reusable template for internal reviews, process checks, supplier assessments, inventory counts, or quality inspections. The appropriate questions depend on the audit’s purpose, scope, and criteria; there is no single checklist that is suitable for every organization or audit type.

A checklist helps keep work consistent, but it does not replace judgment, evidence gathering, or a formal audit plan. A checked box alone does not show that a control works. Record what was reviewed and, when a requirement is not met, describe the gap and how it will be addressed. For regulated or certified work, compare the checklist with the applicable current requirements and your organization’s approved procedures rather than assuming a generic spreadsheet establishes compliance.

Choose the checklist scope before designing it

Start by writing down the audit’s objective, the process or location being reviewed, the period covered, and the criteria used to evaluate it. Decide who will complete the checklist and who will review findings. A checklist for a monthly equipment inspection will need different questions and evidence fields from a review of purchasing approvals, even if both use the same basic workbook structure.

Keep each checklist item specific enough to assess consistently. Instead of “Purchasing controls are effective,” use a testable prompt such as “For the selected purchase orders, is documented approval present before the order date?” Add the sample or evidence expectation where useful. Break a broad requirement into smaller checks if a single Pass or Fail result would hide materially different outcomes.

  • Define the review’s boundaries: what is included, excluded, and covered by the dates.
  • Identify the criteria or procedure that each question relates to.
  • Decide whether the result options should include N/A, and define when that option is appropriate.
  • Separate audit findings from the action plan for fixing them; the reviewer and action owner may be different people.

Plan useful columns and workbook tabs

A practical checklist table can use one row per audit question and columns such as ID, area, question, criteria or evidence expected, evidence examined, result, finding, action owner, due date, and closure date. Include an audit name, site or department, auditor, and audit dates in a small cover section or a separate summary tab. Give each item a stable ID, such as PROC-01, so a finding can be referenced in a report or follow-up review.

Use distinct fields for the audit result and corrective-action status. “Fail” describes the result of a test; “Open,” “In progress,” and “Closed” describe the progress of a response. Combining them in one status field can make summaries misleading: an audit item may remain a finding even after its action is closed, while an open action may need separate follow-up evidence. If the workbook supports several audits, consider a separate audit identifier column or one workbook per engagement, depending on access and reporting needs.

A typical workbook might have a Read Me tab for instructions, a Checklist tab for audit work, and a Summary tab for counts and follow-up. Avoid making the summary more elaborate than the underlying data. Every displayed metric should have a clear definition, especially whether N/A, blank, or in-progress items are included.

Build the Excel table and status dropdown

In Excel for Microsoft 365 on Windows, enter the headers and checklist rows, select the range, and choose Insert > Table. Confirm that My table has headers is selected. Tables expand when new rows are added and provide built-in header filters. To create one with a keyboard shortcut on Windows, select the range and press Ctrl+T; on Mac, use Insert > Table if the shortcut differs in your setup. Give the table a descriptive name under Table Design so formulas and references are easier to understand.

To create a consistent result dropdown, enter the allowed options in an unused area—for example, J2:J6: Not started, In progress, Pass, Fail, and N/A. Select the result cells, choose Data > Data Validation, set Allow to List, and use =$J$2:$J$6 as the source. The list range can be placed on a separate lookup tab if you prefer to keep the checklist tidy. A range-based source is easier to maintain than typing a long list into each validation rule.

Use Home > Conditional Formatting to apply visual cues to results, such as a distinct fill for Fail or a muted fill for N/A. Keep colors supplementary: retain clear text labels so the workbook remains understandable when printed or viewed by someone who cannot distinguish the colors. Freeze the header row with View > Freeze Panes > Freeze Top Row, and use the table filters to show a single area, owner, or result at a time.

Use formulas to summarize results

Formulas are most useful when their meaning and range are visible. In the examples below, assume the result dropdown is in G2:G101, with one audit item per row. To count failed items, enter =COUNTIF(G2:G101,"Fail") in a summary cell. If there are four Fail entries in that range, the result is 4. Expand the range or use a structured table reference if the checklist may grow; a fixed range will not count rows beyond row 101.

For a completion percentage, define completion as the share of in-scope items marked Pass or Fail, excluding N/A, Not started, and In progress items from the completed count and denominator. With results in G2:G101, enter =IFERROR((COUNTIF(G2:G101,"Pass")+COUNTIF(G2:G101,"Fail"))/(COUNTIF(G2:G101,"Pass")+COUNTIF(G2:G101,"Fail")+COUNTIF(G2:G101,"Not started")+COUNTIF(G2:G101,"In progress")),0) and format the cell as a percentage. For example, 18 Pass, 2 Fail, 5 Not started, and 5 In progress items produce 20 completed out of 30 in-scope items, or 66.7%. If no items have one of those four statuses, IFERROR returns 0 rather than a division error.

For action tracking, suppose F2:F101 contains action due dates and G2:G101 contains action statuses Open, In progress, or Closed. To count dated, nonblank actions that are past due and not Closed, use =COUNTIFS(F2:F101,"<"&TODAY(),G2:G101,"<>Closed",G2:G101,"<>",F2:F101,"<>"). If three actions meet those conditions, the formula returns 3. TODAY updates with Excel’s date recalculation, so confirm the workbook’s calculation settings and date values when interpreting the count. Keep action-status values separate from the audit-result values when applying this formula.

Record evidence and manage findings

For each test, record enough context for another reviewer to understand what was checked. That may include a document name and date, transaction identifier, system report, observation, or a restricted link to supporting material. Avoid vague notes such as “looks fine.” A useful finding states the condition observed, the requirement or expectation, and the relevant evidence. The cause and recommended response can be added where known, but do not present an assumption as a verified cause.

For corrective actions, capture a responsible owner, target date, current status, and closure evidence. Define what counts as closure—for example, implementation plus verification, where that is required by the organization’s process. An owner’s “done” update may not, by itself, show that the issue was resolved. A follow-up field or separate action log can help when one finding has several actions or when a corrective action remains open after the audit fieldwork ends.

Common spreadsheet problems to prevent

  • Inconsistent labels: “In progress,” “In Progress,” and “Working” may be treated as different text by formulas. Use dropdowns and avoid manual variations.
  • Blank rows inside the data: They can complicate filtering and formulas. Keep checklist records in a continuous table and add new items as table rows.
  • Unclear N/A decisions: State why an item is not applicable, or require a short explanation in a notes field. Otherwise, N/A can conceal an unchecked requirement.
  • Overloaded questions: One item that tests several controls can produce ambiguous results. Split it into separate checks when each part needs its own conclusion.
  • Stale templates: Procedures, owners, systems, and requirements change. Add a template version or review date and have the responsible process owner approve substantive updates.
  • Unexplained metrics: A completion rate can look high even when important items are still open. Show the counts behind the percentage and document which statuses it includes.

Before relying on a checklist, test it with a few sample rows: try each dropdown option, filter by status, add a row, and check that summary values update as intended. Review formulas for the actual table size and confirm that dates are stored as dates rather than text. These basic checks can reveal errors before the workbook is used for reporting.

Sharing, access, and recordkeeping

Audit workbooks may contain personal, financial, operational, or security-sensitive information. Store them only in an approved location, share access with people who need it, and follow your organization’s retention and records rules. Do not treat hiding a tab, locking a cell, or protecting a worksheet as a substitute for controlling access to the file. Worksheet protection can help prevent accidental edits, but it is not a way to keep sensitive information confidential.

For collaborative work, use an approved shared location and agree how concurrent edits, review comments, and final versions will be handled. Keep a clear record of significant changes to the checklist itself, not just the audit answers. Before distributing a copy, inspect it for hidden information, unnecessary personal data, broken links, and draft notes that were not intended for recipients. If the workbook is an official audit record, follow the organization’s approved process for finalizing and retaining it.

When Excel is a good fit—and alternatives

Excel is often a reasonable choice for a small or moderately sized checklist when the team already uses spreadsheets, the workflow is straightforward, and a designated person can maintain the template. It supports sorting, filtering, formulas, and printable views without requiring a specialized audit application. A carefully designed workbook may also be useful for a one-time review or a prototype before a process is standardized.

A spreadsheet becomes harder to manage when many people edit at once, approvals must follow a controlled sequence, evidence needs formal version history, or audit trails and access controls must be enforced consistently. In those cases, consider an organization-approved audit-management, workflow, or document-management system. A form-based tool may suit simple data collection, while a database-backed system may better support many audits and linked actions. Compare the available options against real requirements, costs, permissions, reporting, retention, and export needs; changing tools does not remove the need for clear criteria and reliable evidence.

Frequently Asked Questions

Is there a built-in audit checklist template in Excel?
Excel’s available templates can vary by version, account, and organization. You can search the template gallery, but review any result carefully: a generic template may not match your audit scope or criteria. A tailored table with defined questions, evidence, and statuses is often more useful.

What should an audit checklist include?
At minimum, include the audit scope or identifier, clear questions, applicable criteria, evidence or notes, a result, and enough ownership and date information to follow up on findings. Add fields based on the audit’s purpose rather than adding columns that nobody will maintain.

How do I make an audit checklist dropdown in Excel?
Put the allowed status labels in cells, select the result range, then choose Data > Data Validation, set Allow to List, and point Source to the cells containing the labels. A cell-range source makes it easier to update the options consistently.

Can Excel show overdue audit actions?
Yes. Store valid due dates and action statuses in separate columns, then use a COUNTIFS formula to count past-due actions that are not closed. Confirm that the status labels and date range in the formula match the workbook, and remember that TODAY changes as the date advances.

Is an Excel checklist enough for a compliance audit?
Not by itself. Excel can organize questions and evidence, but the checklist must reflect the applicable criteria and be supported by appropriate audit work, records, and review. For regulated or formally certified processes, use the organization’s approved procedures and consult the responsible compliance or audit professionals.

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.