Employee Attendance Register
Beginner
30 min
60 views
0 solutions
Overview
A small company tracks attendance for 10 employees across one month. Taught live by Puneet Arora, this classroom case introduces students to Google Sheets navigation, data entry, essential functions (SUM, COUNT, COUNTIF, IF), professional formatting, and basic charting — all in a real HR context.
Case Details
CONTEXT: HR department of a 10-employee company needs a structured attendance register for March. Manager wants to know present days, absent days, late arrivals, and bonus eligibility in a single sheet.
INSTRUCTOR DEMO — STEP 1: Create the sheet live. Columns: Emp ID (A), Name (B), Department (C), Days Present (D), Days Absent (E), Late Arrivals (F). Enter 10 employee rows with realistic values.
INSTRUCTOR DEMO — STEP 2: Use SUM to total present days across all employees. Use COUNT to count filled entries. Demonstrate the formula bar vs. cell display.
INSTRUCTOR DEMO — STEP 3: Use COUNTIF to answer — 'How many employees were absent more than 3 days?' Formula: =COUNTIF(E2:E11,">3")
INSTRUCTOR DEMO — STEP 4: Use IF in column G to flag employees as 'Bonus Eligible' (22+ present days) or 'Not Eligible'. Extend to all rows by dragging.
STUDENT PRACTICE — STEP 5: Students replicate the sheet independently. Instructor circulates. Students must complete formatting: freeze header row, bold headers, blue header background, alternating row colours.
INSTRUCTOR DEMO — STEP 6: Insert a bar chart — Present vs Absent days per employee. Add chart title, axis labels, clean colour theme. Embed chart below the data.
CLASS DISCUSSION: 'Where else in real work would this exact sheet structure be useful?' Collect 3–4 student responses before moving to Case 2.
INSTRUCTOR DEMO — STEP 1: Create the sheet live. Columns: Emp ID (A), Name (B), Department (C), Days Present (D), Days Absent (E), Late Arrivals (F). Enter 10 employee rows with realistic values.
INSTRUCTOR DEMO — STEP 2: Use SUM to total present days across all employees. Use COUNT to count filled entries. Demonstrate the formula bar vs. cell display.
INSTRUCTOR DEMO — STEP 3: Use COUNTIF to answer — 'How many employees were absent more than 3 days?' Formula: =COUNTIF(E2:E11,">3")
INSTRUCTOR DEMO — STEP 4: Use IF in column G to flag employees as 'Bonus Eligible' (22+ present days) or 'Not Eligible'. Extend to all rows by dragging.
STUDENT PRACTICE — STEP 5: Students replicate the sheet independently. Instructor circulates. Students must complete formatting: freeze header row, bold headers, blue header background, alternating row colours.
INSTRUCTOR DEMO — STEP 6: Insert a bar chart — Present vs Absent days per employee. Add chart title, axis labels, clean colour theme. Embed chart below the data.
CLASS DISCUSSION: 'Where else in real work would this exact sheet structure be useful?' Collect 3–4 student responses before moving to Case 2.
Data Sources
https://docs.google.com/spreadsheets/d/175dYTITVPp5lwcAXVlN5RaKvW_LzsI4VC0mPQRZS0r0/edit?usp=sharing
Solution Frameworks
Data Entry Framework: Header row → Data rows → Summary row. Never mix data and summaries in the same column.
Function Selection Framework: COUNT for counting cells, COUNTA for non-empty cells, COUNTIF for conditional counts. Choose based on what you are counting.
Chart Communication Framework: Every chart must answer exactly one question. This chart answers: 'Which employee was most/least present?'
Function Selection Framework: COUNT for counting cells, COUNTA for non-empty cells, COUNTIF for conditional counts. Choose based on what you are counting.
Chart Communication Framework: Every chart must answer exactly one question. This chart answers: 'Which employee was most/least present?'
Solver Guidance & Tutorials
Pre-load a blank Google Sheet and share with students before session starts — saves setup time in class.
Instructor tip: Make a deliberate error (e.g., SUM of wrong range) so students practice debugging.
For COUNTIF: stress that the condition '>3' must be in quotes. Common student mistake is omitting quotes.
IF formula for bonus: =IF(D2>=22,"Bonus Eligible","Not Eligible") — ask students to predict output before revealing.
Chart tip: Select columns B (Name) and D (Days Present) only, then insert chart. Add E (Absent) by editing data range in Chart Editor.
Tutorial Link: https://aplly.xyz/case-studies?search=&type=2&difficulty=Beginner&status=
Instructor tip: Make a deliberate error (e.g., SUM of wrong range) so students practice debugging.
For COUNTIF: stress that the condition '>3' must be in quotes. Common student mistake is omitting quotes.
IF formula for bonus: =IF(D2>=22,"Bonus Eligible","Not Eligible") — ask students to predict output before revealing.
Chart tip: Select columns B (Name) and D (Days Present) only, then insert chart. Add E (Absent) by editing data range in Chart Editor.
Tutorial Link: https://aplly.xyz/case-studies?search=&type=2&difficulty=Beginner&status=
What You'll Learn
- Problem-solving and analytical thinking
- Data-driven decision making
- Business strategy development
- Professional report writing
0
Solutions Submitted
Difficulty
Beginner
Estimated Time
30 minutes
Relevance
Fresh
Source
Google Sheets Module - Puneet Arora