formula to make gantt chart in excel

Excel Gantt Chart Formula: Simplify Project Management

By James Thomas
AD

Transforming complex project plans into visually appealing and crystal clear Gantt charts can greatly enhance understanding and collaboration. Excel, with its extensive features, is more than capable of creating such charts, and we'll guide you through a step-by-step process to create a Gantt chart from scratch.

Before we dive into the details, ensure you have a copy of Microsoft Excel. If you're using an older version, some steps might vary. We'll use Excel 2016 or later for this guide.

Creating the Basic Table

First, you need to set up a basic table in Excel with the tasks you want to include in your Gantt chart. Each task should be on a new row, with a start and end date specified for each task. You can also include task durations if you prefer.

AD
3 Easy Ways To Make a Gantt Chart (+ Free Excel Template)
3 Easy Ways To Make a Gantt Chart (+ Free Excel Template)
How to Use Timeline Gantt Chart in Excel
How to Use Timeline Gantt Chart in Excel
Make Gantt Chart in Excel: Quick Tutorial - How to Create Gantt Charts in Excel with Progress Bars
Make Gantt Chart in Excel: Quick Tutorial - How to Create Gantt Charts in Excel with Progress Bars

For instance, your table could look like this:

TaskStart DateEnd DateDuration
Design2022-01-012022-01-1515
Development2022-01-162022-02-2843

Your chart will visually represent these tasks and their durations.

Making Gantt Charts in Excel - How To | Free Tutorial
Making Gantt Charts in Excel - How To | Free Tutorial
a project plan is shown in the middle of a large sheet with several different sections
a project plan is shown in the middle of a large sheet with several different sections
How to Create and Customize a Gantt Chart in Excel - ExcelDemy
How to Create and Customize a Gantt Chart in Excel - ExcelDemy

Creating the Gantt Chart

Now let's turn this data into a Gantt chart. First, select the data you want to use, including headings. Then, go to the 'Insert' tab and click on 'Insert Bar Chart' if you're using Excel 2016 or later, or 'Recommended Charts' or 'All Charts' in older versions.

In the Insert Chart dialog box, select the 'Bar' category, 'Stacked Bar Chart', and then click 'OK'.

AD

Modifying the Chart

The chart will initially display as a stacked bar chart. To convert it into a Gantt chart, first, change the 'Legend Entries (Series)' to 'None'. Then, for each 'Series' displayed in the 'Format Selection' panel (each task in the chart), click the 'Format Selection' button to bring up the 'Format Task' panel. Here's what you need to do:

How To Create A Simple, Effective Gantt Chart In Excel
How To Create A Simple, Effective Gantt Chart In Excel
Gantt Chart in Excel Simple, Easy and Quick Method
Gantt Chart in Excel Simple, Easy and Quick Method
the bar chart shows that there are many different types of test results
the bar chart shows that there are many different types of test results
Free Gantt Chart Template for Excel
Free Gantt Chart Template for Excel
Excel Gantt Chart Tutorial: Automatically Highlight Today’s Date with Conditional Formatting
Excel Gantt Chart Tutorial: Automatically Highlight Today’s Date with Conditional Formatting
Mastering Project Management: A Step-by-Step Guide on How to Make Gantt Charts in Excel - Earn and Excel
Mastering Project Management: A Step-by-Step Guide on How to Make Gantt Charts in Excel - Earn and Excel
  • Click on the 'Fill & Line' button, then select 'No Fill' for each task so you only see the bars, not the colored areas.
  • Next, click on the 'Shape Outline' button and select 'No Outline' for each task. This removes the borders around each bar.
  • Right-click on the axis and click 'Format Axis'. Then, set the 'Minimum Bounds' for the x-axis to the earliest date in your dataset, and the 'Maximum Bounds' to the latest date.

Your Gantt chart should now look like a proper project timeline, ready for you to add additional details or share with your team.

Adding More Features

Now that you have a basic Gantt chart, you can enhance it by adding dependencies between tasks, displaying task statuses, or even including milestones. Each of these will require a slightly different setup of your data and chart formatting, but the principles remain the same.

AD

In conclusion, creating a Gantt chart in Excel can be a powerful tool for visualizing project timelines and streamlining communication among team members. By following these steps, you can create an effective Gantt chart to manage your projects efficiently. Try it out today and transform your project planning process!

AD