How to Prepare a Meeting Attendance Sheet in Excel

If you are looking for how to create a meeting attendance sheet in Excel, then you are in the right place. In our daily corporate life or in the case of any organization, we need to count the attendance of the employees. In this article, we’ll try to discuss how to create a meeting attendance sheet in Excel.

How to Prepare a Meeting Attendance Sheet in Excel: with Easy Steps

In any organization, meetings can happen daily or at specific times with specific time intervals. Sometimes, in international organizations meetings happen daily even during the holidays. In this article, we’ll assume that an organization holds meetings every day for a month. So, we’ll see the simple steps by which we can prepare a meeting attendance.

1. Making an Information Worksheet

The first step is to create an information worksheet i.e. the information which is mandatory to create a meeting worksheet. In this article, we have created the following dataset of mandatory information named Information Needed for Meeting Attendance. It has column headers as Month and Type in Columns B and D respectively. So, our meeting attendance will have 12 months and 3 types of activities i.e. Present, Planned Leave, and Unplanned Absent.

2. Defining Name of Month List

Eventually, in this step, we’ll define the Month names and Type them in the list. We need to follow some simple steps to do this.

To define month names in the list,

• Firstly, go to Formulas > select the month names in the Month Column > click Define Names.
• Eventually, a New Name window will appear.
• Secondly, give a name in the Name In this case, it is Month.
• Thirdly, click OK.

• So, the month names are listed as Month.
• Now, to list Type in the list, fourthly, again go to Formulas > select the type names in the Type Column > click Define Names.
• Fifthly, in the New Name window, give the name as Type in the Name box and click OK.

So, the Type names are also added in the list.

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

3. Creating Template Structure

In this step, we have created a template structure which is mainly the skeleton of the meeting attendance sheet. The template is like this.

We have added the Start Date and End Date of a month, Present Percentage, and also Absent Percentage in the template.

4. Inserting Formula for Month, Start Date and End Date

Importantly, we have to insert formulas for Month, Start Date, and End Date. We need to follow the steps below.

• Firstly, select the N2 cell which is actually the cell for Name of the Month.
• Secondly, go to Data > select Data Tools > click Data Validation.

• Eventually, a Data Validation window will appear.
• Thirdly, go to Settings > select List in the Allow box > write =Month in the Source box > click OK.

• Consequently, we’ll see that a drop-down bar of months is added in the N2

• Now, we have to fix the Start Date and End Date in E4 and M4 cells respectively.
• Eventually, to fix the Start Date, write the following formula in the E4

Here, N2 refers to the month name in the N2 cell. As we are putting Start Date, we have selected the month name as January.

• Secondly, press ENTER to get the output as 1/1/2022.
• Thirdly, to fix the End Date, write the following formula in the M4 cell like this.

Here, E4 refers to the Start Date in the E4 cell which is 1/1/2022.

• Finally, press ENTER to get the End Date as 1/31/2022.

Read More: How to Create a Monthly Staff Attendance Sheet in Excel

5. Entering the Dates

In this step, we’ll enter the dates in Row 7.

• Firstly, write the following formula in the D7 cell like this.

Here, E4 is the Start Date i.e. 1/1/2022.

• After pressing ENTER, we’ll get the output as 1/1/2022.

But we need to change this date format to dd type.

• To change this format type, right-click on the D7
• Secondly, click Format Cells.

• Eventually, a Format Cells window will appear.
• Thirdly, go to Number > select Custom > write dd in the Type box > click OK.

• So, the output will be 01.

• To fix the 2nd date, write the formula in the E7 cell like this.

Here, M4 is the End Date.

• After pressing ENTER we’ll find the output as 02.
  Note: If the output is again short date format we need to change it to dd by going to Format Cells again.

• Eventually, use the Fill Handle by dragging the cursor rightwards while holding the cursor at the right-bottom corner of the E7

• Consequently, we’ll get the dates upto 08 like this.

Now, we’ll add week names in Row 8.

• Firstly, write the following formula in the D8 cell like this.

Here, D7 is the first date of the month which is 01.

• Secondly, press ENTER and we’ll get the first day of the week which is Saturday i.e. Sat.

• In order to get the other days of the week use the Fill Handle rightwards and the output will be like this.

6. Setting Up Drop-Down Menu for Attendance Cells

In this step, we’ll set up a drop-down menu for the attendance cells which are D9:K28.

• Firstly, click the cells of D9:K28.
• Secondly, go to Data > select Data Tools > click Data Validation.

• Eventually, the Data Validation window will appear.
• Thirdly, click Settings > pick List in the Allow box > write =Type in the Source box > click OK.

• Eventually, the drop-down bars will appear in every cell of D9:K28.

7. Inserting Data in Attendance Cells

In this step, we have filled the attendance sheet with Type like this. We have done this up to Column 12.

8. Inserting Formulas to Calculate Total Attendance

Now, we’ll find out the number of Present , Planned Leave and Absent days for each of the employees individually. We need to use the COUNTIF function to find out these values.

• To calculate Present days for Jane, write the formula in the L9 cell like this.

Here, D9:K9 refers to attendance Type of Jane.

• Secondly, press ENTER and use the Fill Handle to get the Present days of Jane and as well as the other employees upto Row 23.

• Now, to calculate Planned Leave (PL), thirdly, write the formula in the M9 cell like this.

• Similarly, press ENTER and use the Fill Handle to get the Planned Leave days of Jane and other employees.

Additionally, we’ll find now the Unplanned Absent (A) days of the employees.

• Eventually, write the formula in the N9 cell like this.

• Again, press ENTER, and use the Fill Handle, and get the outputs like this.

We’ll now calculate the Present Percentage of the Employees.

• So, write the following formula in the O9

Here, L9 and 09 refer to the Present days of Jane and Workdays respectively.

• By pressing ENTER and then using the Fill Handle we’ll find the Present Percentage of the employees.

Finally, we’ll calculate the Absent Percentage of the employees.

• Eventually, write the following formula in the Q9 cell like this.

Here, M9, N9, and O9 refer to the Planned Leave, Unplanned Absent, and Workdays of Jane respectively.

• After clicking ENTER and then using the Fill Handle we’ll get the Absent Percentage of the employees like this.

Finally, our meeting attendance sheet is ready to use.

Read More: How to Create Employee Attendance Sheet with Time in Excel

Download Practice Workbook

Conclusion

That’s all about today’s session. These are the ways to create a meeting attendance sheet in Excel. We strongly believe this article would be highly beneficial for you. Don’t forget to share your thoughts and queries in the comments section.