If you are looking for some special tricks to create a QR code Attendance tracking with Excel, you’ve come to the right place. There is one way to create a QR code attendance tracking with Excel. This article will discuss every step of this method to create a QR code attendance tracking with Excel. Let’s follow the complete guide to learn all of this.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article. It contains all the datasets in different spreadsheets for a clear understanding. Try yourself while you go through the step-by-step process.
Step-by-Step Procedure to Create QR Code Attendance Tracking with Excel
In the following section, we will use one effective and tricky method to create an Excel QR code attendance tracking with Excel. This section provides extensive details on this method. You should learn and apply these to improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference
Step 1: Make List of Holidays and Leave Types
To create an attendance tracker, we have to follow some specified rules. First of all, we have to create a worksheet named “Information”. In this worksheet, add the lists of Months, Holidays, and the Type of activities in the institution. You can also add the information of participants’ names and IDs to link to the main worksheet.
After inserting the necessary information, you have to Define their Name for them. Defining the name will allow you to use the Data Validation tool to make a drop-down menu in the cells.
- First of all, you have to define a name for the list of the months.
- To do this, select the cells of months.
- Then, go to the Formula tab and select the Define Name option.
- After that, you will see a window named “New Name”. Here, give a suitable name for the list of cells.
- Type “Months” in the Name and click on OK.
- Similarly, select the holiday cells and go to the Define Name option.
- Then, type “Holidays” as the name and click on OK.
- Lastly, select the Type cells and go to the Define Name option.
- Then, type “Types” as the name and click on OK.
Step 2: Create Primary Outline
To create a QR code attendance tracker, we have to follow some specified rules. Ar first, we want to make a dataset. To do this we have to follow the following rules.
- Firstly, write ‘QR Code Attendance Tracking’ in some merged cells at a larger font size, That will make the heading more attractive. Then, type your required Headline fields for your data. Click here to see a screenshot that illustrates what the fields look like.
- Now, after completing the heading part, you have to create an attendance tracker dataset.
- In the following image, we can see the basic outlines of the attendance and its related dataset.
- Here, we have ID, Name, QR Code, Present, Absent, Present Percentage and Absent Percentage columns in the following dataset.
- In the ID column, we enter each student’s ID number.
- In the following dataset, we are going to calculate the attendance record for January month. But this template shows the first weeks of January month attendance record. For clear demonstration purposes, we divide the whole January month into four segments that mean 4 weeks.
- Here, we show all the calculations for the first week. That’s why we enter our starting at first January. But the End date is 7th January.
Step 3: Insert QR Code in Worksheet
Now, we are going to create a QR code in Excel for making an attendance tracker. To do this, you have to follow the following process.
- First of all, You have to go to the Insert tab and select Get Add-ins.
- Therefore, the Office Add-ins window will appear. Next, you have to select QR4Office and click on Add.
- As a consequence, a new window will appear and you have to click on Continue.
- As a consequence, you will get the following QR code window. Next, by customizing the QR code, click on Insert.
- Next, you have to insert each student’s QR Code. As a result, you will get the following QR Code column. Based on this QR code, you will be able to take the attendance of each student.
Step 4: Input Date for First Week
Now, you will have to make columns for all dates of the first week of January. To do this, you have to follow the following rules. Here, we will use the IF and TEXT functions.
- First, enter the first date of the month. For this use this formula to link the cell with the Start Date cell.
=F4
- Press Enter.
- Then, you will make a column for the remaining dates. In the second cell type this formula to get the next date.
=IF(F6
- Press Enter.
- Next, drag the Fill Handle icon to fill other cells with the formula. Therefore, you will get the following dates as shown below.
- After that, get the name of the weekdays for the dates. For that, paste this formula into cell F7.
=TEXT(F6,“ddd”)
The TEXT function will convert the date of the value F6 cell to text. The “ddd” denotes the format of the text which will give the name of the weekday in 3 strings.
- Press Enter.
- Next, drag the Fill Handle icon to fill other cells with the formula. Therefore, you will get the following dates as shown below.
How Does the Formula Work?
- Here, F6denotes a condition that cell F6 (Previous Date before this cell) is less than J4 (End Date). You must use absolute reference because the cell of End Date will be the same for the next cells also.
- F6+1 is a command when the “If” condition is true. It asks to add 1 with the previous cell.
- “ ” It denotes that when the “If” condition is False, keep the cell blank.
Step 5: Create Data Validation in Worsksheet
Now, we are going to set up a drop-down menu for the attendance cells. So, when you want to input the attendance data, you can’t insert any other values except the Type list.
- For this, select all the attendance cells.
- And, go to the Data tab and select Data Validation.
- Then in the Data Validation window, keep it in the Settings tab.
- Now, select List from the Allow option.
- And, write =Types in the Source box.
- Finally, click on OK.
- Now, go to any cells to insert attendance. You will find drop-down options to open.
- You can select any to insert. And without these options, you can’t insert any other values.
Step 6: Enter Formula to Identify Holidays
Next, we are going to identify the holidays. It is necessary to highlight the holiday columns so you can easily identify them. You can manually format them with colors. Alternatively, you can automate them by using the Holiday list and Conditional Formatting. Here, we will use OR and COUNTIF functions.
- At first, select all the cells of the attendance column.
- And go to the Home tab, select Conditional Formatting and finally select the New Rule option.
- Now, a window will appear named “New Formatting Rule” and select the option “Use a formula to determine which cells to format” in the Rule Type.
- Paste this formula into the Rule Description box:
=OR(F$7=“Sat”)
- Now, go to the Format option. Select your desired color to fill the column.
- Next, you have to repeat the above process again and paste this formula into the Rule Description box:
=OR(F$7=“Sun”)
- Now, go to the Format option. Select your desired color to fill the column.
- Next, you have to repeat the above process for th third time and Paste this formula into the Rule Description box:
=COUNTIF(Holidays,F$6)
- Now, go to the Format option. Select your desired color to fill the column.
- By following the above three procedures, finally, you will be able to make the holidays green from the following list.
Step 7: Estimate Absent and Present Percentage
Now, we are going to estimate the present and absent percentages. To do this we have to follow the following process. Here, we will use the COUNTIF function.
- First of all, we have to insert data in the attendance cells to calculate the summary columns. To insert data, you can write from the keyboard or use the drop-down suggestions.
- Next, we going to calculate the total present for each student by typing the following formula in cell L8.
=COUNTIF(E8:J8,"P")
The COUNTIF function will count the total present days for each student.
- Then, press Enter.
- Therefore, you will get the present days for the first ID as shown below.
- Next, drag the Fill Handle icon to fill other cells with the formula. Therefore, you will get the present days for each ID as shown below.
- Next, we going to calculate the total absence for each student by typing the following formula in cell M8.
=COUNTIF(E8:K8,"A")
The COUNTIF function will count the total absent days for each student.
- Then, press Enter.
- Therefore, you will get the absent days for the first ID as shown below.
- Next, drag the Fill Handle icon to fill other cells with the formula. Therefore, you will get the absent days for each ID as shown below.
- Next, we going to calculate the total present percentage for each student by typing the following formula in cell N8 and clicking on percentage from the Home tab.
=L8/(L8+M8)
- Then, press Enter.
- Therefore, you will get the present percentage for the first ID as shown below.
- Next, drag the Fill Handle icon to fill other cells with the formula. Therefore, you will get the present percentage for each ID as shown below.
- Next, we going to calculate the total absent percentage for each student by typing the following formula in cell O8 and clicking on percentage from the Home tab.
=M8/(L8+M8)
- Then, press Enter.
- Therefore, you will get the absent percentage for the first ID as shown below.
- Next, drag the Fill Handle icon to fill other cells with the formula. Therefore, you will get the absent percentage for each ID as shown below.
Step 8: Track Attendance with QR Code for Other Weeks
By the above steps, we can easily calculate the other’s week attendance tracking. To do this you have to follow the following process.
- First of all, you have to copy the first week’s total worksheet.
- Next, you need to change the only Start Date and End Date values.
- As a result, automatically, the whole worksheet will be changed.
- Next, you just need to enter attendance data in the attendance columns.
- Therefore, you will be able to get an attendance tracker for the 2nd week as shown below.
