How to Create Attendance Register in Microsoft Excel | Attendance Sheet in Excel, by StudySpan on YouTube. 5 min 42 s.
- Video length
- 5 min 42 s
- Time to do it
- 20 minutes
- Difficulty
- Easy
- Steps
- 10
What you need
Steps
Open a blank Excel workbook and type Employee Name in cell A1. In cell B1 enter the first date of the month you are tracking.
Select B1 and drag the fill handle to the right until every date in the month is filled in. Type the weekday names in row 2 under each date so the sheet is easy to read.
Widen column A and list each employee name down the rows, one name per row, starting in A3.
Put a small key somewhere clear, such as P for present, A for absent, L for leave and H for holiday. Use the same letters everywhere so your formulas can count them.
Mark each person's day with one code in the matching cell. Leave a cell blank only when the day has not been recorded yet.
Add a total column at the end of each row. Type =COUNTIF(B3:AF3,"P") to count present days, then repeat with "A" and "L" for absent and leave.
Add a summary row at the bottom of the grid to count each day, for example =COUNTIF(B3:B20,"P") in the cell under the first date.
Select the grid and open Home, then Conditional Formatting and Highlight Cells Rules. Colour P green and A red so absences stand out at a glance.
Click a cell below the header rows and choose View, then Freeze Panes. The names and dates stay visible while you scroll through the month.
Before printing, go to Page Layout and set Orientation to Landscape. Use the Scale to Fit options to keep the sheet one page wide.
Tips and common mistakes
Add data validation to the code cells so only P, A, L and H can be typed, which stops typos breaking your totals.
Keep one sheet per month and rename the tab, then copy the layout for the next month instead of rebuilding it.
Use COUNTIF for every total so the numbers update on their own when you change a code.
If a new person joins later, insert a row inside the list rather than adding it below the total row.
Questions
Which formula counts present days in Excel?
Use COUNTIF on the row range with the code you use for present, such as =COUNTIF(B3:AF3,"P"). It updates as soon as you change a cell.
Can I record half days?
Yes. Add a separate code such as HD, count it with its own COUNTIF, and add half of that number to the total.
How do I keep the header row visible while scrolling?
Select the cell just below and to the right of the rows and columns you want locked, then choose View and Freeze Panes.
This guide was drafted from the video's details and general task knowledge. It has not been checked against the full video. Check the video and relevant product instructions before following a procedure. Report a correction.
