How to Create a Monthly Gantt Chart in Excel: 7 Easy Steps

A monthly Gantt chart can turn a vague project plan into a clear timeline. Yet building one in Excel often feels harder than it should. Dates shift, bars appear in the wrong places, and a small schedule change can break the whole view.

That frustration grows when you need to compare planned work with actual progress. A simple task list quickly becomes difficult to read once several months, milestones, and overlapping activities appear.

But here's the truth: you can create a practical monthly Gantt chart in Excel with a task table, a stacked bar chart, and seven straightforward steps. I’ll show you how to build it, format it, update it, and avoid common problems.

How to Create a Monthly Gantt Chart in Excel

A monthly Gantt chart in Excel uses task details, start dates, durations, and monthly timeline columns to show when each activity begins and ends. The easiest approach uses a stacked bar chart, with one series creating invisible spacing and another showing task duration.

Before you start, decide whether your chart needs month-level planning or exact daily scheduling. Monthly planning works well for product roadmaps, marketing campaigns, construction phases, and annual operations plans.

Step 1: Build the project schedule

Open a blank Excel workbook and create a schedule with these columns:

• Task
• Start Date
• End Date
• Duration

For example, your first rows might look like this:

Keep each activity on its own row. Use actual dates in the start and end columns, even when your chart displays months instead of days.

Step 2: Calculate each task duration

In the first cell under Duration, subtract the start date from the end date and add one day:

=C2-B2+1

Here, column B contains the start date and column C contains the end date. The extra one ensures that a task running from January 1 through January 1 counts as one day.

Copy the formula down the schedule. Excel will calculate the length of every activity automatically.

If your plan uses working days only, use this formula instead:

=NETWORKDAYS(B2,C2)

That option excludes weekends. You can also add a holiday range when your project calendar includes company holidays.

Step 3: Add a monthly timeline

Next, create monthly columns to the right of the schedule. Enter the first day of each month in the header row.

For example, use:

• January 1, 2025
• February 1, 2025
• March 1, 2025
• April 1, 2025
• May 1, 2025
• June 1, 2025

Format these cells to display only the month and year. Select the cells, open the formatting options, choose a custom date format, and use:

mmm-yy

Excel will still retain the complete date behind the display. That matters when you create formulas for monthly shading.

Step 4: Create the chart helper columns

A standard stacked bar chart needs two values for each task:

• Offset, which creates empty space before the task begins
• Duration, which creates the visible bar

Add an Offset column beside Duration. If your project starts on January 1, 2025, use:

=B2-DATE(2025,1,1)

This formula counts the days between the project start and each task’s start date.

For a task beginning on January 6, the offset is five days. The chart uses those five days as invisible space before the colored task bar begins.

Your working schedule may now include:

Step 5: Insert a stacked bar chart

Select the Task, Offset, and Duration columns. Then choose:

1. Insert from the Excel ribbon.
2. Bar Chart from the chart options.
3. Stacked Bar.

Excel will create a chart with one segment for Offset and another segment for Duration.

At first, the chart may look backward because Excel usually places the first task at the bottom. You can correct this in the next step.

Step 6: Hide the offset and format the timeline

Click one of the Offset segments and open the series formatting panel. Set the fill to No Fill and the border to No Line.

The offset segment will disappear visually, leaving each colored bar positioned at its correct start date.

Now format the vertical task axis:

1. Right-click the task labels.
2. Choose Format Axis.
3. Select Categories in reverse order.

This places the first task at the top, which matches the way most people read project plans.

Next, format the horizontal date axis. Set the minimum bound to your project start date and the maximum bound to the final planned date. You can adjust the major unit to roughly 30 days for monthly spacing.

Use a consistent bar color for normal activities. A second color can highlight milestones, delayed tasks, or work that needs attention.

Step 7: Add monthly labels and improve readability

Excel may display serial numbers or uneven date labels on the horizontal axis. Format the axis labels as dates and choose a readable month format such as mmm or mmm-yy.

Then make a few practical improvements:

• Increase the chart height so task names remain readable.
• Reduce the gap width to make activity bars thicker.
• Remove unnecessary chart borders and gridlines.
• Add a clear chart title.
• Use milestone markers for major approvals or launches.
• Keep the legend only if both offset and duration need explanation.

The result should let someone answer three questions quickly: What is happening, when does it happen, and how long does it last?

How to Show Monthly Progress Without Losing Detail

A monthly view works best when the timeline stays easy to scan. If you include too many labels, colors, and dates, the chart becomes crowded.

Use the task schedule for exact start and end dates, while the visual chart communicates the broader monthly rhythm. For example, a campaign might run from February 10 through March 21, while the chart shows activity across February and March.

Here's why: monthly planning supports decisions at the portfolio level. A department leader usually needs to see that testing occupies June, while the project manager may need the exact testing start date.

You can serve both needs by keeping detailed dates in the task area and using the chart for month-level visibility.

Use conditional formatting for a calendar-style view

If you want colored cells instead of a bar chart, create a second visual schedule using monthly columns. In the first monthly cell, enter a formula like this:

=AND(F$1<=$C2,EOMONTH(F$1,0)>=$B2)

In this example, F1 contains the month header, B2 contains the start date, and C2 contains the end date.

Apply conditional formatting to the timeline range. Choose a fill color when the formula returns TRUE. Excel will shade each month that overlaps the activity.

This method is useful when your team wants a compact planning board. It also makes a good companion view beside the stacked bar chart.

How to Handle Milestones, Dependencies, and Overlapping Work

Monthly Gantt charts become more useful when they show events that influence the schedule. A launch, approval, inspection, or delivery can matter as much as a long activity.

For a milestone with no duration, add a separate Milestone column. Enter a date and represent it with a diamond, triangle, or colored marker. In Excel, you can add a new data series to the chart and use a scatter point for precise placement.

For a simpler approach, add a text marker such as ◆ beside the relevant month. This works well for executive views where exact day-level placement is less important.

Make dependencies visible

Suppose design approval must finish before development begins. Add a Predecessor column and record the related task name or task number.

Then review the schedule for gaps. If design approval ends on February 28 and development starts on March 1, the handoff is immediate. If development starts on February 20, the overlap may need a decision.

A chart cannot resolve a dependency by itself. It can reveal the timing conflict so you can discuss staffing, approval speed, or scope.

Show overlapping tasks clearly

Overlapping work is common. For instance, content writing may begin while product testing continues. Use distinct colors for different workstreams, or group related activities under a short heading.

Keep the color system limited. Five meaningful colors are easier to understand than twelve decorative ones.

How to Update the Chart When Dates Change

The most useful Excel Gantt chart is easy to maintain. Store the schedule in a structured range and use formulas for duration and offset values.

When a task moves from March 1 to March 15, change the start date. The duration, offset, and chart position should update automatically.

The best part? You can also turn the schedule into an Excel table. Select the range and choose Insert Table. New rows will inherit formulas and formatting, which reduces manual cleanup.

Add a status column

Create a Status column with values such as Planned, In Progress, Complete, Blocked, and At Risk.

You can use conditional formatting to apply colors:

• Green for completed activities
• Blue for work in progress
• Amber for activities at risk
• Red for blocked work
• Gray for planned activities

Keep status colors separate from workstream colors when possible. If color represents both status and department, readers may misinterpret the chart.

Track planned and actual dates

Add Actual Start and Actual End columns if you need progress analysis. A simple comparison can reveal whether work is drifting.

For example, if a task was planned for March 1 through March 20 but actually ran from March 6 through March 27, the seven-day delay becomes visible immediately.

You can create a second bar series for actual work. Use a lighter color for planned timing and a darker color for actual timing. This creates a useful planned-versus-actual view.

When an Excel Gantt Chart Stops Being the Best Option

Excel works well for a small project, a personal roadmap, or a schedule that changes occasionally. It becomes harder to manage when many people edit the plan or when tasks depend on one another.

You might be wondering: when should you move to a dedicated project management platform?

Consider the change when your schedule has frequent updates, multiple project owners, complex dependencies, approval steps, recurring work, or strict access requirements.

For example, a six-week website redesign may fit comfortably in Excel. A product program with 300 tasks, ten teams, and weekly scope changes will require stronger coordination.

Compare the practical trade-offs

The right choice depends on project complexity. Excel gives you flexibility and a familiar interface. A project platform can reduce repetitive coordination when planning becomes a team-wide activity.

Monthly Planning Solution: ONES.com

ONES.com combines project management and knowledge management in one platform, with AI support through ONES Assistant. ONES Project is the project management product and a Jira alternative, while ONES Wiki is the knowledge base product and a Confluence alternative. They are sold separately.

For a simple schedule, Excel may be enough. When monthly planning involves dependencies, approvals, reporting, and multiple teams, ONES.com can provide a more connected workflow with less manual chart maintenance.

Value Proposition

ONES.com helps you move from a manually maintained monthly timeline to a shared project workspace. You can plan work, manage execution, capture project knowledge, and review progress in a consistent environment.

Core Capabilities

• Scattered schedules → Jira-compatible workflows → ONES Project supports familiar issue and workflow patterns, helping teams organize work without rebuilding every planning habit.
• Manual timeline updates → Custom workflows and fields → You can represent approvals, owners, risk levels, release stages, and other project details inside the work process.
• Hidden task relationships → Dependency-aware planning → Linked work makes it easier to identify sequencing problems before a monthly milestone slips.
• Separate progress calculations → Built-in reporting → Teams can review status, workload, and delivery trends without maintaining every chart formula themselves.
• Complex sprint planning → Sprint management → Agile teams can organize planned work into sprints while maintaining a broader roadmap view.
• Repeated manual actions → Automation → Routine updates, assignments, and workflow transitions can follow defined rules, reducing administrative effort.
• Project context spread across locations → ONES Wiki → Teams can connect planning with requirements, decisions, guides, and operational knowledge in a dedicated knowledge product.
• Plugin-heavy workflows → Native feature parity → ONES.com provides core project capabilities directly, which can reduce dependence on multiple add-ons.
• Deployment restrictions → Four deployment choices → You can choose Cloud, On-Premise, Private Cloud, or Air-gapped deployment. The self-hosted version maintains full feature parity with the cloud version.

Application Scenarios

Product launch planning: A product team can organize research, design, development, testing, and launch activities across monthly milestones. Owners can see dependencies while leaders review progress through reports.

Engineering release coordination: An engineering group can combine sprint planning with release schedules. Custom fields can identify risk, environment, approval status, or affected service.

Restricted-network project management: A team operating in a controlled environment can use an On-Premise, Private Cloud, or Air-gapped deployment. This supports local governance while preserving the core project workflow.

ONES.com is most useful when the schedule is part of a larger operating process. If you only need a short, personal timeline, Excel may remain the faster choice.

Common Challenges and Practical Fixes

Challenge: The bars begin in the wrong month

Why it happens: The offset formula uses the wrong project start date, or Excel has treated a date as text.

Solution: Confirm that each date is a real Excel date. Check the formula reference and verify that the project baseline matches the earliest timeline date.

Challenge: The first task appears at the bottom

Why it happens: Excel reverses the category order in many bar charts.

Solution: Open the vertical axis settings and select Categories in reverse order. This puts the schedule in normal reading order.

Challenge: The monthly chart looks too crowded

Why it happens: The chart contains too many labels, tasks, or colors for its size.

Solution: Group minor activities, increase the chart height, shorten task names, and show only major monthly labels. Keep detailed planning in the schedule area.

Challenge: Duration values look incorrect

Why it happens: The formula may exclude the final day, or the project uses working days while the calculation uses calendar days.

Solution: Use =End Date-Start Date+1 for calendar days. Use NETWORKDAYS when weekends should be excluded.

Challenge: The chart does not update after a schedule change

Why it happens: The chart range may exclude new rows, or formulas may have been replaced with fixed values.

Solution: Convert the schedule into an Excel table, check the chart’s data range, and preserve formulas in the duration and offset columns.

FAQs

Can I create a monthly Gantt chart in Excel without using a template?

Yes. You can build one with task names, start dates, end dates, durations, and a stacked bar chart. The key calculation is the offset between the project baseline and each task’s start date. Hide the offset series, then display the duration series as the visible bar.

Should I use calendar days or working days?

Use calendar days when the project runs continuously, such as construction or event preparation. Use working days when weekends and holidays do not count toward delivery time. Choose one method consistently so your chart reflects the real planning rules.

How do I display months instead of exact dates?

Enter the first day of each month in the timeline headers, then format those cells as mmm-yy. The chart can still use exact start and end dates while the labels show a simpler monthly view. This gives you precision behind the chart and readability in the visual timeline.

Can Excel show task progress on a Gantt chart?

Yes. Add a Progress column with a percentage, then create an additional series for completed duration. For example, a 20-day task at 50% progress has ten completed days. Use a darker bar for completed work and a lighter bar for remaining work.

How many tasks should a monthly Gantt chart include?

There is no fixed limit, but readability matters. A summary chart might show 15 to 30 major activities. If you need to show hundreds of tasks, group them by phase, team, or deliverable and keep detailed schedules in a separate view.

When should I use a project management platform instead of Excel?

Consider a dedicated platform when several people update the plan, dependencies change often, approvals affect delivery, or reporting takes significant manual effort. Excel remains useful for lightweight planning, while a project platform can support larger workflows with shared visibility and automation.

Conclusion

You can create a monthly Gantt chart in Excel by preparing task dates, calculating duration and offset values, inserting a stacked bar chart, hiding the offset series, and formatting the timeline for monthly visibility.

Keep the chart focused on decisions. Show major activities, milestones, overlaps, and timing risks. Use formulas and structured ranges so schedule changes do not require rebuilding the visual.

But here's the truth: a chart only helps when the plan stays current. Excel is a practical starting point for smaller schedules. As coordination grows, ONES.com can connect project work, reporting, workflows, automation, and knowledge management in a shared environment.