How to Use QR Code Attendance Tracking with Excel (with Easy Steps)

Step 1: Making a List of Holidays and Leave Days

To create an attendance tracker, we have to follow some specified rules. We have to create a worksheet named “Information”. In this worksheet, add the lists of Months, Holidays, and the Types 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 must 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.

Define a name for the list of the months:
- Select the cells for months.
- Go to the Formula tab and select the Define Name option.

You will see a window named “New Name”
Type “Months” in the Name and click on OK.

Select the holiday cells and go to the Define Name option.
Type “Holidays” as the name and click on OK.

Select the Type cells and go to the Define Name option.
Type “Types” as the name and click on OK.

Step 2: Creating a Primary Outline

Enter ‘QR Code Attendance Tracking’ in some merged cells at a larger font size.
Type the Headline fields for your data.

After completing the heading part, you must create an attendance tracker dataset. The basic outlines of the attendance and its related dataset are shown in the image below.

The following dataset has columns for ID, Name, QR Code, Present, Absent, Present Percentage, and Absent Percentage.

In the ID column, enter each student’s ID number.

In the following dataset, we are going to calculate the attendance record for January. Here, we show all the calculations for the first week.

Read More: Batch QR Code Generator from Excel

Step 3: Inserting a QR Code in the Worksheet

Go to the Insert tab and select Get Add-ins.

The Office Add-ins window will appear.
Select QR4Office and click on Add.

A new window will appear. Click on Continue.

You will get the following QR code window.
Click on Insert.

Insert each student’s QR Code.

You will get the following QR Code column. Based on this QR code, you will be able to take each student’s attendance.

Step 4: Inputting Dates for the First Week

Enter the first date of the month. Use this formula to link the cell with the Start Date cell:

=F4

Press Enter.

Make a column for the remaining dates.
In the second cell, enter this formula to get the next date:

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

Press Enter.

Drag the Fill Handle icon to fill other cells with the formula. You will get the following dates as shown below.
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.

Drag the Fill Handle icon to fill other cells with the formula. You will get the following dates, as shown below.

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 the End Date will be the same for the next cells.
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: Creating a Data Validation in the Worksheet

Select all the attendance cells.
Go to the Data tab and select Data Validation.

In the Data Validation window, keep it in the Settings tab.
Select List from the Allow option.
Type =Types in the Source box.
Click on OK.

Go to any cells to insert attendance. You will find drop-down options to open.
Select any to insert. Without these options, you can’t insert any other values.

Step 6: Entering Formulas to Identify Holidays

Select all the cells of the attendance column.
Go to the Home tab, select Conditional Formatting, and select the New Rule option.

A window will appear named “New Formatting Rule”, select the option “Use a formula to determine which cells to format” in the Rule Type.
Enter this formula into the Rule Description box:

=OR(F$7=“Sat”)

Go to the Format option. Select your desired color to fill the column.

You have to repeat the above process again and paste this formula into the Rule Description box:

=OR(F$7=“Sun”)

Go to the Format option. Select your desired color to fill the column.

Repeat the above process for the third time and Paste this formula into the Rule Description box:

=COUNTIF(Holidays,F$6)

Go to the Format option. Select your desired color to fill the column.

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

Step 7: Estimating Absent and Present Percentages

Insert data in the attendance cells to calculate the summary columns.
Calculate the total present for each student by entering the following formula in cell L8:

=COUNTIF(E8:J8,"P")

The COUNTIF function will count the total present days for each student.

Press Enter.

You will get the present days for the first ID, as shown below.

Drag the Fill Handle icon to fill other cells with the formula. You will get the present days for each ID, as shown below.

To calculate each student’s total absence, enter the following formula in cell M8:

=COUNTIF(E8:K8,"A")

The COUNTIF function will count the total absent days for each student.

Press Enter.

You will get the absent days for the first ID, as shown below.

Drag the Fill Handle icon to fill other cells with the formula. You will get the absent days for each ID, as shown below.

To calculate the total present percentage for each student, enter the following formula in cell N8: Clicking on the percentage from the Home tab.

=L8/(L8+M8)

Press Enter.
You will get the present percentage for the first ID, as shown below.

Drag the Fill Handle icon to fill other cells with the formula. You will get the present percentage for each ID, as shown below.

To calculate the total absent percentage for each student, enter the following formula in cell O8 and click on the percentage from the Home tab.

=M8/(L8+M8)

Press Enter.
You will get the absent percentage for the first ID, as shown below.

Drag the Fill Handle icon to fill other cells with the formula. You will get the absent percentage for each ID, as shown below.

Step 8: Tracking Attendance with a QR Code for Other Weeks

Copy the first week’s total worksheet.
Change the Start Date and End Date values.
Automatically, the whole worksheet will be changed.
Enter attendance data in the attendance columns.
You can get an attendance tracker for the 2nd week, as shown below.

Follow the above process to get an attendance tracker for the 3rd week, as shown below.

Follow the above process to get an attendance tracker for the 4th week, as shown below.

Step 9: Generating a Summary for the Entire Month

Copy all four weeks’ absent and present values in a new worksheet.
Enter the following formula to calculate the total present value:

=D8+F8+H8+J8

Press Enter.

Drag the Fill Handle icon to fill other cells with the formula. You will get the total present days for each ID, as shown below.
Enter the following formula to calculate the total absent value:

=E8+G8+I8+K8

Press Enter.

Drag the Fill Handle icon to fill other cells with the formula. 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.
Select the Clustered Column chart.

You will get the following chart.

You will get the following summary of the QR code attendance tracking.

Download the Practice Workbook

Download this workbook to practice.

QR Code Attendance Tracking.xlsx

<< Go Back To QR Code in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

2 Comments

Suhaili Oct 5, 2023 at 7:51 AM

Hi can you sell the ready-to-be-used template. i am willing to buy if the price is affordable. Thank you
- Reply
  Shamima Sultana Oct 5, 2023 at 10:51 AM
  
  Hello Suhaili,
  
  You don’t need to worry about payment. If you want any read-to-use template kindly send your requirements using [email protected] email.
  
  Regards
  ExcelDemy