đ
What You're BuildingYou'll create a live attendance register for 10 employees that automatically calculates attendance metrics, flags bonus eligibility, and visualises present vs absent days â all using Google Sheets formulas and charts.
Attendance
| Emp ID | Name | Dept | Days Present | Days Absent | Late | Bonus Eligible |
|---|---|---|---|---|---|---|
| E001 | Anita Sharma | Sales | 24 | 2 | 1 | Bonus Eligible |
| E002 | Rohit Verma | Tech | 19 | 7 | 0 | Not Eligible |
| E003 | Priya Nair | Operations | 26 | 0 | 2 | Bonus Eligible |
| âĻ 7 more rows âĻ | ||||||
đĒ
Step-by-Step Guide1
Set Up Your Sheet Structure
Open a new Google Sheet. In row 1 enter these column headers: Emp ID, Employee Name, Department, Days Present, Days Absent, Late Arrivals, Bonus Eligible. Then enter data for 10 employees in rows 2â11.
TipMake Days Present + Days Absent = 26 (working days in March) for each row. This keeps your data realistic.
2
Format the Header Row
Select row 1. Apply: Bold, background colour #1A3C6E (dark blue), white font. Then go to View â Freeze â 1 row so headers stay visible when scrolling. For alternating row colours, select A2:G11 â Format â Alternating colours.
3
SUM â Total Present Days
In a summary cell (e.g., I2), write this formula to total all present days:
Formula
=SUM(D2:D11)
How it worksSUM adds every number in the range D2:D11. If you add more employees later, extend the range to D2:D20.
4
COUNTIF â Employees Absent More Than 3 Days
In cell I4, count how many employees exceeded 3 absent days:
Formula
=COUNTIF(E2:E11, ">3")
Common MistakeThe condition ">3" must be in quotes. Writing =COUNTIF(E2:E11, >3) without quotes will cause a #ERROR.
5
IF â Bonus Eligibility (Column G)
In cell G2, write the bonus eligibility formula. Then drag it down to G11:
Formula in G2 (drag to G11)
=IF(D2>=22, "Bonus Eligible", "Not Eligible")
=IF( D2>=22, "Bonus Eligible", "Not Eligible" )
D2>=22 â The condition: is Days Present âĨ 22?
"Bonus Eligible" â Value if condition is TRUE
"Not Eligible" â Value if condition is FALSE
6
Insert a Bar Chart
Select columns B (Name) and D (Days Present) using Ctrl+Click. Then go to Insert â Chart. In the Chart Editor, choose Column chart. To add Days Absent as a second series, click Add Series and select column E. Set chart title to "March Attendance: Present vs Absent".
Chart Design TipUse contrasting colours â blue for Present, red/orange for Absent. Click any bar in the chart to change its colour in the Format panel.
đ
Summary Metrics to BuildMetrics in I2:J8
Total Present =SUM(D2:D11)
Total Absent =SUM(E2:E11)
Average Present =AVERAGE(D2:D11)
Max Present =MAX(D2:D11)
Min Present =MIN(D2:D11)
> 3 Absent =COUNTIF(E2:E11,">3")
Bonus Eligible =COUNTIF(G2:G11,"Bonus Eligible")
Formatting Checklist
- âRow 1 frozen (View â Freeze)
- âHeader row bold, dark blue bg, white text
- âAlternating row colours applied
- âColumn widths adjusted â no text cut off
- âChart embedded below data table
- âChart has title and axis labels