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.
Read More: How to Make a Daily Checklist in Excel
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.
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.
Dim i_check As CheckBox
Dim xcol As Long
xcol = 1
For Each i_check In ActiveSheet.CheckBoxes
.LinkedCell = _
- 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.
- 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.
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.
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.