How to Create a Free Google Sheets Attendance Template – Easy to Use in 2024

Are you tired of manually tracking attendance in a cumbersome register? Do you want a more efficient way to manage attendance for your classes, meetings, or events? Look no further! Google Sheets is here to save the day with its easy-to-use and customizable attendance templates.

Using attendance sheets is common in various industries and organizations to record attendance and ensure effective participation management. In schools and universities, teachers use these sheets to ensure students regularly attend classes and identify any issues related to their attendance. Additionally, businesses use attendance sheets to track employee presence and consolidate information for activity reports and payroll purposes.

Gone are the days of manually entering attendance in a large register and tallying it at the end of the month. With modern spreadsheet software, attendance tracking is quick and hassle-free. Plus, it automates the process! Once you have a ready-made attendance template with all the necessary formulas, you can reuse it every month without having to recalculate everything.

In this tutorial, we’ll show you how to create a simple Google Sheets attendance template using basic Google Sheets formulas.

What is an Attendance Sheet Template?

An attendance sheet template or a attendance tracking tool in Google Sheets is a grid where details of a group of individuals’ attendance are recorded. This group could be students in a class, employees in a department, or participants in an event. Additional details such as the class/group/service name, date, and time may also be recorded in the attendance sheet.

How to Create a Google Sheets Attendance Template?

To create an attendance sheet, we start by setting up a basic structure or preliminary plan for the sheet. This involves creating spaces for the days of the month, student names, and other basic details such as class/department/group names, names, dates, etc. We also set up basic conditional formatting to highlight weekends with a different background color or font. This helps avoid accidental attendance recordation during weekends.

Once the basic structure is ready, we can start filling in basic details like names, dates, etc. We also set up formulas to count the number of present, absent, and other types of marks. At this stage, we can also add a function to calculate the attendance percentage for each individual.

Once the sheet is ready, we can start filling in the attendance grid with the appropriate code for each student and each day of the month.

In this tutorial, we’ll show you step-by-step how to create attendance sheets. This attendance sheet will automatically count the number of present and absent days throughout the duration.

Download the Free Google Sheets Attendance Template

Using the Free Google Sheets Attendance Template

This template is designed for a standard quarter duration of 10 or 11 weeks. You can also adapt it for year-long use if desired, or remove unused weeks if you only need the template for a shorter period. We’ll show you how to do that later in this article.

Our template features a weekly attendance table on each sheet that automatically updates the date range based on the data entered in the first sheet. We’ve highlighted the start date in red to make it easier for you to enter the current date. The weekdays and date will automatically update in each subsequent sheet.

Modifying the Codes

There are 5 default input codes you can use in your attendance sheet. By changing the text in the box at the top of the sheet, you can modify what appears in the dropdown menus of the main part of the template. Again, you only need to make the modifications in the first sheet, and they will apply to all subsequent weeks of the template.

Naming Cells and Ranges

As the default settings of this template are for a classroom, you’ll find cells where you can enter the teacher’s name, class name, and student names. The table will also automatically fill in the names for all subsequent weeks. If you’re working in a different industry, you can simply modify the text in Week 1, and all subsequent weeks will reflect the changes you made.

Using Dropdown Menus

The template features a dropdown menu for each student, for each day of the week. Just click on the small arrow in the cell and make a selection. You’ll notice that one of the options is a single quotation mark (”). Select this option if you want to leave the cell empty.

Each of the dropdown choices will apply conditional formatting to the cell:

• Green – Present
• Yellow – Partial Absence (late arrival, early departure)
• Red – Absent (unjustified absence, justified absence)

This allows you to easily see who had a poor or excellent attendance throughout the week.

Total Number of Absence Days

The last column of the template calculates the number of absence days for an individual during the week. This includes partial absences, so be mindful of that when making judgments. The last sheet of the template also tracks the total absences throughout the quarter by summing up the values from each week.

How Does the Attendance Template Work?

If you want to make significant changes or customize the template, it’s important to understand the concepts we used to create the spreadsheet. So, let’s take a look at the functions and formulas used in this spreadsheet.

Automatically Updating Dates

As you’ve probably figured out by now, the other dates on each sheet automatically update based on the date entered in cell B1. For this, we simply use cell references and simple additions.

The first cell that requires a date input for attendance marking purposes is cell B4, and since it’s the same date as the start date, we simply display the same date as cell B1. So, we use the formula =B1.

For the following days, we just need to add one day to the previous day. For example, the cell for Tuesday in the screenshot below should simply contain =B4+1, Wednesday should contain =C4+1, and so on.

However, to ensure that the cells display a date after using a formula, you need to format the cells for dates. To do this:

1. Select the cells
2. Go to Format > Number > Date

Displaying the Correct Weekday

Under each date is the corresponding weekday. This remains constant regardless of the date. For example, if you enter a Wednesday in cell B2 “Week Start,” the first day will also be a Wednesday.

For this, we again use cell references and formatting. Each day pulls data from the date directly above it. Cell B5 has the formula =B4, cell C5 has the formula =C4, and so on.

Initially, this will simply display the date again when you do it. To change it to a weekday:

1. Go to Format > Number > More Formats > More date and time formats
2. Remove anything currently in the dropdown list
3. There should be a weekday among the options below. Click on it.
4. Click Apply

Creating Dropdown Menus

You can create dropdown menus using a Google Sheets feature known as data validation. This feature allows you to allow only certain entries in cells, hence the usefulness of a dropdown menu as it limits the possible choices for the user.

The first step in using data validation is to set up appropriate entries. In our case, we have:

• Present
• Justified Absence
• Unjustified Absence
• Late Arrival
• Early Departure
• (Hidden)

These options occupy cells E2 to J2.

To use these options as references for the dropdown menus, simply:

1. Select the relevant cells
2. Go to Data > Data validation
3. Select List from a range in the Criteria option
4. Set the range you want to use as options in the dropdown menu. In our case, it’s =$D$2:$J$2
5. Check the Show dropdown list in cell box
6. Click Save

Adding Conditional Formatting

Each choice in the dropdown menu will highlight the cell with a certain color. This is fairly simple to do. Just follow these steps:

1. Select the cells you want to apply conditional formatting to
2. Go to Format > Conditional formatting
3. Select the formatting rule and set it (e.g. Text is exactly “Present”)
4. Choose the formatting style (we chose green fill in the example)
5. Click + Add another rule and repeat for all options
6. Click Done

Calculating the Total Number of Absence Days

To calculate the number of absence days for an individual during a week, we used the following formula:

=ArrayFormula(SUM(IF(E6:J6,{$F$2, $G$2, $H$2, $I$2})))

Here, you’ll notice that we used absolute references again. These references refer to:

• Justified Absence
• Unjustified Absence
• Late Arrival
• Early Departure

Once again, you need to use absolute values to ensure the formula doesn’t break when applied to other rows.

The COUNTIF formula will then search for the defined values in the defined range B6:H6 and count them whenever they appear.

Unfortunately, the COUNTIF function can only search for and count a single value when used independently. That’s why the above formula uses the SUM function to add them up and ARRAYFORMULA to enable searching for multiple values.

Referencing Other Sheets

As this spreadsheet template is spread across multiple sheets, there are many cases where each sheet needs to pull data from another sheet. One example is the student names. Without automatically moving data to the next sheet, you’d have to manually enter the names every week.

Here’s an example:

='Week 1'!B6

You can see that the cell retrieves the name “Student 1” from cell A6 in the sheet “Week 1”.

When using this type of cell reference, make sure to place the sheet name in single quotation marks and have an exclamation mark before the cell reference. Otherwise, Google Sheets will likely pull data from the same sheet, resulting in an error.

You can also create formulas by pulling data from other sheets. One simple example is getting the dates in each sheet. From Week 2 onwards, the start date used the formula:

='Week X'!B1 + 7

X is the previous week.

That’s how the spreadsheet automatically updates every week to display the correct dates based on a single input.

Modifications You Can Make to the Template

After looking at the formulas and functions we used to create the sheet, we hope you feel comfortable modifying the template to suit your needs.

Standard modifications you should make include:

• Setting the start date
• Choosing absence codes
• Filling in student names

Here are other modifications you may not have thought of:

• Modifying the conditional formatting of the dropdown menus. For example, you can choose different colors for justified and unjustified absences.
• Modifying the formula for the total number of absence days to exclude partial absences (simply remove cell references $H$2 and $I$2)
• Adding more weeks to the template. To do this:
  1. Right-click on “Week 11” at the bottom of the sheet
  2. Click “Duplicate”
  3. Rename the new duplicate to “Week 12”
  4. Modify the formula in cell B1 to =’Week 11′!B1 + 7 (or manually enter the date)

Get Started with the Template

The sheet has no restrictions, so feel free to make a copy of our free Google Sheets attendance template and experiment with it. If you have any further questions about its usage or the modifications you can make, simply ask in the comments!

Article published on bolamarketing.com. For more information on productivity tools and tutorials, visit Crawlan.com.