How to Create Biometric Attendance Report in Excel

When you have an organization with very few employees or participants, you can create a biometric attendance system for them. It helps to get their daily attendance report with just a click. Microsoft Excel has a tremendous method to conduct this. In this article, we will guide you on how to create a biometric attendance report in Excel with easy steps. Without further delay, let’s begin the process.

How to Create Biometric Attendance Report in Excel: with Easy Steps

Now, we will see the step-by-step process for creating a biometric attendance report.

Step 1: Change Settings of Workbook

To begin the process, we need to modify some settings of our workbook.

First, open your workbook and go to the File tab.

Then, click on the Save As option.
Here, select Excel Macro-Enabled Workbook as the file type.

Following, press Save.
Next, go to File > Options.

Here, select the Formulas option in the Excel Options window.
Following, mark checked the Enable iterative calculation box.

Step 2: Prepare Worksheets for Data Entry

Now, we will start working on the worksheet.

First, create a worksheet to insert individual Biometric Attendance like this.

Afterward, create another worksheet to make the overall Biometric Attendance Report.

Step 3: Insert Formula for Date and Time Entry

At this stage, we will generate a formula for the first worksheet.

First, select cell C5 and insert this formula.

=IF(B5<>””,IF(C5<>””,C5,NOW()),””)

Then, press Enter.
Following, use the Autofill tool to imply the formula in cell D5.

Lastly, type any Employee Name in cell B5.
That’s it, you will see that the Date and Time values are automatically generated in the adjacent cells.

Here, we applied the IF function to determine a condition for cell B5. According to the condition, we will have the present date and time in cells C5 and D5 with the help of the NOW function.

Step 4: Format Cells of Date and Time Entry

So far, we inserted our information in the biometric attendance sheet. But you can see that the Date and Time are not showing in the correct format. To solve this, follow the process below.

First, right-click on cell C5 and select Format Cells from its Context Menu.

Following, you will see the Format Cells dialogue box.
Here, select Date from the Category list.
Then, choose any Type for date format as shown below.

Along with it, set the Time format following a similar procedure.

That’s it, you will see the information in the individual Biometric Attendance sheet is in the correct format.

Step 5: Record Macro and Transfer Data to Attendance Report Sheet

Now comes the most important step of our work. Here, we will set the system as a biometric format to work automatically.

In the beginning, select the first worksheet for individual Biometric Attendance.
Then, go to the View tab and click on the Macros icon.

Following, select Record Macro from the drop-down menu.

Afterward, you will see the Record Macro window.
Here, insert the Macro name according to your preference.

Then, press OK.
Now, select cell range B5:D5 and press Ctrl + C to copy it.

After that, go to the second sheet and right-click on cell B5.
Then, select the Paste Special from the Context Menu.

Next, select Values in the Paste Special window and press OK.

Then, change the Date and Time format as we guided in step 4 to see the output below.

Lastly, go to the Home tab and select Insert from the Cells group.

That’s, you will see a new row before the previous one.

Finally, go to View > Macros again and press on Stop Recording.

Step 6: Create Insert Button in Biometric Attendance Sheet

So far, we have generated an automated system for data input. We are almost at the end of our process. At this stage, let us create the biometric button for inserting data.

First, go to the Insert tab and click on Shapes from the Illustrations group.
Then, select any type of shape according to your preference.

Following, you will see the selected shape is shown on the Biometric Attendance worksheet.

Now, type any name that you want to place for the button.
For example, we typed INSERT as the button name.

Now, right-click on it and select Assign Macro.

Then, select the Macro name that we set earlier in step 5.
Lastly, press OK to close the Assign Macro window.

Step 7: Insert Continuous Data

Finally, our workbook is ready for data input. Let’s see how it works.

First, insert the Employee Name in cell B5.
Therefore, it will automatically generate the Date and Time.

Now, press the INSERT button.

That’s it, you will see the data is moved to the Biometric Attendance Report sheet.

In this way, every employee can input their in and out time in Excel.

Download Practice Workbook

Download this practice file and try it by yourself.

Biometric Attendance Report.xlsm

Conclusion

Finally, we have come to the end of our article on how to create a biometric attendance report in Excel with easy steps. Let us know your valuable suggestions.

<< Go Back to Employee Attendance Sheet Excel | Excel HR Templates | Excel Templates

Get FREE Advanced Excel Exercises with Solutions!