To calculate attendance percentage in Excel, divide the number of sessions marked present by the number of recorded sessions, then format the result as a percentage. If a student’s attendance codes are in B2:B21, with “P” for present and “A” for absent, use =IFERROR(COUNTIF(B2:B21,"P")/COUNTA(B2:B21),0); 16 present marks in 20 recorded sessions returns 80%.
What attendance percentage means
Attendance percentage is the share of eligible sessions that someone attended. The basic calculation is present sessions divided by the sessions included in the attendance record. For example, attending 18 out of 20 classes gives 18 ÷ 20 = 0.9, or 90%. Excel stores that result as the decimal 0.9; percentage formatting displays it as 90%.
The calculation sounds simple, but the definition of an eligible session matters. A school may count only scheduled classes, while a workplace may count scheduled shifts. An excused absence might still count as a missed session for one report and be removed from the denominator for another. Decide what the percentage is meant to represent before building the formula.
Attendance percentage is also different from the number of absences. Two people can each have four absences but different attendance percentages if they had different numbers of scheduled sessions. A percentage makes the rate comparable, provided both records use the same rules for counting sessions.
Set up an attendance sheet
A reliable formula starts with a consistent data layout. For a simple record for one person, put the session date in column A and an attendance code in column B. Use one row per scheduled session, with a code such as P for present or A for absent. Keep future sessions blank until they happen; blank cells should not be mistaken for absences.
For a class or team, use one row per person and one column per session instead. Put names in column A, dates across row 1, and attendance codes in the grid below. Either arrangement works, but choose one that makes it easy to enter each session only once and to check the underlying records.
- Use a short, consistent set of codes, such as P and A.
- Record the same kind of event in each cell; avoid mixing codes with notes such as “arrived at 9:15.”
- Keep unrecorded or future sessions blank, rather than entering a zero or a dash that a formula might count.
- Write down how you will treat late arrivals, excused absences, canceled sessions, and partial attendance.
For a larger sheet, a formatted Excel table can make records easier to filter and extend. Select the range and choose Insert > Table, then confirm that the table has headers if it does. Tables are especially useful when each row represents one person’s attendance at one session, with columns for name, date, and status. Whatever layout you use, check that the formula’s range covers the intended records and not headings or summary cells.
Calculate the percentage for one person
With attendance codes in B2:B21, where each nonblank cell is either P or A, use =IFERROR(COUNTIF(B2:B21,"P")/COUNTA(B2:B21),0). COUNTIF counts the present marks, and COUNTA counts all nonblank recorded marks. If there are 16 P entries and four A entries, the formula divides 16 by 20 and returns 0.8, which displays as 80% when formatted as a percentage.
The IFERROR wrapper prevents a division error when there are no recorded sessions. It returns 0 in that case, though a displayed 0% can be mistaken for a real attendance result. If you would rather show a blank until there is data, with the same attendance codes in B2:B21, use =IF(COUNTA(B2:B21)=0,"",COUNTIF(B2:B21,"P")/COUNTA(B2:B21)). With no entries, this returns a blank; with 16 P entries and four A entries, it returns 0.8, or 80% after percentage formatting.
Enter the formula in a separate summary cell, such as C2, rather than in the range being counted. To avoid hidden inconsistencies, make sure that every recorded session has a recognized status. If someone enters “Present” instead of P, the formula will not count it as present. Excel’s COUNTIF text matching is not case-sensitive, so p and P match, but spelling and extra spaces can still cause missed counts.
Choose the right denominator
COUNTA is appropriate when every nonblank status in the range represents one session that belongs in the attendance calculation. If the range can contain additional codes, such as E for excused or L for late, decide whether those codes count as attended, missed, or excluded before relying on the simple formula. Otherwise, the denominator may include a status that the numerator does not handle as intended.
For a policy that counts only P and A as eligible sessions, with those codes in B2:B21, use =IFERROR(COUNTIF(B2:B21,"P")/(COUNTIF(B2:B21,"P")+COUNTIF(B2:B21,"A")),0). If the range contains 16 P entries, three A entries, and one E entry, this calculation returns 16 ÷ 19, approximately 84.21%. The excused entry is excluded from both numerator and denominator. Use this approach only if excluding E matches the reporting rule.
Some policies count an excused absence as a missed session; in that case, it belongs in the denominator but not the present count. Other policies exclude canceled classes because no one had an opportunity to attend. Those should not be treated like absences. The formula cannot decide which policy is correct: the person responsible for the report must define the rule, then record codes consistently with it.
Partial attendance needs a clear rule too. If a half-day or late arrival counts as half a session, a basic P/A count cannot represent that detail accurately. You could record attendance units instead, such as 1 for full attendance, 0.5 for partial attendance, and 0 for absence, then divide attended units by eligible units. Keep the unit values and the reporting policy visible so a reader can understand what the resulting percentage means.
Format and read the result
After entering the formula, select its cell and choose Home > Number > Percent Style (%) in Excel for Microsoft 365 on Windows. Excel will display 0.8 as 80%. Use Increase Decimal or Decrease Decimal in the Number group to control how many decimal places appear. For example, a result of 16 ÷ 19 can display as 84.21% or 84%, depending on the precision you choose.
Changing the displayed decimal places does not change the underlying calculation. A displayed 84% might represent 84.21%, so keep more decimal places when the extra precision matters. Conversely, displaying many decimal places can suggest a level of certainty that the attendance records do not support. Choose a sensible display for the purpose of the report.
Do not multiply the formula by 100 if you are also using percentage formatting. A formula that returns 0.8 should display as 80%; multiplying it by 100 first would make the stored result 80, which percentage formatting would show as 8000%. If a result looks 100 times too large, check for that extra multiplication and confirm the cell’s number format.
Calculate attendance for multiple people
If each person has their own row and session codes run across columns B:U, enter a percentage formula in the summary column for the first person, then fill it down for the other rows. For example, with one person’s codes in B2:U2, use =IFERROR(COUNTIF(B2:U2,"P")/COUNTA(B2:U2),0). With 18 present marks and two absences in that row, it returns 90%. When you copy the formula down, Excel adjusts the row reference for each person.
Check that the session columns cover the same reporting period for everyone. If some people joined later or had a different schedule, their denominator may need to reflect only sessions they were expected to attend. A common mistake is to compare percentages calculated over unequal or inappropriate periods without noting that difference.
For an overall group rate, divide the total number of present person-sessions by the total number of eligible person-sessions. Do not automatically average the individual percentages: a simple average gives a person with two eligible sessions the same weight as someone with twenty. For example, if one person attended 1 of 2 sessions and another attended 18 of 20, the average of their rates is 70%, but their combined rate is 19 of 22, or about 86.36%. The combined figure answers a different question and is usually the better measure of attendance across all recorded person-sessions.
Common errors and ways to check the sheet
When a result seems wrong, inspect the source cells before changing the formula. Check for blank cells, misspelled codes, extra spaces, duplicated session rows, and a range that stops before the last entry. Also confirm that the formula does not include a header or a total row. A displayed result of 0% may mean nobody attended, but it may also mean there is no data if the formula returns zero for an empty range.
- Compare the COUNTIF result for P with a manual count on a small sample.
- Confirm that the denominator follows the written attendance policy.
- Check that future sessions and canceled sessions are not included as absences.
- Verify that the percentage cell is formatted as Percentage, not as a plain number or date.
- Test a known case, such as 4 present out of 5 eligible sessions, which should display as 80%.
Data validation can reduce typing mistakes. Select the cells where users enter status codes, then choose Data > Data Validation and set Allow to List. A source list containing the allowed codes can provide a dropdown. This does not settle how each status should count, and users can still paste unexpected values, so periodic checks remain worthwhile.
Frequently Asked Questions
How do I calculate attendance percentage in Excel?
Divide present sessions by eligible recorded sessions. For P/A codes in B2:B21, =IFERROR(COUNTIF(B2:B21,"P")/COUNTA(B2:B21),0) returns the rate when every nonblank cell is an eligible session. Format the result as a percentage.
How do I calculate attendance if blank cells are future classes?
Leave future sessions blank and use a denominator that counts only completed records. COUNTA does this for nonblank cells, as long as every recorded cell is an eligible session. If you enter placeholders or additional status codes, adjust the denominator to follow your attendance policy.
Should an excused absence count in attendance percentage?
There is no universal rule. Some organizations count it as a missed session, while others exclude it from the denominator. Decide which definition your report needs, document it, and use a formula that counts the eligible statuses accordingly.
Why does Excel show 8000% instead of 80%?
The underlying formula likely multiplies the rate by 100 and the cell is also formatted as a percentage. Remove the extra multiplication; Excel’s percentage format displays a decimal such as 0.8 as 80%.
Can I average individual attendance percentages for a group total?
You can, but that gives each person equal weight regardless of how many eligible sessions they had. For the overall rate across sessions, divide the group’s total present counts by its total eligible counts instead.
Make the result useful
A percentage is most useful when readers can see its time period, denominator rule, and source records. Label the result clearly—for example, “Attendance, September” or “Present sessions ÷ eligible scheduled sessions”—and retain the underlying date-and-status entries. That context helps someone distinguish a real change in attendance from a change in schedule, policy, or data entry.
Excel for Microsoft 365 on Windows, Mac, and the web supports the basic COUNTIF, COUNTA, IF, and IFERROR formulas used here. The ribbon layout can vary slightly by platform, but the calculation is the same. Before sharing a workbook, check a few records against the original attendance source and make sure the displayed percentage uses the intended reporting rules.
Related
- Power Query Editor: What It Is, How It Works, and Why
- Excel Data Validation Icon: What It Looks Like and Does
- Data Validation Multiple Selection in Excel: What Works
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Date Difference in Years: Formulas That WorkTo find the number of complete years between two dates in Excel, use =DATEDIF(A2,B2,"Y"), with the earlier date…
- How to Show Formulas in Excel for MacTo show formulas in Excel for Mac, open the worksheet and choose Formulas > Show Formulas. You can…
- Round Excel Numbers Without a Formula: Display or ChangeYes. In Excel for Microsoft 365 on Windows, use a number format to display fewer decimal places without…
- COUNTIF in Excel Between Two Values: The Right FormulaTo count numbers between two limits in Excel, use COUNTIFS, which applies both limits to the same range.…
- Excel Rank Formula for Scores: How to Rank ResultsTo rank scores in Microsoft Excel for Microsoft 365, use =RANK.EQ(B2,$B$2:$B$6,0) when the score to rank is in…
- XLOOKUP Calculation Examples: Formulas That Work in ExcelXLOOKUP can find a matching value and return a related value, or calculate a result from the matched…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.