How to Create a Chart Audit Form in Excel: Step by Step

Charts & pivot tables · Updated November 20, 2026

Create a chart audit form in Excel by listing each audit as one row, adding consistent Yes/No/N/A fields for chart criteria, and using formulas to calculate a completion rate. Before you start, agree on what will be checked, who will use the form, and how chart references will be protected. The example below is designed for healthcare chart reviews, but you can adapt the same structure to other records.

What You’ll Need

  • Microsoft Excel for Microsoft 365 on Windows. The ribbon commands described are also available in Excel for Mac, although some shortcuts differ.
  • A written list of audit criteria and clear definitions for Yes, No, and N/A. Have the appropriate clinical, quality, or compliance lead review the criteria before the form is used.
  • A secure, approved location for the workbook and a privacy-safe way to identify records. Follow your organization’s rules; avoid entering patient names or other unnecessary identifying information.
  • A target score, if your process uses one. The example uses 90% only to demonstrate how a threshold can work; replace it with the target approved for your audit.

Step-by-Step

  1. Plan the form and its scoring before opening Excel. Decide whether each row will represent one chart review, which fields reviewers need, and how they should handle a criterion that does not apply. For a simple form, use Yes for met, No for not met, and N/A for not applicable. Define each criterion in plain language—for example, whether “medication list reconciled” means it is present, current, and documented at the encounter being reviewed. Avoid combining several checks into one vague question. If reviewers cannot agree on what a Yes means, refine the wording or provide instructions before collecting results.
  2. Create a workbook with separate instruction and data sheets. Open a blank workbook and rename Sheet1 to Audit Log by double-clicking its sheet tab. Add another sheet with the plus button and name it Instructions. On that sheet, record the audit purpose, review period, criteria definitions, who is authorized to access the workbook, and where to ask questions. Add a note that the form is an audit aid, not a substitute for clinical judgment or your organization’s policies. Keep the instructions concise enough for auditors to consult while working.
  3. Set up the Audit Log column headings. In row 1 of Audit Log, enter these headings, one per column: A Audit ID; B Audit date; C Auditor; D Department/clinician; E Chart reference; F Encounter date; G Patient identifiers verified; H Consent documented; I History current; J Medication list reconciled; K Allergies recorded; L Assessment documented; M Plan documented; N Follow-up documented; O Required signatures present; P Timely entry; Q Applicable items; R Compliant items; S Compliance rate; T Result; U Findings / corrective action. Adjust the criteria to fit your approved audit scope. Use a coded or de-identified chart reference if permitted, and do not include details in the findings column that are not necessary for follow-up. Keep each criterion as a separate column so results can be filtered and summarized.
  4. Format the headings and make the log easier to use. Select A1:U1, choose Home > Wrap Text, and apply a clear fill and bold font. Widen columns that need space, such as Findings / corrective action, and freeze the headings with View > Freeze Panes > Freeze Top Row. Format B and F as dates using Home > Number Format; format S as Percentage. If long text makes the sheet hard to scan, set a reasonable column width and use Wrap Text rather than making every column extremely wide. You can hide or group columns Q:T later if reviewers should focus on entering information, but ensure they remain available to the person responsible for the workbook.
  5. Add dropdowns for the audit criteria. Select G2:P2, then choose Data > Data Validation. Under Allow, choose List; in Source, enter Yes,No,N/A, and select the in-cell dropdown option if shown. Choose OK. After testing the list in row 2, copy G2:P2 and paste validation down the range you expect to use, or apply it to a generous bounded range such as G2:P500. Avoid applying it to entire columns if that makes the workbook slow or cumbersome. You can also add validation to the date columns by selecting the intended range and choosing Data > Data Validation, then allowing dates within a sensible review period. Dropdowns reduce spelling variations, which is important because the scoring formulas depend on the exact values.
  6. Enter the scoring formulas and set the threshold. On the Instructions sheet, put the approved minimum rate in B2 and label A2 clearly, such as Minimum compliance rate. Format B2 as Percentage. The following formulas assume that the ten audit criteria are in G:P and the first audit record will be on row 2. In Q2, count criteria marked Yes or No as applicable: =COUNTIF(G2:P2,"Yes")+COUNTIF(G2:P2,"No"). In R2, count the Yes responses: =COUNTIF(G2:P2,"Yes"). In S2, divide compliant items by applicable items, leaving the cell blank if none have been answered: =IFERROR(R2/Q2,""). This assumes the same G2:P2 criteria and the calculated values in Q2 and R2. In T2, show a blank result until a score exists, then compare the score with the threshold in Instructions!B2: =IF(S2="","",IF(S2>=Instructions!$B$2,"Pass","Review")). This assumes the threshold is stored in Instructions!B2. For example, eight Yes responses, one No, and one N/A give Q2 = 9, R2 = 8, and S2 = 88.9%; with a 90% threshold, T2 returns Review. Copy Q2:T2 down as needed and verify the result with a test row before using real audit data. If your Excel uses a different regional formula separator, Excel may require semicolons instead of commas.
  7. Turn the entry area into an Excel Table. Select A1:U2, including the headings and formula row, then press Ctrl+T on Windows and confirm that My table has headers is selected. On Mac, use Insert > Table if the shortcut differs in your setup. A table provides filter buttons, alternating row shading, and helps carry formulas and formatting into new rows. Give the table a descriptive name from the Table Design tab, such as ChartAudits. Keep the formula columns in the table and check that a newly added record receives formulas in Q:T. If it does not, copy the formulas down or confirm that the calculated-column option is enabled in Excel’s table behavior.
  8. Make results readable, then test the whole workflow. Select the result cells in T and choose Home > Conditional Formatting > Highlight Cells Rules to apply distinct formatting to Pass and Review, or use Home > Conditional Formatting > New Rule for a custom rule. Do not rely on color alone; the text result should remain visible. Enter a fictional test record with a mix of Yes, No, and N/A, confirm the counts and rate by hand, and check that changing the threshold changes the result as expected. Test a row with all criteria blank as well. Save the workbook in the approved location, then provide reviewers with the Instructions sheet and a clear process for entering, reviewing, and correcting records.

Common Mistakes

  • Using unclear criteria. “Complete chart” can mean different things to different reviewers. State what evidence qualifies as Yes and what should be marked N/A, and keep a written definition beside the form or on the Instructions sheet.
  • Counting N/A as a failure. The example formulas exclude N/A from the denominator. If your organization uses a different scoring method, update the formula and document the method before comparing results.
  • Using inconsistent response text. Values such as Yes, Y, and Compliant will not all be counted by a formula looking for Yes. Use dropdowns and avoid manually typing alternate labels.
  • Putting identifiable details in the workbook unnecessarily. A chart reference can still be sensitive. Use approved storage, permissions, retention, and de-identification practices; do not email or share the file outside authorized channels.
  • Treating a passing score as proof that every requirement was met. A percentage can hide a serious individual finding. Decide whether certain failures require escalation regardless of the overall score, and make that process explicit.

Tips

  • Keep the reviewer entry fields together and calculated fields at the end. Consider using a distinct fill for formula columns and explaining that users should not overwrite them.
  • Use one row per chart audit and one column per criterion. This structure is easier to filter than putting multiple audits or criteria inside one cell.
  • Keep the audit period, audit ID, auditor, and chart reference consistent. A unique audit ID helps distinguish two reviews of the same record without exposing extra details.
  • Use Excel’s table filter arrows to focus on a department, date range, or Review result. Before sharing a filtered workbook, check whether hidden rows could confuse recipients.
  • Protect formulas if accidental edits are a concern: unlock intended input cells, then use Review > Protect Sheet. Test the protection settings first so reviewers can still enter data and use the filters they need.
  • Version the criteria and note when they change. Results collected under different definitions may not be directly comparable, even when the column headings look similar.

Troubleshooting

The dropdown arrow or list is missing: Select a criterion cell and open Data > Data Validation to confirm that Allow is set to List and the source reads Yes,No,N/A. If you copied cells from elsewhere, the validation may have been replaced; apply it again to the affected range.

The count or score is wrong: Check that the response cells contain exactly Yes, No, or N/A, and confirm the formulas point to G:P on the same row as the audit. Make sure Q and R are not overwritten with typed values. A blank score is expected when the row has no applicable responses.

The result says Pass when you expect Review: Confirm that Instructions!B2 is a percentage such as 90%, not the number 90, and check that S is formatted as a percentage. The formulas compare stored numeric values, so entering 90 instead of 90% creates a very different threshold.

New table rows do not calculate: Check whether formulas exist in Q:T in the new row. Copy the formulas from the preceding row if necessary, then verify Excel’s table calculated-column behavior. Keep a test record in a copy of the workbook rather than changing live audit results to debug the form.

The workbook is difficult to share or opens with unexpected access: Move it to the organization-approved secure location and check sharing permissions with the workbook owner or IT team. If reviewers need to work separately, agree on a controlled process to combine records and prevent duplicate audit IDs.

Frequently Asked Questions

Should each row represent a chart or an individual audit question?
For the example form, each row represents one chart audit and each criterion has its own column. This makes it easy to review one chart’s score and filter across audits. A question-per-row design can be useful for complex audits with many changing criteria, but it requires a different summary setup.

Can I change the criteria or add more columns?
Yes. Add or rename criteria only after updating the instructions and confirming that the formulas cover the new criterion range. If you add a criterion in Q or later, move the calculated columns so they do not overlap the new input field, then revise and retest the formulas.

Can Excel automatically identify which charts need a follow-up?
The Result column flags records below the threshold, but a reviewer still needs to interpret findings and assign any required action. Add a separate owner, due date, or action-status field if your follow-up process requires tracking those items.

Can I use this form for audits outside healthcare?
Yes. Replace the example chart criteria with checks appropriate to the records you review, define the response options, and confirm the scoring and privacy rules with the responsible subject-matter lead. Do not assume the healthcare example’s criteria or threshold fit another process.

Does this form replace a dedicated audit system?
No. Excel can be suitable for a controlled, appropriately sized log, but it does not by itself establish secure access, approval workflows, or a complete audit trail. Follow your organization’s requirements and use an approved system when the process calls for stronger controls.

Related

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

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

Spotted an error in this guide? Let us know.