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.
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
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
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).
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).
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.
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.
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.
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.
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
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.