sodcalculator

10 Tech How-To

How to Create Gantt Charts in Excel

Guide 10-43984 · Updated · Step by step

This guide shows you how to build a simple but effective Gantt chart in Microsoft Excel using a stacked bar chart. You’ll turn a task list with start dates and durations into a visual timeline that shows progress at a glance. No add-ins or advanced skills are needed—just a few formulas and a couple of formatting tricks.

What You’ll Need

  • Microsoft Excel (2016 or later, including Microsoft 365)
  • A list of tasks with start dates and durations (in days)
  • Basic familiarity with entering data and selecting cells

Step-by-Step

  1. Prepare your data table. In columns A, B, and C, enter your task names, start dates, and durations (in days). For example:

    Row 1: Task Name | Start Date | Duration (days)
    Row 2: Research | 2024-01-01 | 5
    Row 3: Design | 2024-01-06 | 7
    Row 4: Development | 2024-01-13 | 10
    Row 5: Testing | 2024-01-23 | 4
    Row 6: Launch | 2024-01-27 | 2
  2. Add a helper column for the chart. In column D, label it “Start (number)” and enter this formula in D2: =B2. Drag the formula down to fill all tasks. This column will be used to position the bars on the chart.
  3. Insert a stacked bar chart. Select the range A1:D6 (including headers). Go to the Insert tab, click Bar Chart (or the bar chart icon), and choose Stacked Bar (the first option under 2-D Bar). Excel will create a chart with two series: “Start (number)” and “Duration (days)”.
  4. Make the start series invisible. Right-click on the chart and select Select Data. In the legend entries, click on “Start (number)” and then click Edit. Change the series name to “Start (hidden)” (optional). Now click on any bar that represents the “Start (hidden)” series (they are usually on the left side of each row). Right-click and choose Format Data Series. In the Fill section, select No fill. In the Border section, select No line. This hides the start bars, leaving only the duration bars visible.
  5. Reverse the vertical axis. By default, Excel lists tasks from bottom to top. To have the first task at the top, right-click on the vertical axis labels (the task names) and choose Format Axis. Check the box for Categories in reverse order.
  6. Adjust the horizontal axis dates. Right-click on the horizontal axis (the numbers at the top) and choose Format Axis. In the Bounds section, set the Minimum to the serial number of your earliest start date (e.g., if your project starts on 2024-01-01, enter 45292, which is the serial number for that date). To find the serial number, type the date in a cell and format it as a number. Set the Maximum to the serial number of your project end date plus a few days for padding. Set the Major unit to 7 (for weekly intervals) or as desired.
  7. Remove the legend. Click on the legend (usually at the bottom or side) and press Delete, since it’s not needed.
  8. Format the bars. Click on the visible duration bars. Use the Format Data Series pane to adjust the gap width (e.g., 50% for thicker bars). You can also change the fill color to a professional blue or any color you prefer.
  9. Add data labels (optional). Right-click on the duration bars and choose Add Data Labels. By default, it shows the duration in days. To show the task name, right-click on the labels, choose Format Data Labels, and uncheck “Value” and check “Series Name” (or “Category Name” depending on your version).
  10. Save and update. Your Gantt chart is now ready. When you change a start date or duration, the chart updates automatically. To add a task, insert a new row in the data table and drag the formulas down.

Common Mistakes

  • Forgetting the helper column: The stacked bar chart needs a numerical start value. Using dates directly in the chart can cause misalignment.
  • Not hiding the start series: If you don’t set the start series to “No fill”, you’ll see two bars per task, which defeats the purpose.
  • Tasks appear in reverse order: Without reversing the vertical axis, the first task ends up at the bottom, which is confusing for most viewers.
  • Using the wrong chart type: A regular bar chart (clustered) will not work; you must use a stacked bar chart.
  • Incorrect date serial numbers: If you set the axis bounds manually, make sure you use the correct serial numbers for your dates. A wrong number will make the timeline start or end at the wrong place.

Tips

  • Use a table: Convert your data range to an Excel Table (Ctrl+T). This makes it easier to add new tasks and keeps formulas consistent.
  • Add a “Today” line: You can add a vertical line to show the current date. This requires a more advanced technique (e.g., adding a secondary axis or using a scatter plot), but it’s a great way to track progress.
  • Color-code by status: If you have a “Status” column, you can format each bar individually by selecting a single bar and changing its fill color. This helps highlight completed, in-progress, or delayed tasks.
  • Use conditional formatting for the data table: Highlight tasks that are overdue by comparing the end date (start + duration) with TODAY(). This makes the chart more informative.
  • Keep it simple: For complex projects, consider using a dedicated project management tool. Excel Gantt charts are best for small to medium-sized projects with fewer than 30 tasks.

Troubleshooting

  • Chart shows all bars the same length: This usually means the “Duration” series is not being used correctly. Check that your duration column contains numbers (not text) and that the chart is a stacked bar chart.
  • Bars are not aligned with dates: If the start bars are visible or the axis is not set to the correct serial numbers, the alignment will be off. Hide the start series and recheck the axis bounds.
  • Task names appear as numbers: This happens when the task names are formatted as numbers. Select the task name column and change the format to Text, then re-enter the names.
  • Chart doesn’t update when I change data: If you didn’t use a table, the chart might not automatically include new rows. Convert to a table or manually extend the data range in Select Data.
  • Legend won’t delete: Click on the legend, then press the Delete key. If that doesn’t work, right-click and choose “Delete” from the context menu.

Frequently Asked Questions

Can I create a Gantt chart without a helper column?

Yes, but it’s more complicated. You can use a stacked bar chart with the start date as the first series, but you’ll need to format the axis to display dates correctly. The helper column method is simpler and more reliable.

How do I add a vertical line for “today”?

Add a new series to the chart (e.g., a scatter plot with a single point for today’s date) and then add error bars to create a vertical line. This requires a few extra steps but is doable with Excel’s built-in features.

Why are my bars showing in reverse order?

Excel plots the first data row at the bottom of a bar chart. To reverse the order, right-click on the vertical axis and check “Categories in reverse order”.

Can I use this method for a project with hundreds of tasks?

Technically yes, but the chart will become cluttered and hard to read. For large projects, consider using a dedicated project management tool or a specialized Excel template with scrolling capabilities.

How do I change the date format on the horizontal axis?

Right-click on the axis, choose Format Axis, and under Number, select a date format (e.g., “Mar-01” or “03/01/2024”). You can also adjust the major unit to show weekly or monthly intervals.

Is there a way to make the bars thicker?

Yes, right-click on the bars, go to Format Data Series, and reduce the Gap Width percentage. A smaller gap width makes the bars thicker.

Related

General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.

More in this bed

The whole Tech How-To bed →