How to Make a Gantt Chart in Excel 2010

In this tutorial we will explain How to Make a Gantt chart in Excel 2010. This type of chart is used in project management for assessing the possibility of attaining different project’s mile stones within due period. As the project is normally divided into tasks (small projects), Gantt chart (G C) is used to visually display the position of each task in terms of time.

We have the following data for a project. How to make stack G C? Follow the following steps.

Picture 1

How to Make a Gantt Chart in Excel 2010

This project has 12 total numbers of activities.  We want to make our Gantt chart a dynamic chart. We don’t want to make it a static chart which will require to be updated every day. Because our main goal is that our chart updates automatically with passage of days. It will save us lot of time and peace of mind.

To make our chart dynamic we need to use Excel Today function. Let’s move to use it in cell D2 and then D3 till D13 for all 12 activities.  In column D we want to determine the number of days completed for particular Activity . We can determine it with help of following formula

= TODAY () – B2

After entering formula =TODAY ()-B2 we get following

Result = 18-Jan

As we are interested in number format instead of date format. So we apply the following procedure.

Click inside cell D2 and select General number from number format drop down list

Picture 2

Data Flaws removal

Now our Days Completed Data is number format shown in second Screen shot in above picture.

Today () function produced some values which are greater than days to complete, for example in cell D2 value 18 which is greater than days to complete corresponding value 15 in cell C2. Similarly in cell D9 it produces -3. Days completed value should be equal or less than Days to complete. Or it should be zero

Days Completed < or = Days to Complete Or Days Completed =0

To eliminate the above flaws from data we need If and Nested If function in Days Completed column.

Enter the following If and Nested If Function in Cell D2

=IF (TODAY ()-B2>C2, C2, IF (TODAY ()-B2<0, 0, TODAY ()-B2))

Apply the above function to Cell D3… D13 for all 12 activities.

Result for Days  Completed Column after above formula

Activity 1 = 15

Activity 2 = 12

Activity 3 = 12

Activity 4 = 9

Activity 5 = 6

Activity 6 = 3

Activity 7 = 0

Activity 8 = 0

Activity 9 = 0

Activity 10 = 0

Activity 11 = 0

Activity 12 = 0

We have calculated Days Remaining which is difference between Days to complete (Cell C2) and Days completed (Cell D2).

Type following formula in cell E2

=C2- D2

Then apply this formula to other E3.. E13

Now our data is in proper shape and ready for use in G C.

How to Insert  G Chart?

First click inside the empty space on Excel worksheet

On the Ribbon’s Inert tab click Bar chart and from Bar drop-down select 3-D bar ( option 2nd).

Picture 3

Stacked bar Gantt chart

Right click inside the chart space and choose Select Data form list.

Clear default data range in box chart data range

Click on Add button, Edit Series dialogue box opens, asking for Series name, here our first series name is Start Date (clicking inside Cell B1 to capture name Start Date for our first series and for Series Value select range B2 to B13 ( As shown in Picture 5).

Picture 4

Data Series Selection

Picture 5

Editing Series

Picture 6

After entering all series of data

Above is the final shape of our chart after entering all data series. This chart has some obvious problems.

First problem is with activities order, they are in reverse order.

Second problem is date range for our data. Our project starts date is 15 October while chart displaying 3-Sep and its end date is 17 Nov while on chart it is 1st Jan. so there is wide gap and we have to narrow down this gap.

Let’s move to solution of first problem to bring activities in proper order.

Click on activities inside chart,so that it becomes editable then right click on mouse and click on Format Axis from list. Format Axis dialogue box opens, in Axis Options check Categories in reverse order check box and close it.

Format Axis

Now your G C activities are in order of activitiy1 to activity12 from top down.

Now remove date gap from our chart.

First select the blue part of stack in our chart which representing start of date.

Removing Date Gap

Right click on it and click on Format data Series in list. Format Data Series dialogue box opens. Click on Fill option and then under Fill select option No fill and close the dialogue box.

Format date gap

Now replace 3-Sep with 15- Oct which is our project start date. How to do this? Follow the following steps.

Step1: first copy and paste 15-oct in a cell. Then change its format from date to general number format. How to change format is discussed in the beginning of this article. After change of format we get value 41562 for 15-Oct. similarly for end date of our project we get 41595 for 17 Nov.

Now click on date range inside chart, so it becomes editable then right click on Format Axis.

Date Range in Editable Mode

Format Axis dialogue opens. Under Axis Options entering minimum (41562) and maximum (41595) value by checking Fixed options. Close the format dialogue box. And our final chart will look like this

Final G Chart

Our final G chart is free from activity reverser order. And the start date and end date is same as our project date.

If you consider this article about How to Make a Gantt Chart in Excel 2010 informative then please share it and post comment in comment section of this post.

Article written by

This blog is administered by Farzand A, he is also the main content contributor.This guy is in relationship with Ms Excel for last 10 years. During this period of romance he has developed many excel based applications for accounting, statistic and other data intense fields. This blog will serve as guide to Excel users of all stages. You will find more information here as compared to 50 0r $100 MS Excel book. Prime advantage of this blog is 24 hours availability for answering your questions. So enjoy free surfing ! G00GLE+

Please comment with your real name using good manners.

Leave a Reply


Get every new post delivered to your Inbox

Join other followers