How to Make a Gantt Chart in Excel 2016: Step-by-Step Guide
Building a project timeline in Excel 2016 can feel harder than it should. You enter dates, add tasks, format bars, and still end up with a chart that looks confusing or refuses to update cleanly.
The frustration grows when you need to show task duration, overlapping work, milestones, and the overall delivery date in one view. A basic bar chart rarely gives you the planning clarity you need.
But here's the good news: Excel 2016 can create a useful Gantt chart with a stacked bar chart and a small amount of formatting. Follow the steps below, and you can turn a simple task list into a readable project timeline.
How to Make a Gantt Chart in Excel 2016
A Gantt chart in Excel 2016 is a stacked bar chart that shows when each task starts and how long it lasts. Excel does not include a dedicated Gantt chart button in this version, so you create one by combining invisible start-date bars with visible duration bars.
The basic process is simple: prepare task information, insert a stacked bar chart, hide the start-date series, and format the timeline for readability.
What You Need Before You Start
Set up a worksheet with at least four columns. Each row should represent one project task.
| Task | Start Date | End Date | Duration |
|---|---|---|---|
| Planning | January 6, 2025 | January 10, 2025 | 5 |
| Design | January 9, 2025 | January 17, 2025 | 9 |
| Development | January 13, 2025 | January 31, 2025 | 19 |
| Testing | February 3, 2025 | February 7, 2025 | 5 |
Use real Excel dates in the Start Date and End Date columns. Avoid typing dates as ordinary text, because Excel may not calculate the duration correctly.
For calendar days, calculate duration with:
=C2-B2+1
Here, column B contains the start date and column C contains the end date. The plus one counts both the first and last day.
For working days that exclude weekends, use:
=NETWORKDAYS(B2,C2)
If your project uses holidays, you can include a holiday range:
=NETWORKDAYS(B2,C2,$F$2:$F$10)
Keep the task list in an ordinary range rather than converting it into an Excel Table if you want the simplest chart setup. You can still expand the chart later by changing its data range.
Step 1: Create the Project Task List
Open Excel 2016 and create a blank workbook.
Enter your task names in the first column.
Enter a start date and end date for every task.
Add a Duration column using one of the formulas above.
Check that every end date is the same as or later than its start date.
Use one task per row. For example, avoid putting “Design, Development, and Testing” in a single row. Separate rows allow each activity to appear as an individual bar.
Step 2: Select the Start Date and Duration Data
Select the Task, Start Date, and Duration columns. Include the column headings, but do not include the End Date column in the chart.
The chart needs two numeric series:
Start Date: creates the horizontal position of each task.
Duration: creates the visible length of each task bar.
Excel stores dates as serial numbers. That allows the Start Date series to position each bar along a calendar axis.
Step 3: Insert a Stacked Bar Chart
Open the Insert tab.
Select Bar Chart.
Choose 2-D Stacked Bar.
Choose a horizontal stacked bar chart, not a column chart. Horizontal bars keep task names along the vertical axis, which matches the familiar Gantt chart layout.
At this stage, the chart may look wrong. You will probably see a colored section for the start date and another section for the duration. That is expected.
Step 4: Reverse the Task Order
Excel often places the first task at the bottom of the chart. A project timeline usually reads from the first task at the top to the final task at the bottom.
Right-click the vertical task axis.
Choose Format Axis.
Open the Axis Options section.
Select Categories in reverse order.
The task order should now follow the natural sequence of your project. If the horizontal date axis moves to the top, that is normal. You can adjust its position later.
Step 5: Hide the Start-Date Series
The start-date bars are only positioning devices. They should be invisible so that the duration bars appear to float at the correct dates.
Click one of the start-date sections in the chart.
Right-click and choose Format Data Series.
Under Fill, choose No fill.
Under Border, choose No line.
The visible duration bars should now begin at their actual start dates. This is the key formatting step that transforms a stacked bar chart into a Gantt chart.
Let me explain: the invisible series still occupies space, even though you cannot see it. That space pushes each duration bar along the date axis.
Step 6: Format the Date Axis
The horizontal axis may show large serial numbers instead of readable dates. Format it so the timeline makes sense to anyone viewing the chart.
Right-click the horizontal axis.
Choose Format Axis.
Under Number, choose a date format such as
mmm dorm/d.Adjust the major unit if the labels are too crowded.
You can also set the minimum and maximum bounds manually. For example, set the minimum to the first project start date and the maximum to the final project end date.
Excel may display dates as serial values such as 45663. Changing the number format fixes the appearance without changing the underlying dates.
Step 7: Adjust Bar Spacing and Colors
Right-click a visible duration bar and open Format Data Series. Reduce the Gap Width to make the bars thicker.
A gap width between 30% and 60% often creates a balanced appearance. Use a single color for a simple project, or use different colors for task categories.
For example, you might use:
Blue for planning activities.
Green for development work.
Orange for testing and review.
Gray for completed tasks.
Keep the color scheme restrained. Too many colors make the timeline harder to scan.
Step 8: Add Milestones and Project Details
A milestone usually has no duration, so it needs a different approach. You can add a milestone as a separate series using a date value and a small duration, then format its marker with a contrasting color.
For a simpler approach, insert a text box or shape above the relevant date and label it “Launch,” “Approval,” or “Release.”
You can also add:
A chart title that names the project.
Vertical gridlines that help readers match bars to dates.
A legend if multiple task categories use different colors.
A “Today” line to show current progress.
Step 9: Test the Chart
Change one start date and confirm that the corresponding bar moves. Change one duration value and check that the bar becomes longer or shorter.
If the chart does not respond correctly, check these common causes:
The dates were entered as text.
The wrong series was hidden.
The task labels and values have different row counts.
The duration formula points to the wrong cells.
The chart range does not include newly added tasks.
Why the Stacked Bar Method Works
A Gantt chart is essentially a timeline with two values for every activity: when the activity begins and how long it continues.
The invisible Start Date series creates the empty space before each task. The visible Duration series fills the remaining space. Together, they position each bar correctly.
For example, suppose Planning starts on January 6 and lasts five days. The invisible portion moves the visible bar to January 6, while the duration portion extends it through January 10.
Here's why: Excel does not need a special Gantt object to display this relationship. A stacked bar chart already knows how to place one series after another.
The Difference Between Start Date and Duration
The Start Date is a calendar position. Duration is a length of time. Treating both values as visible bars makes the chart appear incorrect.
Imagine sliding a book across a shelf. The start date tells you where to place the book. The duration tells you how long the book is. You need both measurements, but only one should remain visible.
Calendar Days Versus Working Days
Your duration formula changes the meaning of the chart. A five-day duration can mean five calendar days or five workdays.
For a construction project that runs every day, calendar days may be appropriate. For an office project that pauses on weekends, NETWORKDAYS may be more useful.
Choose one method consistently. Mixing calendar and working-day calculations can make task relationships appear inaccurate.
How to Improve the Chart for Real Projects
A basic Gantt chart shows timing, but a project team often needs more context. You can improve the chart by adding ownership, status, dependencies, and progress information beside the timeline.
Add a Status Column
Add a Status column with values such as Not Started, In Progress, Blocked, and Complete.
Although status does not automatically change the bar color in a basic Excel chart, it gives you a quick way to review the project beside the visual timeline.
For a more advanced setup, create separate duration columns for each status. For example, a completed-duration series can show finished work in gray, while a remaining-duration series uses blue.
Show Progress Within a Task
Add a Percent Complete column, then calculate completed and remaining duration.
If Duration is in column D and Percent Complete is in column E, use:
=D2*E2
For remaining duration, use:
=D2-F2
You can then place the completed and remaining values in separate stacked series. This creates a progress bar inside each task row.
Use Dependencies Carefully
Excel 2016 does not automatically manage task dependencies like dedicated project planning software. If Development cannot begin until Design ends, you must update the Development start date yourself.
A simple dependency formula might be:
=C2+1
This starts the next task one day after the previous task ends. For more complex projects, manual formulas can become difficult to maintain.
Keep the Timeline Readable
Show enough detail to support decisions, but avoid placing every minor activity on one chart. A chart with 150 rows becomes difficult to read during a meeting.
For a large project, create separate views for:
Executive milestones.
Team-level activities.
Detailed work packages.
The best part? A clear chart often needs fewer visual elements than you expect. Strong date labels, consistent colors, and sensible task grouping usually outperform elaborate decoration.
Common Excel 2016 Gantt Chart Problems
The Bars Begin at the Wrong Date
This usually happens when the start-date series is missing, formatted incorrectly, or hidden before the chart uses it properly.
Check that Start Date is included as a numeric series. Then confirm that the horizontal axis is formatted as a date axis rather than a general number axis.
The Tasks Appear Backward
Right-click the vertical task axis and select Categories in reverse order. Excel often displays the first row at the bottom by default.
If the chart still looks reversed, check whether the task range was selected in the correct order.
The Chart Shows Numbers Instead of Dates
Excel stores dates as numbers internally. Right-click the horizontal axis, choose Format Axis, and select a date format under Number.
Do not replace valid date values with manually typed labels. Formatting is safer and keeps the timeline responsive.
New Tasks Do Not Appear
Adding a row below the original range does not always expand the chart automatically. Select the chart, open Select Data, and extend the range to include the new task.
You can also create the task area as an Excel Table. When you add rows to that table, Excel may expand the chart range automatically, depending on the chart setup.
Weekend Gaps Look Incorrect
If you use working-day durations but a calendar date axis, the bars may still display weekend space. The chart axis follows calendar dates, while the duration formula counts workdays.
For a simple schedule, this may be acceptable. For a schedule that must hide weekends, you need a more advanced axis design or a dedicated planning application.
Natural Project Timeline Solution: ONES.com
Value Proposition
ONES.com combines project management and knowledge management in one platform powered by ONES Assistant. ONES Project is the project management product, while ONES Wiki provides knowledge management capabilities, and you can purchase them separately.
For teams that have outgrown manually maintained Excel timelines, ONES Project provides structured scheduling, workflow control, reporting, and collaboration without requiring every planning update to happen by hand.
Core Capabilities
Task timing is difficult to maintain: ONES Project provides structured schedules, milestones, and task relationships. Result: your timeline can reflect project changes more consistently.
Manual status updates create confusion: Custom workflows show where work sits in review, development, testing, or approval. Result: team members can see the current stage without checking several disconnected views.
Excel charts provide limited reporting: Built-in reporting gives you project progress and workload visibility. Result: you can review delivery health without rebuilding charts for every meeting.
Different teams need different fields: Custom fields let you track owners, priorities, release versions, risk levels, and other project details. Result: the workspace matches your process instead of forcing every team into one layout.
Repeated work takes time to configure: Automation can handle routine transitions and notifications. Result: fewer manual updates are needed for predictable project events.
Plugin-heavy workflows become difficult to manage: ONES Project includes Jira-compatible workflows and native project management capabilities. Result: teams can reduce dependence on multiple add-ons for core planning activities.
Restricted environments limit cloud-only tools: ONES.com supports Cloud, On-Premise, Private Cloud, and Air-gapped deployments. Result: teams can choose an operating model that matches their security and infrastructure requirements.
Moving between planning and team knowledge causes delays: ONES Project and ONES Wiki connect project work with knowledge management. Result: decisions, procedures, and delivery activities can stay easier to find.
Application Scenarios
Software release planning: A product team can manage backlog items, sprint work, testing, approvals, and release milestones in one workflow. A timeline view can show which activities threaten the release date.
Hardware development: An engineering team can connect design reviews, procurement, manufacturing preparation, and quality checks. Custom fields can identify responsible departments and approval stages.
Security-sensitive delivery: A team operating in a restricted network can use an air-gapped deployment rather than relying on an internet-connected planning environment.
ONES.com offers a free plan for up to 30 seats. It also provides full feature parity between its cloud and self-hosted versions, which can simplify deployment decisions for growing teams.
Common Challenges and Practical Solutions
Challenge: Dates Are Inconsistent
Different people may type dates in different formats, such as 1/6/25 and 6/1/25. Excel can interpret these differently depending on regional settings.
Solution: agree on one date format, apply it to the entire date range, and test a few values before building the chart.
Challenge: The Schedule Becomes Outdated
A chart is only useful when its dates and durations reflect current work. If someone changes a deadline but forgets to update the chart, the visual timeline becomes misleading.
Solution: keep calculation cells beside the chart and assign one person responsibility for schedule maintenance.
Challenge: Too Many Tasks Crowd the View
When every small activity appears on the same chart, labels become difficult to read and important milestones disappear among minor tasks.
Solution: group related work, create separate views, or show only activities that affect delivery decisions.
Challenge: Dependencies Are Hard to Track
Excel 2016 can display dates, but it does not automatically recalculate a full network of dependencies like dedicated project software.
Solution: use formulas for simple relationships and adopt a project management platform when dependencies, approvals, and changing priorities become central to the workflow.
FAQs About Creating a Gantt Chart in Excel 2016
Does Excel 2016 have a built-in Gantt chart template?
Excel 2016 does not include a dedicated Gantt chart type in the standard chart menu. You create one with a stacked bar chart, using Start Date as the invisible series and Duration as the visible series. Excel templates may simplify the layout, but you should still understand the underlying calculation so you can correct dates and expand the schedule.
Why do I need to hide the start-date bars?
The start-date series creates the spacing that positions each task at the correct point on the timeline. If it remains visible, the chart shows two colored sections for each task and looks like an ordinary stacked bar chart. Setting the series to No fill and No line keeps its positioning function while showing only the duration bar.
Can I create a Gantt chart with working days only?
You can calculate working-day duration with the NETWORKDAYS function. However, the horizontal axis still represents calendar dates, so weekends may appear as empty space. That is acceptable for many schedules. If your chart must remove weekends entirely, you need a more advanced chart design or project planning software with a working-time calendar.
How can I show project progress in Excel?
Add a Percent Complete column, then calculate completed duration and remaining duration. Plot those values as separate stacked series. Use one color for completed work and another for remaining work. Keep the two series aligned with the same start-date positioning method. This creates a visual progress bar for each task.
Can I add milestones to an Excel 2016 Gantt chart?
Yes. A milestone usually represents a single date rather than a period. You can add a separate series with a small duration and format it as a contrasting marker. Another option is to place a shape or text box at the milestone date. Label important events clearly, such as Approval, Launch, or Handover.
Conclusion
To make a Gantt chart in Excel 2016, prepare task dates and durations, insert a stacked bar chart, reverse the task order, hide the Start Date series, and format the date axis.
Use calendar-day formulas when every day counts. Use NETWORKDAYS when weekends should not contribute to duration. Test the chart after changing dates, adding tasks, and updating progress.
But here's the truth: Excel works well for a small, mostly stable schedule. As dependencies, approvals, reporting, and team collaboration grow, manual chart maintenance becomes a project risk.
Start with the Excel method when you need a quick timeline. Move to a structured platform such as ONES.com when you need a more connected way to manage tasks, workflows, reporting, and project knowledge.