A planned-versus-actual Gantt chart in Excel compares each task’s scheduled dates with the dates it really started and finished. A practical template uses one row per task, separate planned and actual date columns, and two date grids colored with conditional formatting; a finish-variance column makes schedule slips easy to spot.
What a planned-versus-actual Gantt chart shows
A Gantt chart places tasks alongside a calendar so you can see when work is expected to happen and how long it should take. The planned view records the baseline schedule: the dates and durations the team agreed to use for tracking. The actual view records what happened. Comparing the two reveals delays, early finishes, changes in task timing, and work that has not started.
That comparison is different from showing percentage complete alone. A task can be 50% complete but still be on schedule, or it can be 90% complete after its planned finish date. Dates, progress, and remaining work answer related but distinct questions. A clear template should avoid treating them as interchangeable.
There is no single required Excel layout for this kind of chart. You can build a useful version from a task table and calendar cells, or use a downloadable workbook and adapt its fields. The setup below is designed for Microsoft Excel for Microsoft 365 on Windows and keeps the planned and actual bars separate for straightforward comparison.
Choose a layout that is easy to maintain
For a simple project, use one row per task and put the planned and actual calendars side by side. This avoids complicated chart-series arrangements and makes it possible to filter, sort, or update task records in a familiar table. A useful starting layout is:
- A: Task name; B: planned start; C: planned finish.
- D: actual start; E: actual finish; F: finish variance in calendar days.
- G:AK: planned calendar; AM:BQ: actual calendar, with matching dates in each grid’s header row.
Put field names in row 4 and the first task in row 5. Leave column AL as a visual gap between the calendars. The example ranges cover 31 days, which is suitable for a short project or a focused reporting window; extend the date grids if the project runs longer. Freeze the task-name and date-header area if the sheet is wide, and use filters on the task table when it grows.
Use genuine Excel dates in the start, finish, and calendar-header cells, not text that merely looks like a date. Apply a consistent date format, such as 4-May, so the year does not consume unnecessary space. Include the year in the underlying value; it matters when a project crosses December or spans more than one year.
Build the calendar grids in Excel
Enter the first date of the reporting window in G4. With that date in G4, the next-day header formula goes in H4; copy it right through AK4. For the actual calendar, enter the same first date in AM4. With AM4 holding that date, enter the next-day formula in AN4 and copy it right through BQ4.
Formula for H4, assuming G4 contains the first calendar date: =G4+1. This advances the date by one day. Format the header cells as dates, and check that the first and last dates in both grids line up; otherwise a visual comparison can be misleading.
Enter planned start and finish dates in B and C, and actual dates in D and E. Leave actual dates empty until work has begun or finished rather than entering an estimated date as if it were observed fact. If you want to show an estimate separately, add clearly labeled forecast columns instead of mixing estimates into actuals.
For the sample task “Design,” suppose the planned dates are May 4 to May 8 and the actual dates are May 4 to May 11. If dates are counted inclusively, that is five planned calendar days and eight actual calendar days. The finish is three calendar days later than planned. The chart will show the planned bar ending on May 8 and the actual bar continuing through May 11.
Color the planned and actual bars
Conditional formatting fills each calendar cell when its header date falls between the task’s start and finish. First select G5:AK100, or the planned-calendar range covering your task rows. On Windows, choose Home > Conditional Formatting > New Rule, then select Use a formula to determine which cells to format. With planned dates in B and C and the first selected cell at G5, use this rule formula:
=AND($B5<>"",$C5<>"",G$4>=$B5,G$4<=$C5)
Choose a planned-bar fill color and confirm the rule. The formula checks that both planned dates exist and that the date at the top of the current column is within the task’s planned range. The dollar signs keep the date columns fixed while allowing the task row and calendar column to adjust across the selected range.
Repeat the process for AM5:BQ100, using a different fill color. With actual start in D, actual finish in E, and AM5 as the top-left selected cell, use:
=AND($D5<>"",$E5<>"",AM$4>=$D5,AM$4<=$E5)
Choose a contrasting actual-bar color. For readability, use a legend and keep the same colors throughout the workbook. You can add borders between weeks or format weekend columns, but make sure those styles do not obscure the bars. These formula-based conditional formatting rules are supported in Excel for Microsoft 365 on Windows. Excel for Mac has conditional formatting too, though the dialog appearance can differ; Excel for the web supports many conditional-formatting tasks, but some editing options may vary by browser and workbook.
Calculate schedule variance without hiding the dates
A finish-variance value gives readers a quick numeric summary alongside the colored bars. If column F is labeled Finish variance, put the following formula in F5 and fill it down. It assumes planned finish dates are in C and actual finish dates are in E:
=IF(OR($C5="",$E5=""),"",$E5-$C5)
Excel stores dates as serial numbers, so subtracting one date from another returns the number of days between them. For the Design example, May 11 minus May 8 returns 3. A positive result means the recorded actual finish is later than the planned finish; a negative result means it finished earlier; zero means the finish dates match. This is calendar-day variance, not working-day variance, so weekends count.
Keep unfinished tasks blank in this finish-variance calculation because they have no actual finish date yet. A blank is more honest than a zero, which could be mistaken for a completed task that met its date. If you also need start slippage, add a separate start-variance column and calculate actual start minus planned start when both dates are available. Label each measure so users do not confuse start delay with finish delay.
Set a baseline and keep actuals trustworthy
The chart is only as useful as the plan it compares against. A baseline should be an agreed schedule snapshot, not a plan that is silently edited every time the project slips. If you overwrite planned dates to match new expectations, the chart may appear on track even though it no longer shows the original commitment. Preserve the original baseline, and record approved revisions in separate revised-plan fields or a dated change log.
Agree on what “actual start” and “actual finish” mean before collecting updates. For example, a team might define actual start as the date substantive work begins, not the date a task is assigned. Define whether finish means the work is complete, reviewed, or accepted. Consistent definitions make comparisons across tasks and reporting periods more meaningful.
- Update actual dates from a consistent source, such as a task owner’s weekly status update.
- Leave unknown actual dates blank; do not substitute the planned date to make the chart look complete.
- Record changes to scope, dependencies, or the baseline so a delay is not interpreted without context.
- Protect formula and header cells if many people edit the workbook, while leaving input cells accessible.
A planned-versus-actual view does not automatically explain why a task moved. Dependencies, resource availability, approval delays, and scope changes may all matter. Add an owner, status, or notes column if readers need context, but avoid turning the schedule into an unstructured notes dump.
Common problems and practical fixes
Bars do not appear. Check that the task dates are real Excel dates, that the conditional-formatting rule applies to the intended range, and that the formula references the correct start and finish columns. Also confirm the dates fall within the calendar window. A task outside the displayed month will not produce a visible bar.
Bars appear one day too long or too short. Decide whether the finish date is inclusive. The rules above color both the start and finish dates, so a May 4–May 8 task occupies five calendar cells. If your team defines finish as an exclusive boundary, adjust the rule and document that convention rather than mixing counting methods.
Actual bars are confused with estimates. Do not color forecast dates as actuals. Use a third, explicitly named forecast view or a separate status indicator. Similarly, avoid using a blank actual finish to imply that a task is late: it may simply be in progress. An explicit status field can distinguish “Not started,” “In progress,” and “Complete.”
The workbook is too wide. A daily calendar is helpful for short projects but becomes unwieldy across many months. Use weekly columns for a high-level roadmap, or split a long project into phases or reporting periods. Keep the detail table intact and change the displayed time scale only when the audience no longer needs day-level precision.
Excel template, chart, or project-management tool?
A conditional-formatting template is often the simplest choice when a project has a manageable number of tasks and someone can maintain the dates. It is transparent: users can inspect the cells, formulas, and rules. It can also be tailored to a team’s labels and reporting needs without requiring a separate system.
A native Excel stacked bar chart can be useful for presentations, but planned and actual bars need careful data shaping and axis formatting. It is less direct to maintain than two aligned calendar grids when users need to edit dates frequently. A calendar-grid template is also not a substitute for dependency scheduling: changing one task’s dates will not automatically reschedule successors unless you build additional logic.
Dedicated project-management software is worth considering when the schedule needs linked dependencies, multiple calendars, resource assignments, permissions, audit history, or automated status reporting. For a compact project update, Excel may be sufficient. The right choice depends on the complexity of the workflow and how many people need to update or approve it, rather than on the chart’s appearance alone.
Frequently Asked Questions
Does Excel have a planned-versus-actual Gantt chart template?
Excel may show ready-made templates in its template gallery, but availability can vary. Search the templates when creating a workbook, or build the layout above in a blank sheet. Check that any downloaded template distinguishes baseline dates from actual dates before relying on its comparison.
How do I show planned and actual dates in the same Gantt chart?
One accessible method is to use two aligned date grids, with planned start and finish driving one grid and actual start and finish driving the other. Conditional formatting colors the dates between each pair of endpoints, making differences visible without combining the two schedules into one bar.
Can Excel show an in-progress actual bar if there is no finish date?
Yes, but the rule needs to use a chosen reporting date as the temporary endpoint when actual finish is blank. Keep that reporting date clearly labeled and do not store it as the task’s actual finish. For a simple baseline template, leaving the actual bar blank until a finish is entered avoids implying that a forecast is a recorded actual.
Should finish variance count weekends?
The example formula uses calendar days, so weekends count. That makes it simple to interpret as the difference between two dates. If your schedule is managed in working days, define the workweek and holidays and use a working-day calculation consistently; do not compare calendar-day variance with a business-day target as if they were the same measure.
What is the difference between planned, actual, and forecast dates?
Planned dates are the chosen baseline, actual dates record what happened, and forecast dates estimate what may happen next. Keeping these as separate concepts prevents an updated prediction from overwriting the history needed to assess schedule performance.
Make the chart useful for decisions
A readable planned-versus-actual Gantt chart is more than a colored calendar. It preserves the baseline, records actual dates consistently, and gives viewers enough context to understand differences. Start with a limited reporting window, label the colors and variance convention, and test the sheet with a task that starts late and one that finishes early. Once the date logic is clear, extend the rows or calendar as the project requires.
Related
- Excel Text Number Format: How It Works and When to Use It
- Excel Text Filters: How to Find Matching Rows Easily
- Excel Text Box Wrap Text: How to Enable It
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Chart Format Code for Millions: Examples and TipsTo show Excel chart values in millions, apply the custom number format #,##0,,"M"; for one decimal place, use…
- Dot Progress Chart in Excel: Create One Step by StepExcel does not include a built-in chart type called a dot progress chart, but you can create the…
- Yearly Gantt Chart Excel Template: Build and Use OneYes. A yearly Gantt chart Excel template can show tasks against the 12 months of a selected year:…
- Excel Chart Color Based on Value: How to Set It UpYes. In Excel for Microsoft 365 on Windows, the most reliable way to color chart points according to…
- Excel Column Chart Colors Based on Values: How to Set ThemTo color an Excel column chart by value, use separate helper series for the value ranges you want…
- Excel Extend Trendline Beyond Data Points?Yes. In Excel for Microsoft 365 on Windows, select the chart, choose Chart Design > Add Chart Element…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.