For a task-level Gantt chart, calculate percentage complete by dividing the amount of work completed by the total amount of work, then format the result as a percentage. If total planned hours are in B2 and completed hours are in C2, enter =IFERROR(C2/B2,0) in D2; for example, 6 completed hours out of 10 returns 60%.
What percentage complete means in a Gantt chart
A Gantt chart places tasks along a calendar timeline. Its bars show when work is scheduled, while a percentage-complete value describes how much of a task has actually been done. These ideas are related, but they are not interchangeable: a task can be halfway through its scheduled dates and still have only 20% of its work finished.
For a meaningful completion figure, decide what counts as completed work. It might be hours spent on defined deliverables, units produced, checklist items finished, or a manager’s documented estimate. Choose one consistent basis for comparable tasks. A percentage based on hours is not necessarily a percentage based on value or quality, so label the measure clearly.
In Excel, a percentage is stored as a decimal: 60% is stored as 0.6. Applying Percentage format changes how that value looks; it does not change the underlying calculation. This is why a correct result may appear as 0.6 until you format the cell.
Set up a task table before adding the formula
A simple layout keeps the calculation separate from the dates used to draw the chart. For example, use these columns:
- A: Task name
- B: Planned hours, units, or other total-work measure
- C: Completed hours, units, or measure
- D: Percentage complete
- E: Start date
- F: Finish date
Enter one task per row, starting in row 2. In this arrangement, the percentage formula goes in D2 and can be filled down for other tasks. Keep planned and completed values in the same unit: comparing completed hours with planned units would produce a number, but not a useful completion rate.
For a task that is tracked by a direct estimate rather than counted work, you can enter the estimate in D as a percentage, such as 60%. Do not divide that value again. If completed work is stored as a number such as 60 rather than as 60%, it will usually display as 6000% when formatted as a percentage; use 60% or 0.6 instead.
Calculate task-level completion
With planned work in B2 and completed work in C2, enter =IFERROR(C2/B2,0) in D2. The example of 6 completed hours divided by 10 planned hours returns 0.6, which displays as 60% when D2 is formatted as Percentage. IFERROR returns zero when the division would otherwise produce an error, such as when the planned amount is zero.
To apply the formula to the rest of the task list, select D2 and use the fill handle at the lower-right corner of the cell to drag down. Excel adjusts the row references automatically, so the next row uses its own planned and completed amounts. You can also copy D2 and paste it into the remaining cells in column D.
Select the percentage cells and choose Home > Number > Percent Style. Increase or decrease the displayed decimal places with the decimal-place controls in the Number group if the extra precision is useful. Formatting to zero decimal places may show 59.6% as 60%, but the cell still retains the more precise underlying value.
If you prefer to show an empty cell when no amount is planned, and planned work is in B2 while completed work is in C2, use =IF(B2=0,"",C2/B2) in D2. This avoids showing zero for tasks that have not yet been sized. Decide which treatment suits your report: zero can mean no recorded progress, while blank can mean that the percentage is not available.
Calculate overall project progress fairly
A common mistake is to average the task percentages. That gives every task equal influence, even when one task is much larger than another. If a project has a one-hour task at 100% and a 99-hour task at 0%, the simple average is 50%, although only 1 of 100 planned hours is complete. Use a weighted calculation when the project measure is hours or another countable unit.
With planned hours in B2:B10 and completed hours in C2:C10, enter =IFERROR(SUM(C2:C10)/SUM(B2:B10),0) in the project summary cell. For example, if those rows contain 100 planned hours in total and 40 completed hours, the formula returns 40%. This calculation weights each task according to its planned hours.
Choose the weighting basis to match the question the summary is meant to answer. Hours are appropriate for a labor-progress measure; quantities can suit repetitive production; and equal task weighting may be intentional when every milestone has the same importance. If milestones differ in value, assign documented weights that total 100%, then calculate progress from each milestone’s weight and completion. Do not mix bases without explaining what the headline percentage represents.
Show completed work inside the Gantt bars
A percentage column is useful on its own, but a chart can also show completed and remaining portions of each scheduled bar. One straightforward Excel approach is a date grid: put dates across the header row and tasks down the rows, then use conditional formatting to color cells that fall inside each task’s schedule. A second rule can color the part of the schedule corresponding to the recorded completion percentage.
Suppose task start dates are in B2:B10, finish dates in C2:C10, completion percentages in D2:D10, and calendar dates start in F1 and continue across the row. Select the timeline grid, such as F2:AZ10, and choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. With the grid’s top-left cell at F2, use this rule formula to mark the completed portion: =AND(F$1>=$B2,F$1<=$B2+($C2-$B2)*$D2). Choose a fill color for completed work.
For the remaining scheduled portion, apply another rule to the same grid, using =AND(F$1>=$B2,F$1<=$C2,F$1>$B2+($C2-$B2)*$D2). Give it a different fill. The header cells must contain real Excel dates, and the task dates must be valid date values rather than text. The rule uses the percentage in column D to divide the scheduled span visually; it does not verify that the task has actually progressed evenly across its dates.
This is a visual approximation, not a replacement for tracking work. If a task is reported 50% complete but its remaining half is not scheduled to happen in the latter half of the date range, the colored split can imply a misleading sequence. Keep the percentage visible in the table and explain whether the bar represents actual completion, planned progress, or simply a proportional overlay.
Actual progress versus time elapsed
When there is no work-tracking data, people sometimes estimate progress from the start and finish dates. That calculates the share of the scheduled time that has passed, not the share of work completed. It can be useful as a planned-progress reference, but it should not be labeled actual completion unless the project’s work genuinely advances evenly over time.
With a start date in B2 and finish date in C2, a simple date-based estimate in D2 is =IF(C2<=B2,IF(TODAY()>=C2,1,0),MAX(0,MIN(1,(TODAY()-B2)/(C2-B2)))). If the dates span ten days and today is five days after the start, it returns 50%, subject to the formula’s date-boundary convention. It returns 0% before the start, caps the result at 100% after the finish, and handles a same-day or reversed date range without dividing by zero.
TODAY updates when Excel recalculates the workbook, so a date-based estimate changes as time passes. It is not a frozen record of a status meeting. For a report that must preserve a past status, record the update date and the reported completion value, rather than relying only on a formula that refers to today.
Check edge cases and common errors
Before relying on a Gantt chart percentage, check the source values as well as the formula. A plausible-looking percentage can still describe the wrong thing if inputs are inconsistent or the schedule has changed.
- Zero planned amount: Dividing by zero produces an error. IFERROR prevents the error from showing, but decide whether zero or a blank communicates the situation better.
- Completed amount exceeds plan: The ratio can exceed 100%. That may reflect extra work or an outdated estimate. Investigate it before capping it, since a cap can hide useful information.
- Negative values: Check for data-entry mistakes or adjustments. Negative completed work is rarely an appropriate task-progress measure.
- Revised scope: If the planned total changes, the percentage can change even when the completed amount does not. Record or communicate the revised baseline when that distinction matters.
- Rounding: Display rounding is not the same as changing the stored value. Avoid rounding the calculation early if the value will be used in a project-wide summary.
- Blank dates or text dates: Date-grid rules depend on valid date values. Convert text dates to actual Excel dates and check that the timeline headers cover the tasks’ date range.
Choose a tracking method that fits the work
There is no universally correct percentage-complete method. The right choice depends on how the task can be observed and what the project needs to report. For measurable work, completed quantity divided by planned quantity is usually easier to audit than a subjective estimate. For a task with a clear sequence of deliverables, a checklist or milestone method can be more informative than guessing a percentage.
Before choosing, ask:
- Can the completed portion be counted in the same units as the plan?
- Does a task’s percentage describe work, elapsed time, or a manager’s estimate?
- Should project progress be weighted by effort, quantity, milestone value, or equally by task?
- Will the plan change, and how will the team show that the baseline was revised?
Whatever method you choose, apply it consistently and define it in the workbook. A short note such as completion based on accepted deliverables or completion based on hours gives readers more context than a percentage alone. For complex schedules or dependencies, a dedicated project-management application may offer more planning features than a worksheet, but Excel can work well for a transparent, manually maintained task list.
Frequently Asked Questions
What is the basic Excel formula for percentage complete?
Divide completed work by total planned work. If planned work is in B2 and completed work is in C2, use =IFERROR(C2/B2,0) in D2 and format D2 as Percentage.
How do I calculate overall Gantt chart progress?
For a progress measure based on hours, divide total completed hours by total planned hours. With planned hours in B2:B10 and completed hours in C2:C10, use =IFERROR(SUM(C2:C10)/SUM(B2:B10),0). If another basis better reflects the project, use that consistently instead.
Should I average task percentages?
Only if each task is meant to carry equal weight. If tasks differ in size and progress is measured in hours or units, divide total completed hours or units by the corresponding total planned amount to get a weighted result.
Can Excel calculate progress from start and finish dates?
Yes, a date formula can estimate the proportion of scheduled time elapsed. That is planned time progress, not proof that the same proportion of work is finished. Use actual task data for an actual-completion figure.
Why does my percentage show as 6000%?
The source value may be entered as 60 rather than 60% or 0.6. Excel treats 60 as sixty whole units, so Percentage format displays it as 6000%. Enter the intended fraction or percentage value instead.
Can a Gantt chart show the completed part of each bar?
Yes. A date-grid layout with conditional formatting can color scheduled cells and use the task’s completion percentage to split the bar visually. The display depends on valid date headers and task dates, and it remains an illustration of the percentage rather than independent evidence of progress.
Related
- Extract Data from Google Maps to Excel: Practical Options
- Export Excel to Word: Methods and Formatting Tips
- Expand Data Validation Lists in Excel
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Power Pivot vs Power BI: Which Fits?Excel Power Pivot is Excel’s data-modeling and analysis feature; Power BI is a broader platform for building, publishing,…
- Excel Gantt Chart Schedule Template: Build, Format, and UseAn Excel Gantt chart schedule template combines a task list with a dated timeline, then uses conditional formatting…
- Excel Stacked Column Chart: Show Totals ClearlyYes. Excel’s stacked column chart does not automatically show a label for each column’s total; its standard data…
- Excel Pivot Pie Chart: Create, Filter, and Fix OneYes—Excel for Microsoft 365 on Windows can create a pie PivotChart from a PivotTable. Select a cell in…
- Line Balancing Chart in Excel: Build One Step by StepA line balancing chart in Excel compares the work assigned to each station with a target cycle time,…
- Best Pivot Table Design: Five Clear Layouts for ExcelFor most Excel reports, the best pivot table design is a simple summary in Tabular Form, with only…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.