To calculate attendance percentage in Excel, divide the number of sessions attended by the total number of sessions held, then format the result as a percentage. If attended sessions are in B2 and total sessions are in C2, enter =B2/C2 in D2 and apply Percentage formatting; 18 attended out of 20 returns 90%.
What attendance percentage measures
An attendance percentage expresses attendance as a share of the relevant opportunities to attend. The basic calculation is attended sessions ÷ total sessions × 100. Excel stores the division result as a decimal, so 18 ÷ 20 is 0.9; Percentage formatting displays that value as 90%. Formatting does not change the underlying value used in calculations.
Decide what counts as a session before building the sheet. A school might count class periods, a course might count scheduled meetings, and a workplace might count shifts or workdays. The result is only meaningful when the numerator and denominator refer to the same unit and time period. If the denominator is 20 class meetings, the numerator should be the number attended out of those same 20 meetings—not the number of days present in a different date range.
Calculate attendance from attended and total counts
A two-column summary is the simplest structure for a small register:
- Column A: student or employee name
- Column B: sessions attended
- Column C: total sessions held
- Column D: attendance percentage
With the counts in B2 and C2 as described above, put =B2/C2 in D2. For the example, B2 contains 18 and C2 contains 20, so the result is 0.9 before formatting, or 90% after formatting. Copy the formula down column D for other people. Excel adjusts the row references as the formula is filled down, so the next row uses B3 and C3.
On Excel for Microsoft 365 for Windows, select the result cell or range, then choose Home > Number > Percent Style. The button displays a percent sign. You can also use Ctrl+Shift+% on Windows; on a Mac, the equivalent shortcut is Control+Shift+%. If the result shows 90% but you need a decimal place, use Increase Decimal on the Home tab. For example, 18 attended out of 19 is about 94.7% when displayed to one decimal place.
Do not multiply the formula by 100 if you plan to use Percentage formatting: doing both would display 0.9 as 9,000%. Multiplying by 100 is only appropriate if you deliberately want a number such as 90 rather than a percentage-formatted value such as 90%.
Use a session-by-session attendance register
If you record each meeting separately, put a student’s attendance marks across a row, with one column per session. For example, B1:K1 can hold session dates, and B2:K2 can contain P for present, A for absent, or a blank for a session that has not happened yet. A formula can count present marks and divide by the number of recorded marks.
With P and A as the only recorded attendance codes in B2:K2, enter =COUNTIF(B2:K2,"P")/(COUNTIF(B2:K2,"P")+COUNTIF(B2:K2,"A")) in L2. If the row has 8 P entries and 2 A entries, the result is 80% after percentage formatting. Blank cells are not included, so future sessions do not lower the percentage before they take place.
This formula treats every A as an absence and every P as attendance. If you use other codes, such as L for late or E for excused, define how each code should affect the calculation and update the numerator or denominator accordingly. An unexpected spelling or extra space, such as “Present” instead of “P,” will not match the COUNTIF criterion. Consistent data validation or a drop-down list can reduce these entry errors.
Choose what belongs in the denominator
The denominator is the total number of eligible sessions, not necessarily the number of dates shown in a spreadsheet. Cancelled classes, holidays, leave, excused absences, and sessions scheduled after today can all affect the result depending on the reporting rules. Excel cannot decide those rules; a formula only applies the definition represented by its data.
For example, if 20 classes were scheduled but 2 were cancelled for everyone, a policy that excludes cancelled classes may use 18 as the total. Someone who attended 16 of the 18 held classes would then have 88.9% attendance. If a policy instead measures attendance against all 20 scheduled classes, the same 16 attended sessions produce 80%. Neither figure is universally correct: label the metric and follow the relevant school, employer, or program policy.
Excused absences require the same care. If they are excluded, remove them from the eligible-session count; if the governing rules count them as absences, include them. Avoid quietly changing the denominator from person to person, because percentages will no longer be comparable. A note beside the report explaining exclusions can prevent readers from interpreting the result incorrectly.
Calculate an overall group percentage
For a class-wide or team-wide rate, add all attended sessions and divide by all eligible sessions. This weights each person according to their number of opportunities to attend. It is generally different from taking the simple average of individual percentages when people have different denominators.
With individual attended counts in B2:B31 and each person’s eligible session count in C2:C31, enter =SUM(B2:B31)/SUM(C2:C31) in a summary cell. If the group has 450 attended sessions out of 500 eligible sessions, the overall rate is 90%. Format the summary cell as a percentage.
With individual percentages already calculated in D2:D31, =AVERAGE(D2:D31) gives the arithmetic mean of those individual rates. That answers a different question: what is the average person’s percentage if every person counts equally? For example, one person with 9 of 10 sessions and another with 45 of 50 each have 90%, so both approaches agree. If one person has 9 of 10 and another has 40 of 50, the mean of their rates is 85%, while the combined rate is 49 of 60, or about 81.7%. Choose the measure that matches the report’s purpose and state which one you used.
Prevent blank and zero-denominator errors
If the total-sessions cell is blank or zero, direct division can produce a #DIV/0! error. That error indicates there is no valid denominator yet; it is not the same as 0% attendance. To keep a report cell blank until a positive total is entered, use a conditional formula.
With attended sessions in B2 and total sessions in C2, enter =IF(C2=0,"",B2/C2) in D2. When C2 is zero or blank, the cell displays nothing; when C2 is 20 and B2 is 18, it returns 90% after percentage formatting. This approach makes the report cleaner without labeling someone as having zero attendance before any sessions have been counted.
Check that the attended count cannot exceed the eligible total. A percentage above 100% often signals a data-entry error, repeated counting, or mismatched reporting periods. Also check whether cells contain numbers stored as text, especially after copying data from another system. A percentage may look plausible while still being wrong if some sessions were omitted or counted twice.
Common calculation choices and mistakes
- Mixing units: Do not divide days attended by class meetings held. Use the same kind of opportunity in both counts.
- Counting future sessions: If they are in the denominator before they occur, current attendance looks artificially low. Use only sessions held or otherwise eligible under the reporting rule.
- Confusing a decimal with a percent: A raw result of 0.9 equals 90%, not 0.9%. Apply percentage formatting once.
- Using the wrong group summary: Averaging percentages and dividing combined counts can produce different answers. Select the one that fits the question being asked.
- Leaving codes undefined: Decide how late arrivals, excused absences, and cancelled sessions are treated before using a register formula.
For an audit-friendly sheet, keep the raw session marks or counts alongside the percentage rather than storing only the final result. Use clear column headings, such as “Sessions attended,” “Eligible sessions,” and “Attendance %.” That makes it easier to trace a surprising percentage back to its source data.
Frequently Asked Questions
How do I calculate attendance percentage in Excel for a single person?
Divide that person’s attended sessions by their eligible total. With attended sessions in B2 and total sessions in C2, use =B2/C2, then apply Percentage formatting.
How do I calculate attendance when I have P and A marks?
Count the P marks and divide by the total of the P and A marks. With the marks in B2:K2, use =COUNTIF(B2:K2,"P")/(COUNTIF(B2:K2,"P")+COUNTIF(B2:K2,"A")). This assumes those are the only codes that count as recorded sessions.
Should I multiply the formula by 100?
Usually no. Excel’s Percentage format displays a decimal such as 0.9 as 90%. Multiplying by 100 and then applying Percentage formatting scales the result twice.
Why does my attendance formula show #DIV/0!?
The denominator is zero or blank. Confirm that the total eligible sessions have been entered. If you want the result cell to remain blank until then, use an IF formula that checks for a zero total.
How do I calculate attendance for a whole class?
For a combined class rate, divide the sum of everyone’s attended sessions by the sum of their eligible sessions. This produces a weighted rate; it may differ from the average of the individual percentages.
Related
- 3-Way Forecast Excel Template: 3 Models to Compare
- 5 Year Sales Forecast Excel Template: 5 Options to Compare
- Structured Cell References in Excel: How They Work
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- VLOOKUP Across Sheets in Excel: Formula and TipsYes. If the value to look up is in A2 on an Orders sheet, and a Product List…
- Filter Excel by Date: Steps, Custom Ranges, and FixesTo filter dates in Excel for Microsoft 365 on Windows, select a cell in your data, choose Data…
- Excel Text Filter for Multiple Words: How It WorksTo filter Excel text for multiple words, open the column’s filter menu and choose Text Filters > Custom…
- How to Calculate ROI Percentage in Excel With a FormulaTo calculate return on investment (ROI) in Excel, subtract the investment cost from its final value, divide the…
- How to Create Box File Labels in Word That Fit RightTo create box file labels in Word, measure the label area, set up a page or table to…
- Excel Age from Date of Birth with DATEDIF ExplainedTo calculate age in completed years in Excel, put the date of birth in A2 and enter =DATEDIF(A2,TODAY(),"Y")…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.