How to Create a Planned vs. Actual Gantt Chart in Excel

Apply conditional formatting to highlight late tasks in red, early tasks in green, and unfinished work in amber.

9. Add milestones and a today marker

Milestones usually have no meaningful duration. You can display them as diamonds or narrow bars.

Create a separate milestone series using the milestone date as the offset and a very small duration. Format that series with a dark color and a narrow bar height.

To show today’s date, add a vertical line series or use a chart shape positioned against the current date. A manual line is quick, but it can become misaligned when the axis changes.

For a reusable chart, connect the today marker to TODAY() in a helper cell. Then update the chart when you refresh the schedule.

How the Chart Works

The visual depends on a simple idea: every horizontal bar contains an invisible waiting period followed by a visible work period.

Imagine a task planned to start five days after the project begins. Excel first creates an invisible five-day offset. It then draws the visible task duration after that offset.

The actual schedule uses the same construction. If the actual start is eight days after the project begins, its invisible offset is eight days. The visible actual bar begins three days later than the planned bar.

That separation turns date values into a timeline. Without the offset series, every task would begin at the left edge, which would create a duration chart rather than a Gantt chart.

Planned versus actual bars

Use planned bars to show the approved or expected schedule. Use actual bars to show what happened or what is currently happening.

For an incomplete task, you can show actual progress through today:

=IF(D2="","",TODAY()-D2+1)

That formula displays elapsed days from the actual start through today. It does not prove that the task is complete, so pair it with a status label.

Elapsed days versus working days

The formulas above count calendar days, including weekends. That is suitable when the project operates continuously.

If your team works Monday through Friday, use NETWORKDAYS:

• =NETWORKDAYS(B2,C2)
• =NETWORKDAYS(D2,E2)

For company holidays, include a holiday range:

=NETWORKDAYS(B2,C2,$K$2:$K$10)

Keep the offset logic consistent with your duration logic. Mixing calendar-day offsets with working-day durations can distort the chart.

How to Read Schedule Variance

A planned vs. actual view is useful because it exposes three common conditions quickly: late starts, extended durations, and overlapping work.

If the actual bar begins farther right than the planned bar, the task started late. If both bars begin together but the actual bar extends farther, the work took longer.

Example: identifying a late start

Suppose design was planned for April 5 through April 10. The team actually started on April 6 and finished on April 12.

The chart shows the actual bar shifted one day right at the beginning and extending two days beyond the planned finish. The variance is two days late.

That distinction matters. A delayed start and a longer duration may require different corrective actions.

Example: finding downstream effects

Imagine testing was planned to begin on April 23, immediately after development. Development finishes three days late, so testing starts on April 26.

The comparison reveals a dependency problem rather than an isolated testing problem. Moving testers earlier may help, but only if development can provide a usable build.

When overlapping bars are useful

Overlapping planned and actual bars are helpful for short tasks. They show whether work is tracking closely or drifting.

For long tasks, heavy overlap can make the chart crowded. In that case, use a thinner actual bar, a contrasting border, or separate views for summary and detail.

Ways to Make an Excel Schedule Chart Easier to Maintain

A chart can look impressive once and still become unreliable after several updates. Maintenance matters as much as formatting.

Use an Excel Table for the schedule range

Convert the schedule range into an Excel Table with Ctrl + T. New rows can then inherit formulas and formatting more consistently.

Check the chart after adding tasks. Some chart ranges expand automatically, while manually selected ranges may not.

Separate calculations from presentation

Keep date fields and helper calculations together in one clearly labeled area. Place the chart beside them or on a separate worksheet.

This makes it easier to audit formulas without disturbing the visual. It also reduces accidental edits to the chart.

Use a consistent status routine

Update actual start and finish dates at a regular interval. For example, a project manager might review them every Friday afternoon.

Irregular updates create misleading comparisons. A task may appear on schedule simply because nobody entered its latest actual date.

Protect formula cells

Lock helper columns if several people edit the workbook. Leave planned dates, actual dates, and status fields editable.

This prevents someone from overwriting a duration formula while entering a date.

Keep the visual focused

Limit the main chart to milestones and tasks that affect delivery. Put detailed activities in a secondary view if the chart becomes too dense.

A project chart with 20 clearly labeled tasks is usually easier to discuss than one with 200 tiny bars.

Common Excel Problems and Fixes

The bars begin at the same point

The offset series is probably missing, included incorrectly, or still visible. Confirm that the chart uses both offset and duration values.

Then set the offset series to No fill and No line.

The task order appears upside down

Open the vertical axis settings and enable Categories in reverse order. You may also need to adjust the horizontal axis so it crosses at the maximum category.

Actual bars do not appear

Check that actual start and finish cells contain real Excel dates. Text that looks like a date may not behave like a date in subtraction formulas.

Test a cell with =ISNUMBER(D2). A TRUE result confirms Excel recognizes the value as a date number.

The chart has strange gaps

Look for blank cells, inconsistent formulas, or negative durations. An actual finish date earlier than the actual start date produces an invalid duration.

Add a validation formula such as:

=IF(OR(D2="",E2=""),"Check dates",IF(E2<D2,"Invalid range","OK"))

The timeline is too compressed

Adjust the axis bounds and major unit. A chart covering two years does not need daily labels.

You can also create a summary chart for major phases and a detailed chart for daily task tracking.

Excel Gantt Chart Solution: ONES.com

Value Proposition

Excel works well for a small schedule, but a growing project often needs shared ownership, history, dependencies, and repeatable reporting. ONES.com brings project management and knowledge management into one platform, with ONES Project available as a Jira alternative and ONES Wiki available for team knowledge.

ONES Project is sold separately from ONES Wiki. You can deploy ONES.com in the cloud, on-premise, private cloud, or an air-gapped environment.

Core Capabilities

• Manual chart maintenance becomes time-consuming → Custom workflows and fields → Capture planned dates, actual dates, owners, status, and variance fields in a structured project workflow.
• Schedule changes are difficult to coordinate → Jira-compatible workflows → Teams familiar with Jira-style planning can organize tasks, statuses, and handoffs without rebuilding every project habit.
• Dependencies are easy to miss in a workbook → Sprint management and task relationships → Connect work items and reveal how a delayed predecessor can affect downstream delivery.
• Repeated formulas create maintenance risk → Built-in reporting → Turn project activity into progress views and status reports without recreating calculations for every review.
• Manual updates slow routine work → Automation → Trigger assignments, status changes, notifications, or follow-up actions when defined project conditions occur.
• Plugin-heavy setups create administration overhead → Native project management capabilities → Use workflows, custom fields, reporting, and sprint planning in one project environment.
• Cloud-only access may conflict with security requirements → Four deployment choices → Choose cloud, on-premise, private cloud, or air-gapped deployment according to your operating environment.
• Moving away from a familiar platform can be disruptive → Jira alternative positioning and compatible workflows → Transfer established planning concepts while reducing the need to recreate every process.
• Project decisions become scattered across conversations → ONES Wiki knowledge management → Keep project guidance, decisions, and working knowledge near the teams that use them.

Application Scenarios

Software release planning: A development team can track sprint work, planned release dates, actual completion, dependencies, and delivery risks in one project environment. A manager can review progress without collecting separate workbook updates from each contributor.

Hardware or manufacturing programs: Teams can coordinate design, procurement, testing, and launch activities while keeping milestone ownership visible. On-premise or air-gapped deployment may suit restricted operating environments.

Cross-functional initiatives: Marketing, engineering, operations, and support can use custom fields and workflows for their own responsibilities. Shared reports provide a common view without forcing every team to maintain separate charts.

Common Challenges

Challenge: planned dates keep changing

Solution: Keep an approved baseline separate from the current forecast. If you overwrite the original plan, the chart can no longer show how much the schedule moved.

Challenge: actual progress is incomplete

Solution: Record actual start dates as soon as work begins. For unfinished tasks, use today’s date only for a progress view and label the task as in progress.

Challenge: the chart becomes unreadable

Solution: Split the view by phase, workstream, or time horizon. Use a summary chart for leadership and a detailed chart for the delivery team.

Challenge: people interpret variance differently

Solution: Define the meaning of early, on time, late, and at risk. For example, classify any finish date more than two working days late as a delivery risk.

Challenge: updates depend on one person

Solution: Assign owners to each task and establish a recurring update routine. A chart becomes trustworthy when the process for maintaining it is clear.

FAQs

Can I create a planned vs. actual Gantt chart without a template?

Yes. You only need task names, planned dates, actual dates, offset calculations, duration calculations, and a stacked bar chart. The invisible offset series positions each bar on the calendar. Once the formulas are in place, you can copy them to additional tasks and adjust the chart formatting.

What should I do when a task has not finished?

Leave the actual finish date blank if you want to show only completed work. For a live progress view, calculate duration through today with TODAY(). Label the task as in progress so nobody mistakes elapsed time for confirmed completion.

Should planned and actual bars use the same color?

Use related colors when you want the comparison to feel connected, such as light blue for planned work and dark blue for actual work. Use different patterns or borders when color alone may be difficult to distinguish. Add a clear legend so viewers understand the meaning immediately.

How do I show tasks that finish early?

Calculate finish variance by subtracting the planned finish from the actual finish. A negative result indicates an earlier finish, while a positive result indicates a delay. You can apply conditional formatting to highlight early, on-time, and late tasks with consistent labels.

Can I use working days instead of calendar days?

Yes. Use NETWORKDAYS for task durations and include a holiday range when necessary. Keep your offset calculations consistent with the calendar model. If durations exclude weekends but offsets count every day, the bars may not align correctly.

When should I move beyond Excel?

Excel may stop being convenient when several people update the schedule, dependencies change frequently, approval steps multiply, or reporting requires repeated manual work. A project management platform can centralize ownership, workflows, reporting, and access controls while preserving a timeline view.

Conclusion

A planned and actual Gantt chart gives you more than a polished project visual. It shows whether work started late, lasted longer than expected, or created downstream pressure.

Start with separate planned and actual dates. Calculate offsets and durations, build a stacked bar chart, hide the offset series, and use clear formatting for the two schedules.

But here's the truth: the chart is only as reliable as the update routine behind it. Review actual dates consistently, preserve the original baseline, and define what each variance means.

For a small project, Excel may be enough. As collaboration, dependencies, reporting, and deployment requirements grow, ONES.com and ONES Project can provide a more structured way to manage the work and keep project knowledge accessible.