Create a Gantt Chart in Excel [Step-By-Step]

Creating a GANTT CHART IN EXCEL

In this tutorial, we will learn to create a Gantt chart in Excel. We will learn to manage and organize data in a Gantt chart and also learn to customize them in different ways. We have already learned to create line charts, bar charts, and more previously.

Steps to create a Gantt Chart in Excel

Let’s get started with this step-by-step guide to creating a Gantt chart in Microsoft Excel.

To begin with, we will take an example of sample data to create a Gantt chart, below.

activities
Table displaying a project

Here is a table showing a list of activities with a specified start date and the time duration of each activity or task in days.

Now, let us create a Gantt chart for this data table to showcase the total project completion time graphically. Follow these steps below to create a Gantt chart.

  • Click on a blank cell.
  • Go to the Insert tab.
  • Pull down on Bar Chart.
  • Select 2-D or 3-D Stacked Bar chart.
stacked bar
2-D Stacked Bar
  • Click on the Design that now appears above.
  • Under the Data section, click on Select Data
select data
Select Data option
  • A window named Select Data Source opens.
  • Click Add on the left side.
  • Select the column title in the series name – Start Date.
  • Select the series range under that column title.
  • Click OK to continue.
  • Repeat the same for the other column. Here, select the data from the Days to Complete column
selecting series
Selecting Series

Note – If the values are present in merged cells, remember to unmerge the cells before selecting the series values.

  • Move to the right side of the window now.
  • We’re now selecting the values to be present on the horizontal axis.
  • Click Edit.
  • Select the values under the column name Tasks.
  • Click OK.
  • Click OK again to close the window.
horizontal
Selecting Horizontal Axis Data
raw gantt
Default Gantt Chart

You can see that a rough Gantt chart has been prepared for us. But we have a problem. The horizontal axis has become the vertical axis. You can also notice that the dates have a huge gap between each other.

Let’s fix these problems and manage the data in our chart properly.

  • Click on the horizontal axis that displays tasks on the left.
  • Right-click on it.
  • Select Format Axis at the bottom.
format
Format Axis Option
  • A window on the right opens for you displaying axis options.
  • Check Categories in reverse order under Axis Position.
  • You will notice that the dates have been shifted to the top of the chart area.

Our next task is to hide the blue bars from the chart as we don’t need them to be displayed. Follow these steps to achieve this.

  • Click on one of the blue bars.
  • Right-click on it.
  • Click on Format Data Series.
format data series
Format Data Series
  • A window on the right opens for you displaying data series options.
  • Click on the paint bucket icon.
  • Select the Fill tab.
  • Select No fill.

You can now see that the blue bars have been hidden successfully.

no blue bars
Blue bars removed

Our next goal is to reduce the gap in the dates that are being displayed on the chart. Let’s fix this with the help of these steps below.

  • Click on the dates.
  • Right-click on it.
  • Select Format Axis.
format axis dates
Format Axis option
  • A window on the right opens for you displaying axis options.
  • You will notice the minimum and maximum bounds to set under these options.
  • You’re supposed to add the minimum date in a number format.

To convert a date in number format, do as follows.

  • Copy and paste the first date in the table in a blank cell. Here, 3 Mar 2021.
  • Go to the Home tab.
  • Under the Number section, pull down on the dialogue box displaying Date.
  • Select the Number format.
number
Number format option
  • Enter this number in the minimum bound for the date.
  • Repeat the same while entering the maximum bound date.
minbound
Minimum bound added
gantt chart
Gantt Chart is prepared

You can see that a Gantt chart is completely ready.

Customizing a Gantt chart in Excel

Let’s get started with customizing the Gantt chart.

To change the layout, follow these steps.

  • Click on the chart area.
  • Go to the Design tab that now appears.
  • Select a chart layout in Quick Layouts.
chart layout
Chart layout changed

To edit the chart title, do as follows.

  • Double-click on the existing chart title.
  • Enter the name you want for your chart.
  • Click anywhere else once you have named your chart.

To edit the chart style, do as follows next.

  • Click in the chart area.
  • Now, click on the paintbrush icon on the top right corner.
  • Select a chart style that you like.
  • You can even select the bar colors under the Color tab.
  • Hide the blue bars again by following the steps mentioned above.

To change the chart area color, do as follows next.

  • Click on the chart area.
  • Go to the Format tab that appears.
  • Pull down the shape styles and select a color you like for a chart area color.
chart styles 1
Applying chart styles
new chart style
New chart style applied

You can see that a new chart area color has been applied successfully.

Conclusion

This was all about creating and customizing Gantt charts in Microsoft Excel. We also learned to manage data in a Gantt chart. Feel free to comment below if you have any doubts regarding Gantt charts in Excel.

Reference: Ablebits