How to Create an Audit Checklist in Excel (With Easy Steps)

If you want to create an audit checklist in Excel, you have come to the right place. Here, we will walk you through some easy steps to do the task smoothly.

What Is an Audit Checklist?

An audit checklist is a bundle of questions that must be answered to confirm whether the requirements or quality of an organization are being met. It ensures the auditor that nothing is overlooked or missed from checking. It is created during the audit planning stage.

Overview of an Audit Checklist

In the following picture, you can see an overview of an Audit Checklist. Further, we will show you how you can create such an audit checklist in Excel.

How to Create an Audit Checklist in Excel: 6 Easy Steps

In the following picture, you can see the Checklist Questions for an audit. Next, we will make a checklist in Excel by following some easy steps. Here, we used Excel 365. You can use any available Excel version.

Step-1: Adding Developer Tab to Ribbon

In this step, we will add the Developer tab to our ribbon. In general, we add a checkbox from the Developer tab in Excel. Sometimes, the Developer tab is not visible in the ribbon. To make the Developer tab visible, follow the procedures below.

First of all, we will go to the File tab.

Then, select Options.

After that, from the Customize Ribbon >> select Developer.

Moreover, click OK.

Therefore, you will see the Developer tab in the ribbon.

Step-2: Inserting Interactive Checkbox in Excel

In this step, we will insert a Checkbox to create an audit checklist in Excel. The checkbox will be inserted for corresponding Checklist Questions.

In the first place, we will go to the Developer tab >> select Insert.

Then, a drop-down menu will appear.

Afterward, from the Form Controls field >> select the Checkbox icon.

Then, we will put the cursor in the cell where we want to insert the checkbox.

Here, we put the cursor in cell G8.

The cursor will change into a plus (+) sign.

Afterward, we will click on the cell and the checkbox will appear.

Step-3: Editing Checkbox

In this step, we will remove the text from the checkbox so that the created audit checklist looks more eye-soothing.

To do so, press the CTRL key and select the checkbox.
Then, we will right-click on the mouse to open the Context Menu.
Moreover, we will select Edit Text.

After That, select the text with the mouse and press the BACKSPACE key on the keyboard.

This will remove the text from the checkbox.

Hence, you can see the checkbox in cell G8.

Furthermore, we will copy the checkbox to other cells by dragging down the checkbox with the Fill Handle tool.

Therefore, you can see that all the cells from G8 to G16 have a checkbox.

Step- 4: Linking Checkbox to Cells

In this step, we will link the checkboxes to cells. This will make our audit checklist an interlinked checklist.

In the beginning, press the CTRL key >> select the first checkbox.
After that, go to the Formula Bar and type the following formula.

=$H$8

This will link cell H8 with the checkbox of cell G8.

Since the checkbox of cell G8 is unmarked, cell H8 will show FALSE.

After that, when we mark the checkbox of cell G8, cell H8 will return TRUE.

Here, we need to type the formula for each individual cell which is time-consuming. Therefore, we will use the VBA code to link the checkbox to a cell, and hence we will find the condition TRUE or FALSE for the Checklist Questions.

In the next step, we will show how you can use VBA to link multiple checkboxes.

Step-5: Using VBA to Link Multiple Checkboxes with Checklist

You can’t link all cells manually at the same time. You need to repeat the procedure for each checkbox. But using VBA, you can link all checkboxes within a few seconds.

Here, we have marked some of the checkboxes so that it returns TRUE along with FALSE value.

First of all, we will press the ALT+F11 keys to open the Visual Basic window.

You can also do this from the ribbon, for this select the Developer tab >> choose Visual Basic.

At this point, a VBA Editor window will appear.

Then, from Insert >> we will select the Module.

Then, we will type the following code in the Module.

Sub link_check_boxes_to_cells()
Dim i_check As CheckBox
Dim xcol As Long
xcol = 1
For Each i_check In ActiveSheet.CheckBoxes
   With i_check
     .LinkedCell = _
     .TopLeftCell.Offset(0, xcol).Address
   End With
Next i_check
End Sub

Code Breakdown

We take link_check_boxes_to_cells as the Sub.
We declare i_check, and xcol as variables.
Here, xcol = 1 indicates one column to the right of the checkboxes.

If you want to add one column to the left, type xcol = -1. And if you want to add two columns to the right, type xcol = 2.

For loop is used to continue the linking of the checkboxes until the last checkbox is found.

After that, Save the code.
Then, Run the code, the Run button is marked with a red color box for your convenience.
After closing the Visual Basic window >> go back to your worksheet.

Therefore, you will see that all checkboxes are linked.

Step-6: Applying Conditional Formatting in Audit Checklist

In this step, we will apply Conditional Formatting to highlight the marked checkboxes along with their Checklist Questions.

First of all, we will select cells B8:G16.
Then, from the Home tab >> select Conditional Formatting.
Afterward, select the New Rule.

At this point, a New Formatting Rule dialog box will pop up.

Then, we will select Use a formula to detect which cells to format.
Moreover, we will type the following formula in the Format value where this formula is true box.

=$H8

In addition, click on Format.

After that, a Format Cells dialog box will appear.

Then, from Fill >> we will select a color.

Here, we choose a light pink color. You can choose any color according to your preference.

Afterward, you can see the Sample of the color.

Then, click OK.

Furthermore, you can see the Preview of the color >> click OK.

Therefore, you can see that the marked checklist along with their Checklist Questions are highlighted with light pink color.

Hence, you can see the created Audit Checklist in Excel.

Here, we do not need to show the TRUE and FALSE values in the Audit Checklist.

Therefore, we will select cell H8:H16 >> go to the Home tab.
Then, from the Font group >> select Font Color.

This will bring several number of colors.

Here, we will select While as the Font Color to make the cells invisible.

Therefore, you can see that the audit checklist looks more eye-catching.

Practice Section

You can download the above Excel file to practice the explained method.

Download Practice Workbook

You can download the Excel file and practice while you are reading this article.

Creating an Audit CheckList.xlsm

Conclusion

Here, we tried to show you some easy steps to create an audit checklist in Excel. Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, please let us know in the comment section below.

Get FREE Advanced Excel Exercises with Solutions!

2 Comments

Am Oct 2, 2023 at 1:30 AM

Alssalaam
I was looking for how auditors can schedule their audit workload, assign resources like human resources,time etc to meet audit deadlines.

In the process your website came up. However the topic is different.
Can you address the topic i was looking for?
Many thanks
- Reply
  Osman Goni Ridwan Oct 2, 2023 at 6:42 PM
  
  Hello AM!
  Thanks for your comment. To schedule the audit workload and assign resources, you can create an Audit Plan Template. To do this, you have to assign these parameters. I am listing the items with proper descriptions:
  1. Particulars: This column should describe the specific audit item or task that needs to be checked or reviewed. It could be a process, financial statement, or any other aspect of the audit.
  2. Frequency: This column specifies how often the particular item is audited. For example, it might be done annually, quarterly, monthly, or as needed.
  3. Extent of Check: This column outlines the depth or scope of the audit procedure. It could be a high-level review or any other specified level of scrutiny.
  4. Records Available On: This column indicates where the relevant records or documents are available. 5. Target Start and End Date: These columns define the planned dates for starting and completing the audit procedure for the particular item.
  6. Actual Start and End Date: These columns record the actual dates when the audit procedure for the specific item started and ended.
  7. Total Hours: This column can be used to record the total number of hours spent on auditing the particular item.
  8. Staff’s Name: This column mentions the name of the staff or auditor responsible for conducting the audit for the particular item.
  9. Staff’s Initial: This column can be used for the staff member’s initials or code for identification purposes.
  10. Remarks: This column allows auditors to provide comments or notes related to the audit of the specific item. This can include observations, findings, issues, or any other relevant information.
  
  Here, you can see the image of the template that I have made for you. Also, you can download the template from the following link.