Excel Guide

How to Calculate Attendance Percentage in Excel

Complete guide with ready-to-use formulas, COUNTIF method, conditional formatting for 75% rule, and step-by-step instructions.

Method 1: Basic Formula (Fastest)

If you have total classes in column A and attended classes in column B:

Excel Formula
=B2/A2*100

This gives you the percentage as a number (e.g., 75). Format the cell as Number with 2 decimal places.

CellA (Total)B (Attended)C (Formula)Result
Row 28060=B2/A2*10075.00%
Row 310085=B3/A3*10085.00%
Row 49062=B4/A4*10068.89%

Method 2: Percentage Format (Cleaner)

Use this formula and format cell as percentage:

Formula + Percentage Format
=B2/A2
How to format as percentage:
  1. Select the cell with the formula
  2. Press Ctrl + Shift + 5 (shortcut) OR
  3. Right-click → Format Cells → Percentage → Set decimal places to 2
  4. Excel will display 0.75 as 75.00% automatically

Method 3: Check 75% Rule Automatically

Add an IF formula to auto-flag attendance status:

75% Rule Check Formula
=IF(B2/A2>=0.75,"✅ Safe","❌ Shortage")
With Exact Percentage
=IF(B2/A2>=0.75,"Safe "&TEXT(B2/A2,"0.00%"),"Need "&CEILING(0.75*A2-B2,1)&" more classes")

Method 4: COUNTIF from Daily Log

If your sheet has daily attendance marked as "P" (Present) or "A" (Absent):

COUNTIF Formula
=COUNTIF(B2:B100,"P")/COUNTA(B2:B100)*100
What each part does:
  • COUNTIF(B2:B100,"P") — counts all cells with "P" (Present)
  • COUNTA(B2:B100) — counts all non-empty cells (total class days)
  • *100 — converts to percentage

Method 5: Conditional Formatting (Visual Alert)

Automatically color cells red if below 75%:

1
Select attendance % cells
Select all cells containing attendance percentages (e.g., C2:C50)
2
Open Conditional Formatting
Home tab → Conditional Formatting → New Rule
3
Set rule for below 75%
Choose "Format only cells that contain" → Cell Value → Less Than → 0.75 (or 75 if number)
4
Set red fill
Click Format → Fill → Red color → OK
5
Add green rule
Repeat for ≥ 75% with green fill
Don't want to use Excel?
Use our free online attendance calculator — no formulas needed, instant results.
Open Free Calculator
Quick Reference
Basic %:
=B2/A2*100
% Format:
=B2/A2 (format as %)
75% Check:
=IF(B2/A2>=0.75,"Safe","Low")
From P/A log:
=COUNTIF(B:B,"P")/COUNTA(B:B)*100
Online Calculator

Skip Excel entirely — use our instant online calculator.

Attended
Total
FAQ

Excel Attendance FAQ

Use =B2/A2*100 where A2=total classes and B2=attended. Format as Number with 2 decimal places.
If P=Present, A=Absent: =COUNTIF(B2:B100,"P")/COUNTA(B2:B100)*100. This automatically counts all present entries.
Add a column for each month. Use =COUNTIF(C2:C100,"P")/COUNTA(C2:C100)*100 for each month's column.
Create separate columns per subject or use SUMIF to aggregate. Overall: =SUM(attended_range)/SUM(total_range)*100.