
Stop Bad Data at the Door: Drop-Down Lists for Attendance and Program Logs
When five site coordinators type attendance codes by hand, you get five versions of the truth. One person types "P," another types "Present," a third types "p " with a trailing space. When reporting season comes, someone has to clean all of it before a single number can go to a funder.
The fix takes about five minutes: a drop-down list. Staff pick a value instead of typing it, so every entry matches.
Step 1: Build a Lists tab
Create a tab named Lists. In column A, type your approved values, one per cell. For attendance that might be:
P (present)
A (absent)
E (excused)
T (tardy)
Use other columns for other lists, such as activity names, site names, or session types. Keeping every list in one place means you update it once and every tracker follows.
Step 2: Add the drop-down in Excel
Select the cells where staff will enter attendance.
Go to Data, then Data Validation.
Under Allow, choose List.
In Source, select your values on the Lists tab, for example
=Lists!$A$2:$A$5.On the Error Alert tab, choose Stop so nothing outside the list can be entered.
Step 3: Add the drop-down in Google Sheets
Select the cells.
Go to Data, then Data validation, then Add rule.
Choose Dropdown (from a range) and point it to
Lists!A2:A5.Under Advanced options, choose Reject the input for invalid data.
Google Sheets also offers checkboxes, which are perfect for yes or no items like "permission slip on file" or "snack served."
Step 4: Make the list grow with you
If you add a new activity halfway through the year, you do not want to rebuild every rule. In Excel, turn your list into a Table (select it and press Ctrl+T) and point the validation to the table column. New items appear in the drop-down automatically. In Sheets, point the rule at a longer range such as Lists!A2:A100 and leave blank cells at the bottom.
Step 5: Protect the structure
Drop-downs only help if nobody deletes them. In Google Sheets, use Data, then Protect sheets and ranges to lock the Lists tab and the header row. In Excel, use Review, then Protect Sheet and leave only the entry cells unlocked. Staff can still enter data, but they cannot break the formulas or change the approved codes.
Step 6: Count with confidence
Because every entry now matches, summary formulas just work:
=COUNTIFS(C:C, "P", B:B, "Site 3")
That gives you present days for Site 3 without any cleanup. Feed those counts into a PivotTable or chart, and your monthly attendance summary takes minutes instead of an afternoon.
Why this matters for grant reporting
Funders such as 21st CCLC and BOOST expect attendance and dosage to be counted the same way at every site. A shared tracker with drop-downs is one of the simplest ways to get there. It also makes life easier for your evaluator, who can spend time on findings instead of fixing typos.
Get help setting it up
Steps To Achieve builds tracking templates for afterschool and OST programs and trains staff to keep them running. Our Excel and Google Sheets training is hands-on, built from your own files, and available virtually or in person.
See Excel and Google Sheets training or Book a free 20-minute call.

