How to Use Excel for Daycare Attendanceby Lynn Burbeck
If you have a daycare or other childcare facility, it's important to track the attendance of every child who is currently enrolled. The tools in Microsoft Excel make it relatively simple for teachers and school administrators to track children's attendance -- whether you want to track it by the week, month or year. Depending on how you want to view the spreadsheet data, you can create graphs and charts to represent the attendance or absences of each child in your daycare facility.
Creating the Template
Open a new Excel workbook with 13 worksheets. You can add worksheets by clicking on the tabs at the bottom of the page. Each individual worksheet should represent a month, with the final worksheet used for year-to-date tabulations.
Merge the cells from C1 to AI1. Type the name of the month, followed by the year into the merged cell. Center the text, increase the font size and bold the lettering to make the heading visible. Add color or other design elements to suit your preferences.
Type the heading "Last Name" in the A2 field and "First Name" in the B2 field. Make the text stand out by making the font bold and increasing the text size.
Add a final column at the end in cell AJ1 to automatically tabulate the number of absences for the month. This column will use conditional formatting to determine how many days each child has been absent.
Insert the dates of the month in the columns between C2 and AI2. Apply shading to holidays and weekends when you won't be taking attendance records.
Type each child's first and last name into the rows beneath the corresponding column heads. Sort the information by last name, class, or a combination of the two.
Copy and paste this template into all 12 worksheets. You'll still need to customize the information on each worksheet however, by changing the heading to the correct month and updating the dates to represent the correct number of days in the month.
Create a code for filling out the attendance record. Perhaps, "P" for present and "A" for absent.
Insert a formula into the month-to-date column to automatically tabulate how many absences each child has per month. If you are using "A" for absent, the formula for the first child is: COUNTIF ($C3:$CAI3, "A"). The mixed-cell formula can then be copied and pasted to each row in the column. The formula above assumes that C3 is the first entry of the month for the first child listed on the spreadsheet. Modify the formula as necessary so the selected fields includes the entire date range for the month.
Copy the formulas into each of the 12 worksheets. Double check the formulas to ensure they are correct.
Copy and paste the first and last names of the children into the last worksheet if you want to tabulate the number of absences over the course of the year. Make sure you include appropriate headings, as necessary.
Tabulating the Year to Date Sum
Create a formula to tabulate the number of absences for each child at the daycare facility. To create the formula, enter "=sum(" into the year-to-date field for the first child on the list.
Click on the first worksheet and click on the monthly total for the first child on the list.
Hold down the SHIFT key on your keyboard and click on the final month's worksheet. Click on the monthly total for the first child on the list and press ENTER. The formula will look something like, "=sum('January:December'!AI3)."
Copy and past the formula into the year-to-date column for each child on the list.
- check In order to copy and paste the formulas correctly, all of the names must be in the exact same order. If you need to add names throughout the year to account for incoming students, you'll need to adjust the formulas, as necessary.
- photo_camera Jupiterimages/Goodshoot/Getty Images