How to Create an Excel Gantt Chart Template

Need to visualize your project timeline without investing in expensive, complicated software? Creating a Gantt chart in Excel is a surprisingly powerful and flexible way to track tasks, deadlines, and project progress. This guide will walk you through building a professional-looking Gantt chart from scratch, transforming a simple spreadsheet into a clear visual roadmap for any project.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Setting Up Your Project Data: The Foundation

Before you can build any chart, you need well-organized data. A Gantt chart relies on a few key pieces of information for each task in your project. We'll also add a "helper" column that makes the charting process much easier.

Open a new Excel sheet and create the following columns:

• Task Name: A brief description of the task (e.g., "Draft Website Copy," "Design Mockups").
• Start Date: The day the task is scheduled to begin.
• End Date: The day the task is scheduled to be completed.
• Duration: This is our crucial helper column. It calculates the number of days each task will take.

Your initial data table should look something like this:

To calculate the duration, click into the first cell of the "Duration" column (D2 in our example) and enter the following formula:

=C2-B2

Press Enter, then click the small square in the bottom-right corner of the cell and drag it down to apply the formula to all your tasks. Excel uses the serial number representation of dates, so subtracting the start from the end gives you the exact number of days - this is what the chart will use to draw the length of your task bars.

Building the Gantt Chart: From Data to Visual Timeline

With your data prepped, you're ready to create the chart. We'll use a specific type of chart - the stacked bar chart - and then clever formatting to make it look and function like a Gantt chart.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 1: Insert a Stacked Bar Chart

First, we only need to select the tasks and their start dates. The order is important here.

1. Click and drag to highlight your Task Name column (including the header).
2. Hold down the Ctrl key (or Cmd on Mac) and highlight your Start Date column (including the header). This lets you select two non-adjacent columns.
3. Go to the Insert tab on the Excel ribbon.
4. Find the Charts group, click the Insert Bar Chart icon, and under the 2-D Bar section, select Stacked Bar.

You’ll see a basic bar chart appear on your sheet. The tasks are likely in reverse order, and it doesn't look like a timeline yet. That's perfectly normal - we'll fix it in the next steps.

Step 2: Add the Duration Data to the Chart

Now, let's add the duration of each task to our chart. This will create the second part of our stacked bars, which will eventually become our final project bars.

1. Right-click anywhere on the chart plot area and choose Select Data.
2. In the "Select Data Source" window that pops up, look for the "Legend Entries (Series)" box on the left. Click the Add button.
3. A small "Edit Series" window will appear.
4. Click OK on the "Edit Series" window, and then OK again on the "Select Data Source" window.

Your chart now shows two sets of colored bars for each task. The first (likely blue) represents the "lead time" - the time from the project beginning to the task's start date. The second (likely orange) represents the actual duration of the task. We're getting closer.

From Messy Bars to a Masterpiece: Formatting Your Gantt Chart

This is where we transform the clunky stacked bar chart into a sleek, professional Gantt chart. It's all about clever formatting to hide what we don't need and emphasize what we do.

1. Reverse the Task Order

• Click once on the list of task names on the vertical axis (the Y-axis) of your chart to select it.
• Right-click and choose Format Axis. A formatting pane will open on the right.
• Under Axis Options, look for a checkbox that says "Categories in reverse order." Check it.

Your tasks are now in the correct chronological order from top to bottom, and you'll notice the dates have moved from the bottom to the top of the chart, which is standard for a Gantt chart.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

2. Make the "Start Date" Bars Invisible

The core trick of an Excel Gantt chart is to hide the first part of each stacked bar - the blue bars representing the start dates. This leaves only the duration bars "floating" at their correct start times, creating the Gantt effect.

• Click on any of the first-segment bars (the blue ones) in your chart. This should select all of them.
• Right-click and select Format Data Series.
• In the format pane, go to the Fill & Line (paint bucket) icon.
• Under Fill, select No fill.
• Under Border, select No line.

The blue bars have now vanished! All you see are the floating "Duration" bars, perfectly positioned along the timeline. You've officially created a Gantt chart.

3. Adjust the Timeline's Start and End Dates

Your chart likely has a lot of empty space at the beginning before your project starts. Let's tighten up the date range on the horizontal axis (the X-axis).

• Click on the dates at the top of the chart to select the horizontal axis.
• Right-click and choose Format Axis.
• In the Axis Options, you'll see a section for Bounds. These are currently set to "Auto." We need to change the Minimum bound to match (or be just before) our project's start date.
• To find the number Excel uses for your start date, go back to your data table, right-click your earliest start date, choose Format Cells, and switch the category to General. This reveals the serial number (e.g., 45443 for June 1, 2024). Copy this number.
• Go back to the Format Axis pane and paste this serial number into the Minimum bound field. Press Enter.

Your chart timeline now begins right at the start of your project, making it much cleaner and easier to read.

4. Add Finishing Touches

A few minor tweaks can make your chart look much more polished and professional.

• Better Title: Click the default "Chart Title" and give it a descriptive name like "Q3 Marketing Campaign Timeline."
• Remove the Legend: Since we made one of the data series invisible, the legend is no longer very useful and takes up space. Click on the chart legend (the box that said "Start Date" and "Duration") and press the Delete key.
• Add Color-Coding: You can click individual task bars to change their color. This is great for visually grouping tasks by phase or assigning them to different teams (e.g., green for Marketing, orange for Design).

Leveling Up: Creating a Dynamic and Reusable Template

A static chart is good, but a template you can reuse and that automatically updates is even better. Here's how to add some dynamic functionality.

Use an Excel Table for Auto-Updating

• Click anywhere inside your data range (Tasks, Dates, Durations).
• Go to the Insert tab and click Table.
• Ensure the range is correct and that "My table has headers" is checked. Click OK.

Your range is now a formatted table. Now, when you add a new task on the row immediately following the table, it automatically becomes part of the table, and your Gantt chart's source data will expand to include it.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Highlight Overdue Tasks with Conditional Formatting

You can also use conditional formatting in your data table to quickly spot tasks that are falling behind.

1. Select all the "End Date" cells in your table.
2. Go to the Home tab, click Conditional Formatting -> New Rule.
3. Choose "Use a formula to determine which cells to format."
4. Enter this formula (assuming C2 is the first End Date cell):

=AND(C2<TODAY(), D2>0)

This checks if the end date is in the past AND the progress isn't 100% (or in "Done" status, depending on your setup). You may need to add another column for task status (e.g., Not Started, In Progress, Complete) to make this more robust. A better formula with a status column in E2 would be:

=AND(C2<TODAY(), E2<>"Complete")

1. Click the Format button and choose a fill color, like light red, to make overdue tasks pop. Click OK.

Now, your data table will instantly flag tasks that are behind schedule, right next to your visual timeline.

Final Thoughts

Building a Gantt chart in Excel may seem like a complex hack, but once you follow the steps, it’s a repeatable process for creating powerful project visuals without extra cost. By setting up your data correctly and using a stacked bar chart with clever formatting, you can turn a spreadsheet into a dynamic and professional project management tool.

While Excel is fantastic for project management, gathering all the data needed to track the actual performance and ROI of those projects - especially in marketing and sales - is often a manual struggle. For that, we built Graphed. Instead of spending hours pulling reports from Google Analytics, Facebook Ads, Shopify, and your CRM, we connect to your tools automatically. You can build real-time dashboards and get instant insights by simply asking questions in plain English, turning hours of reporting work into a 30-second task.