QR Code Attendance Tracking with Excel (with Easy Steps)

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 for Tracking Attendance with QR Code Attendance in 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.

Make List of Holidays and Leaves Types

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.

QR Code Attendance Tracking with Excel

  • 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.

QR Code Attendance Tracking with Excel

  • 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.

Create Primary Outline

Read More: Attendance and Overtime Calculation Sheet in Excel


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.

Create QR Code in Excel

  • Therefore, the Office Add-ins window will appear. Next, you have to select QR4Office and click on Add.

QR Code Attendance Tracking with Excel

  • 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.

QR Code Attendance Tracking with Excel

  • 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.

QR Code Attendance Tracking with Excel

Read More: How to Create Student Attendance Sheet in Excel with Formula


Step 4: Input Dates 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.

Input Date

  • Then, you will make a column for the remaining dates. In the second cell type this formula to get the next date.

=IF(F6<$J$4,F6+1,"")

  • Press Enter.

QR Code Attendance Tracking with Excel

  • 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.

QR Code Attendance Tracking with Excel

🔎 How Does the Formula Work?

  • Here, F6<$J$4 denotes 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 Worksheet

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.

Create 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.

QR Code Attendance Tracking with Excel

  • 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 Formulas 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.

Enter Formula to Identify Holidays

  • 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.

QR Code Attendance Tracking with Excel

  • 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.

QR Code Attendance Tracking with Excel

  • By following the above three procedures, finally, you will be able to make the holidays green from the following list.

QR Code Attendance Tracking with Excel

Read More: How to Make Time Attendance Sheet in Excel (2 Easy Ways)


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.

Estimate Absent and Present Percentage

  • 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.

QR Code Attendance Tracking with Excel

  • 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.

QR Code Attendance Tracking with Excel

  • 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.

QR Code Attendance Tracking with Excel

  • 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.

QR Code Attendance Tracking with Excel


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.

Track Attendance with OR Code for Other Weeks

  • You have to follow the above process to get an attendance tracker for the 3rd week as shown below.

QR Code Attendance Tracking with Excel

  • You have to follow the above process to get an attendance tracker for the 4th week as shown below.

QR Code Attendance Tracking with Excel


Step 9: Generate Summary for Entire Month

Now, we are going to create a summary of the attendance tracker for a whole month. To do this you have to follow the following process.

  • First of all, you have to copy all four weeks’ absent and present values in a new worksheet.
  • Next, you have to type the following formula to calculate the total present value.

=D8+F8+H8+J8

  • Then, press Enter.

Generate Summary for Entire Month

  • Next, drag the Fill Handle icon to fill other cells with the formula. Therefore, you will get the total present days for each ID as shown below.
  • Next, you have to type the following formula to calculate the total absent value.

=E8+G8+I8+K8

  • Then, press Enter.

QR Code Attendance Tracking with Excel

  • Next, drag the Fill Handle icon to fill other cells with the formula. Therefore, you will get the total absent days for each ID as shown below.

  • To create a Clustered Column chart, select the range of data and go to the Insert tab. Next, select the Clustered Column chart.

QR Code Attendance Tracking with Excel

  • As a consequence, you will get the following chart.

  • Finally, you will be able to get the following summary of the QR code attendance tracking.

QR Code Attendance Tracking with Excel


Conclusion

That’s the end of today’s session. I strongly believe that from now you may be able to create QR Code attendance training in Excel. If you have any queries or recommendations, please share them in the comments section below.

Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions. Keep learning new methods and keep growing!


Related Articles

Saquib

Saquib

Welcome to my Profile. I am working on and researching Microsoft Excel right now, and I will be posting articles about it here. I received a B.Sc. in Naval Architecture and Marine Engineering from the Bangladesh University of Engineering and Technology (BUET). Having studied naval architecture, I have a strong interest in research and development. Always try to learn from different sources and come up with creative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo