How to Prepare a Meeting Attendance Sheet in Excel

In this article, we’ll describe how to create a meeting attendance sheet in Excel. We’ll assume that an organization holds meetings every day for a month, and prepare a meeting attendance register to record who attended and when.

Step 1 – Making an Information Worksheet

First we need to prepare an information worksheet containing the information necessary to create our meeting worksheet. For purpose of demonstration, we’ve created the following dataset of necessary information named Information Needed for Meeting Attendance. Our meeting attendance will have 12 months and 3 types of activities, namely Present, Planned Leave, and Unplanned Absent.

Step 2 – Defining the Name of Month List

To define the month names in the list:

Go to Formulas > select the month names in the Month Column > click Define Names.

A New Name window will appear.

Enter Month for the Name.
Click OK.

The month names are listed as Month.

To list Type:

Go to the Formulas tab again > select the Type names in the Type Column > click Define Names.
In the New Name window, enter Type in the Name box and click OK.

The Type names are also added to a list. We’ll use these two lists to fill validated values in our meeting attendance sheet.

Step 3 – Creating the Template Structure

Now we create a template structure which serves as the skeleton of the meeting attendance sheet, and looks like this:.

We have added the Start Date and End Date of a month, along with columns to record the Present Percentage and Absent Percentage for each employee in the template.

Step 4 – Inserting Formulas for Month, Start Date and End Date

Next, we insert formulas to fill values for Month, Start Date, and End Date.

Select cell N2, the Name of the Month.
Go to the Data tab > select Data Tools > click Data Validation.

The Data Validation window will appear.

Go to Settings > select List in the Allow box > type =Month in the Source box > click OK.

A drop-down bar of months is added in cell N2.

Now, we’ll set the Start Date and End Date in cells E4 and M4 respectively.
To set the Start Date, enter the following formula in cell E4:

=DATEVALUE(“1”&N2)

Here, N2 refers to the month name in cell N2. As we are setting the Start Date, we select January as the month name.

Press ENTER to return output of 1/1/2022.
To set the End Date, enter the following formula in cell M4:

=EOMONTH(E4;0)

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

Press ENTER to return an End Date of 1/31/2022.

Step 5 – Entering the Dates

Now we’ll enter the dates in Row 7.

Enter the following formula in cell D7:

=E4

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

Press ENTER to return output of 1/1/2022.

We need to change this date format to dd type.

Right-click on cell D7.
Select Format Cells from the context menu.

The Format Cells window will appear.

Go to the Number tab > select Custom > enter dd in the Type box > click OK.

The output is 01.

To set the 2nd date, enter the formula below in cell E7:

=IF(D7<&M&4+1;D7+1;””)

Here, M4 is the End Date.

Press ENTER to return output of 02.

Note: If the output is again in short date format, change it to dd format by going to Format Cells again.

Use the Fill Handle to copy the formula across to cell K7.

We have dates up to 08.

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

Enter the following formula in cell D8:

=TEXT(D7;”ddd”)

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

Press ENTER to return the first day of the week, Saturday i.e. Sat.

To get the other days of the week, drag the Fill Handle right.

The output looks like this:

Step 6 – Setting Up a Drop-Down Menu for the Attendance Cells

Next, we’ll set up a drop-down menu for the attendance cells in D9:K28.

Select the range D9:K28.
Go to the Data tab > select Data Tools > click Data Validation.

The Data Validation window will appear.

Click Settings > pick List in the Allow box > write =Type in the Source box > click OK.

Drop-down bars will appear in every cell of the range D9:K28.

Step 7 – Inserting Data in the Attendance Cells

Using the drop-down lists in every cell, fill the attendance sheet with Type values up to Column 12 like in the image below.

Step 8 – Inserting Formulas to Calculate Total Attendance

Now that we have all the raw data, we can work out the number of Present, Planned Leave and Absent days for each of the employees individually. We’ll use the COUNTIF function to find these values.