Gantt chart in Excel

This instruction will guide you to build a Gantt chart in MS Excel using existing data

Table of contents

1. What is a Gantt chart
2. Getting Started
3. Insert Graph and Add Data
   1. Adding a graph to Excel spreadsheet
   2. Adding starting dates to tasks
   3. Adding names to tasks
   4. Adding duration to tasks
4. Modifying graph layout
   1. Remove starting date as independent bars
5. Benefits of a Gantt chart

What is a Gantt chart

A Gantt chart is a staggered-layered horizontal bar chart. Its serves as a visualization of a horizontal timeline demonstrating the start and end dates of multiple tasks. A Gantt chart provides a viewer a quick, generalized breakdown of time-based weights and sequences of tasks.

Getting Started

You will need the tasks and their start date or duration entered in a tabular form. Here is an example.

Insert Graph and Add Data

Adding a graph to Excel spreadsheet

1. Go to [Insert] > [Charts] > [2-D bar] > Stacked Bar.

   

   A blank graph appears.

Adding starting dates to tasks

1. Right-click on the graph and click Select Data.

   The Select Data Source window appears.

2. Click the Add button.

   

   The Select Data Source transforms to Edit Series window.

   Click on the Up Arrow adjacent to Series name text box to add a title for the starting dates.

   The Edit Series box transforms to show only a text box for select or entering the range of the title.

   

3. Click on the title for starting date.

   

   Next, click the Down Arrow adjacent to Series name textbox to complete selection of title.

   The Edit Series box transforms back to its original form.

4. Click on the Up Arrow adjacent to Series values text box allow selection of task names in the chart.

   

   The Edit Series box transforms to show only a text box for select or entering the range of cells storing the task starting dates.

   Left-click and drag to select the cells containing the task start dates. Click the Down Arrow to complete selection.

   

   The Edit Series box transforms back to its original form. Both text boxes should contain the cell ranges containing the required information.

5. Click OK to save the data selected in the above steps.

Adding names to tasks

1. Click on the Edit button in Select Data Source window.

   

   The Select Data Source window transforms into Axis Labels window.

   NOTE: Even though the Edit button’s caption says Horizontal (Category) Axis Labels, the bar chart is horizontal, effectively the conventional x and y axes placement swapped.

2. Click on the Up Arrow button adjacent to the Axis label range text box.

   

3. Left-click and drag to select the cells containing the task names.

   

   The Axis Labels text box now contains the range storing the names of tasks.

   Click on the Down Arrow to return to the initial Axis Labels window.

   

Adding duration to tasks

The following steps repeat those in Adding starting dates to tasks above, with the exception of selecting different ranges of cells. Skip to Modifying graph layout if you understand how to add new data to a graph.

1. Click the Add button in the Select Data Source window.

   

   The Select Data Source window transforms into Axis Labels window.

   Click on the Up Arrow adjacent to Series name text box to add a title for the starting dates.

   The Edit Series box transforms to show only a text box for select or entering the range of the title.

   

2. Click on the title for task duration.

   

   Next, click the Down Arrow adjacent to Series name textbox to complete selection of title.

   The Edit Series box transforms back to its original form.

3. Click on the Up Arrow adjacent to Series values text box to allow selection of task durations in the chart.

   The Edit Series box transforms to show only a text box for select or entering the range of cells storing the task durations.

4. Left-click and drag to select the cells containing the task names. Click the Down Arrow to complete selection.

   

   The Edit Series box transforms back to its original form. Both text boxes should contain the cell ranges containing the required information.

5. Click OK to save the data selected in the above steps.

Modifying graph layout

The graph produced from the steps above is not a Gantt chart. We need to modify the bars, axes range, axes labels, and title to convert it into a Gantt chart.

Remove starting date as independent bars

1. Right-click on the chart and left-click on Format Chart Area.

   A Format Chart Area pane appears on the right side of the Excel application window.

2. Left-click on any of the blue bars to activate Format Data Series. Click on No fill for both Fill and Color.

   The blue bars become invisible in the graph.

   

3. Invert the y-axis order first by clicking on any task name on the y-axis to activate Format Axis window. Click on the bar chart icon to switch to Axis Options. Check the box labeled Categories in reverse order.

   

4. Edit the x-axis first by click on any date label to activate Format Axis. Click on the bar chart icon same as the image to open Axis Options. Under Bounds incrementally add the Minimum and subtract the Maximum values until the bars in the graph achieve a better use of plot area.

   NOTE: Excel’s date format is an integer starting at 1, and beginning from January 1, 1900. April 1, 2020’s equivalent integer date value in Excel is 43922, meaning 43921 days have passed since January 1, 1900.

   

5. Modify the number format by expanding the Number tab at the bottom of Format Axis and click on the Type dropdown. Select a format to your liking and save the file.

6. Add a chart title by clicking on the graph to trigger the Chart Element button. Edit the text in the chart title.

   You now have a completed Gantt chart.

   

Benefits of a Gantt chart

A Gantt chart provides a fast visual breakdown of tasks to do in series, which can help you in reordering and prioritizing tasks. You now have the knowledge to create a Gantt chart in Excel.