Guide · 2026-09-21

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

Monthly project plans often look simple until overlapping tasks, shifting deadlines, and unclear ownership turn your worksheet into a maze. A few colored cells may show activity, but they rarely explain whether work is on track.

That confusion creates practical problems. You may miss a dependency, underestimate a task, or spend hours recoloring your timeline whenever a date changes. A static project list cannot give you a reliable visual schedule.

But here’s the solution: build a monthly Gantt chart in Excel with task details, start and end dates, and conditional formatting. I’ll walk you through seven steps, using a small project example you can adapt immediately.

How to Make a Monthly Gantt Chart in Excel

A monthly Gantt chart in Excel is a timeline that shows project tasks across calendar months, with colored cells indicating when each task starts, runs, and ends. You can create one without a specialized project tool by combining a task table, date calculations, and conditional formatting.

The fastest approach is to place task information on the left and monthly periods across the top. Excel then colors each month when the task overlaps that period.

Step 1: Create the Task Information Area

Open a blank Excel workbook and create these headings in row 1:

For example, a website launch plan might look like this:

Task ID Task Name Owner Start Date End Date Duration Status
1 Confirm requirements Maya January 5, 2026 January 14, 2026 10 days Complete
2 Create design concepts Leo January 12, 2026 February 6, 2026 26 days In progress
3 Build landing page Priya February 2, 2026 March 6, 2026 33 days Not started

Keep one task per row. This makes sorting, filtering, and updating much easier later.

Calculate Task Duration

In the Duration column, subtract the start date from the end date. If the start date is in cell D2 and the end date is in E2, enter:

=E2-D2+1

The +1 counts both the first and last day. Without it, a task running from January 5 through January 14 would show nine days instead of ten.

If you want working days only, use:

=NETWORKDAYS(D2,E2)

This excludes Saturdays and Sundays. You can also add a holiday range if your project uses a fixed holiday calendar.

Step 2: Add Monthly Timeline Headers

Choose the first column after your task details. If your project information ends in column G, start the timeline in column H.

Enter the first day of each month in the timeline header row. For example:

After entering the first two months, use this formula in the next header cell:

=EDATE(H1,1)

Copy the formula across as many months as your plan requires. The EDATE function moves the date forward by one calendar month while keeping the timeline consistent.

Format Headers as Month Names

Select the timeline header cells, open the number format options, and choose a custom format such as:

mmm-yy

Your dates will appear as Jan-26, Feb-26, and Mar-26. Excel still stores the complete date, which is important for the coloring formula.

You can use mmmm yyyy if you prefer full names, such as January 2026.

Step 3: Create the Monthly Gantt Grid

The Gantt grid is the empty area beneath the month headers. Each cell represents the relationship between one task and one month.

For example, the cell under January 2026 and beside “Create design concepts” should be colored because the task runs during January. The cell under March should also be colored because the task continues into March.

At this stage, leave the grid blank. Conditional formatting will fill it automatically.

Choose a Clear Layout

Freeze the task columns or the top row so labels remain visible while you scroll. To do this, select the cell immediately below and to the right of the area you want to keep visible, then choose View and Freeze Panes.

Widen the Task Name column enough to show complete descriptions. Keep the month columns narrow, but not so narrow that the color blocks become difficult to read.

Step 4: Add Conditional Formatting

Select the entire Gantt grid, excluding the month header row. Then choose Home, Conditional Formatting, and New Rule.

Select the option that lets you use a formula to determine which cells receive formatting. Use this formula if:

=AND(H$1<=$E2,EOMONTH(H$1,0)>=$D2)

This formula checks two conditions:

If both conditions are true, Excel colors the cell. The dollar signs keep the task date columns fixed while the month reference changes as the rule moves across the grid.

Match the References to Your Layout

Your formula must match your actual columns. In this example:

If your start date is in column F, replace $D2 with $F2. If your timeline starts in column J, replace H$1 with J$1.

Choose a fill color, such as blue, and select a contrasting font color if needed. Then apply the rule to the full Gantt grid.

Step 5: Add Status Colors

A single color shows timing, but status colors can make the plan easier to scan. You might use:

There are two practical ways to do this. The simpler method is to create separate conditional formatting rules for each status. The more advanced method uses the Status column inside each formula.

For completed tasks, you could use:

=AND($G2="Complete",H$1<=$E2,EOMONTH(H$1,0)>=$D2)

For tasks in progress, use:

=AND($G2="In progress",H$1<=$E2,EOMONTH(H$1,0)>=$D2)

Assign a different fill color to each rule. Review the rule order so a general timing rule does not override your status-specific rules.

Step 6: Add Milestones and Key Dates

Milestones are important events with little or no duration. Examples include approval, testing completion, launch, and client sign-off.

Add a Milestone column or create a separate section below the task list. A milestone can use the same start and end date, such as March 6, 2026.

For a milestone marker, create a rule that checks whether the month contains the milestone date:

=AND(H$1<=$F2,EOMONTH(H$1,0)>=$F2)

Here, column F would contain the milestone date. Use a diamond symbol, dark fill, or bold border to distinguish milestones from longer task bars.

Because a monthly view can hide the exact day, add a note beside the task name when a milestone falls near the beginning or end of a month.

Step 7: Test, Clean Up, and Share the Plan

Change one start date and one end date to test the chart. The colored range should move automatically. Then check these situations:

Remove unused columns, correct spelling, and add a legend for colors. Format dates consistently and use a clear title above the schedule.

Before sending the plan to other people, decide who can edit dates and who should only review the timeline. A simple protection setting can prevent accidental changes to formulas and formatting.

How the Monthly Gantt Formula Works

The formula works because it compares two date ranges: the task period and the calendar month. A task appears when those ranges overlap by at least one day.

For example, a task running from January 28 through February 3 overlaps both January and February. The formula colors both months, even though most work happens in only one week.

Here’s why: the formula does not ask whether the task starts in a particular month. It asks whether any part of the task exists inside that month.

Why the Month-End Check Matters

Suppose a task starts on January 31 and ends on February 2. Checking only whether the month’s first day falls inside the task would miss January.

The EOMONTH function solves that problem by finding the final day of the month. The formula then checks both ends of the month.

This approach works for short tasks, long tasks, and tasks that cross several monthly periods.

What the Dollar Signs Do

In H$1, the row is fixed while the column can change. When Excel moves the rule from January to February, the reference changes from H1 to I1.

In $D2 and $E2, the columns stay fixed while the row can change. When Excel moves down to another task, row 2 becomes row 3.

Without these references, the formula may compare the wrong dates and color unexpected cells.

Monthly Versus Weekly Gantt Charts

A monthly chart gives you a high-level view. It works well for a six-month product launch, an annual marketing plan, or a construction schedule with broad phases.

A weekly chart gives you more control over short activities. It suits software sprints, event preparation, and campaigns where a few days can change the outcome.

Planning view Best for Main limitation
Monthly Long projects and executive updates Hides daily timing
Weekly Medium-term planning and team coordination Requires more columns
Daily Short deadlines and detailed execution Becomes wide quickly

You might be wondering: which view should you choose? Use monthly periods when the audience needs direction rather than daily instructions. Add a weekly or daily view when timing precision affects handoffs.

Ways to Make the Chart Easier to Read

A working chart can still become difficult to interpret. Small design choices help people understand the schedule before they read every task name.

Use Short Task Names

Write “Approve wireframes” instead of “Obtain final approval for the updated website wireframe concepts.” Put additional context in a notes area or a related planning page.

Separate Task Types

Use a different style for phases, deliverables, and milestones. A phase might use a darker border, while a milestone uses a diamond symbol.

Show the Current Month

Add a vertical border around the current month or apply a subtle header color. This helps people see where the project stands during a review meeting.

Use a Legend

Place a small legend above or beside the chart:

The best part? A clear legend reduces questions during status meetings because everyone reads the colors the same way.

Common Mistakes in Excel Gantt Charts

Many problems come from date handling rather than the visual design. Excel may treat a date as text if it includes inconsistent punctuation or an unexpected format.

Test a date by changing its number format. If it does not behave like a real date, re-enter it using a consistent format such as 1/5/2026.

Another common issue is an end date earlier than the start date. Add a validation rule or a warning column with:

=IF(E2<D2,"Check dates","")

Blank rows can also receive formatting if your rule covers more rows than necessary. Limit the conditional formatting range to the expected task area, or add a condition that the task name is not blank.

Finally, remember that a monthly bar does not show workload. Two tasks colored in the same month may require very different amounts of effort. Add a resource view when capacity matters.

Natural Excel Gantt Chart Solution: ONES.com

ONES.com product screenshot

Value Proposition

ONES.com combines project management and knowledge management in one platform, with AI support through ONES Assistant. ONES Project is a Jira alternative for teams that need structured schedules, custom workflows, and reporting beyond a manually maintained Excel chart.

You can still use a monthly Excel view for simple planning. A dedicated project workspace becomes more useful when several people update tasks, dependencies, approvals, and progress at the same time.

Core Capabilities

Application Scenarios

Small product team: A team planning a six-month release can replace a wide Excel timeline with tasks, sprints, owners, and reports. The team can retain a high-level monthly view while managing detailed work in the project workspace.

Restricted-network organization: A team that cannot use a public cloud service can choose an On-Premise, Private Cloud, or Air-gapped deployment. The self-hosted version maintains feature parity with the cloud version.

Approval-heavy marketing project: A campaign team can define review stages for copy, design, legal approval, and publication. Each handoff becomes part of the workflow instead of a separate color or note in Excel.

Common Challenges and Practical Fixes

Challenge: The timeline shows months but hides important deadlines

Solution: Add milestone markers and include exact dates beside major approvals, launches, and handoffs. Use a weekly view for activities where a delay of two or three days matters.

Challenge: Conditional formatting colors the wrong cells

Solution: Check the row and column references. The month header row should have a fixed row reference, while the start and end date columns should have fixed column references.

Challenge: The chart becomes too wide

Solution: Group months into project phases, reduce the visible range, or create separate views for planning and execution. A single chart does not need to display every period forever.

Challenge: Several people overwrite changes

Solution: Protect formula cells, limit editing permissions, and define who owns schedule updates. For larger teams, use a project platform with role-based workflow controls.

Challenge: Colors do not explain project health

Solution: Add status, owner, dependency, and risk columns. A task can be on schedule while still blocked, over capacity, or waiting for approval.

FAQs

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

Yes. You need task names, start dates, end dates, monthly headers, and a conditional formatting rule. The formula checks whether each task overlaps each month. A template can save setup time, but building the chart yourself helps you understand how to adjust it later.

What formula colors task months in Excel?

A reliable formula is =AND(H$1<=$E2,EOMONTH(H$1,0)>=$D2), assuming H1 contains the month header, D2 contains the start date, and E2 contains the end date. Adjust the cell references to match your own layout.

How do I show a task that crosses two months?

Use a formula that checks both the first and last day of each month. If any part of the task overlaps that period, Excel colors the cell. This allows a task running from January 28 through February 3 to appear in both January and February.

Can I add progress percentages to the chart?

Yes. Add a Progress column with values such as 25%, 50%, or 100%. You can use a second conditional formatting rule, data bars, or a darker fill to show completed progress. Keep the progress indicator visually distinct from the schedule bar.

Is Excel suitable for a large project schedule?

Excel works well for small projects, simple timelines, and one-person planning. It becomes harder to maintain when many people edit tasks, dependencies, approvals, and reports. At that point, a project platform such as ONES Project can reduce manual coordination.

Conclusion

To make a monthly Gantt chart in Excel, create a task list, add start and end dates, build monthly headers, apply an overlap formula, and use formatting to show timing and status.

Test the chart with tasks that cross month boundaries, add milestones for critical dates, and keep a legend visible. These small details turn colored cells into a useful planning view.

But here’s the truth: Excel is strongest when your schedule is manageable and updates are limited. As coordination grows, structured workflows, reporting, and shared ownership become more valuable than manual coloring.

Start with the seven-step method above, then move to a dedicated project workspace when your planning needs outgrow a simple monthly timeline.