How to calculate the percentage of tasks completed in Excel

Done divided by tasks, without the N/A rows

The tracker has 11 tasks. Four are Done (one typed as lowercase "done", which COUNTIF still counts because it ignores case). Two tasks were dropped and marked N/A. They will never be finished, so if they stay in the bottom line the project can never reach 100%.

The top of the fraction counts Done. The bottom counts task names in column A, which every real task has, and subtracts the N/A rows. The range runs to row 14 so the two empty template rows are included; empty rows add nothing to COUNTIF or COUNTA, so you can extend the range ahead of new tasks. Format the cell as Percent with Ctrl+Shift+%.

Why your percentage is wrong: the bottom line

Most wrong answers come from dividing by the wrong count. Three versions people use, all on the same data: dividing by ROWS(C2:C14) counts the empty template rows as tasks and gives 31%. Dividing by COUNTA(C2:C14) counts statuses, not tasks, so the Training video with no status yet drops out, and the N/A rows stay in: 40%. Dividing by COUNTA(A2:A14) alone keeps the N/A tasks and shows 36%.

Count tasks from a column that every task fills in, usually the task name, and decide on purpose what to do with N/A, Cancelled or On hold. If you want a readable label next to the percentage, build one with &, as below.

Weight by hours: percent of the work done

Counting tasks treats a 2-hour kickoff meeting the same as a 30-hour front-end build. The four finished tasks here are the small ones, so 44% of tasks is far ahead of the work actually done. If each task has an estimate, divide the hours of finished tasks by the total hours, again leaving out N/A.

SUMIF adds column D only where column C says Done. Use this version for status reports to a manager or client, where "how much of the work is left" matters more than how many items are ticked. Use the plain task count for checklists where every item is about the same size.

Percent complete per person with COUNTIFS

To see each owner's progress, add the owner as a second condition to every count. For Priya the formula counts her Done tasks and divides by her tasks minus her N/A tasks. Put the names in a column, point the formula at that cell instead of typing "Priya", and fill it down.

Some teams give partial credit, for example half a task for In progress: =(COUNTIF(C2:C14,"Done")+0.5*COUNTIF(C2:C14,"In progress"))/(COUNTA(A2:A14)-COUNTIF(C2:C14,"N/A")) gives 56% here. That is a judgement call, not a measurement, so say so on the report if you use it.

Add a progress bar

G2 holds the % complete from the first formula. A text bar works in any Excel version and in a printed report: REPT repeats a block character once per 10%, and a lighter block fills the rest, so the bar always has 10 segments.

For a graphic bar, use a data bar. Set its minimum and maximum to the numbers 0 and 1. With the default automatic settings Excel sizes the bar from the values in the selected range, not from 0% to 100%, so a single cell's bar does not show how far along the project is.

1. Select G2.
2. Go to Home > Conditional Formatting > Data Bars and pick a fill.
3. Go to Home > Conditional Formatting > Manage Rules, select the data bar rule and click Edit Rule.
4. Set Minimum to Type Number, Value 0, and Maximum to Type Number, Value 1. Click OK twice.