If you find any post useful then, please do share with others. Thanks!

Popular Posts

Contact

Email me

Need help and that also free? I like to learn this way, in case of any question or for a small task, please feel free to email me with details and example data, if required.

Tuesday, August 25, 2015

Gantt Chart in MS Excel 2010

Gantt chart
As per Wikipedia, the Gantt chart is a type of bar chart, adapted by Karol Adamiecki in 1896, and independently by Henry Gantt in the 1910s, that illustrates a project schedule. Gantt charts illustrate the start and finish dates of the terminal elements and summary elements of a project.
We can build this chart in Microsoft Excel also. Please see the step by step illustration.
1-      First we should have a list of data i.e. task table to build the chart.

Task Start Date Duration End Date
Brain Storming 5/2 4 5/6
Listing Ideas 5/6 4 5/9
Selecting Idea 5/9 3 5/11
Estimating 5/11 2 5/12
Executing 5/11 2 5/13
Managing 5/12 2 5/14
Reporting 5/13 2 5/12
Meeting 5/13 2 5/12

2-      Make a Bar chart
From the top menu bar, select Insert then Bar and the 2D Stacked Bar. This will inert a blank chart.


3-       Have your mouse in blank Excel chart and click there and then left click -> Select Data. The source window will appear.
Click on legend entries (click add). This will open the series window.
In the series name box, select the cell reference of Start date column header. Like, if our data is in Column A to D, this would be B1.
In the series values box, select the data range of start date column i.e. b2:b9. Click ok and start dates data is in the chart
In same way add the durations.



Now, we will change the dates on the left side to list of tasks.
For this under Horizontal (Category) Axis labels, click on edit.
With mouse highlight the names of tasks and not include the name of column itself i.e A2:A9. Click ok.



Now chart looks like below.



Now Format the Gantt chart

Our task names are in reverse order to correct this, select task names and right click then
Format Axis. And then in Axis options, check the box of categories in reverse order and close.





For more space in chart, select the start date and duration legend/label with mouse and delete them.
Now hide the blue portions of the chart.
Click on any blue bar, this will select all, right click and choose Format Data series.

Ø  Click on Fill then select no fill
Ø  Now click on Border color and select No Line.
We are almost done. We just need to remove the empty extra area from the start.
Go to the first start date cell in the list, in our case it is A2. Now right click and select Format cell. In the Category section, select General. And note the number appearing, in our case it is 42126. Cancel as we are not changing anything, just need this number to use somewhere else.


In the Gantt chart select the dates appearing at top. Click there to select all and then right click and click on Format Axis.

In the Axis options change the minimum bound to the number, we noted.
Change the major unit to 2 and then press Close.

To remove most of the spaces and make the chart look nicer. Click on the top first bar and right click, select Format Data series.
Change the Gap width to 10%

We are finished, our Gantt chart look like this.

2 comments:

  1. The above post helps in creating bar charts for data analysis but i want Data Bars – Bar chart within data

    ReplyDelete
  2. how make profit and loss projection in microsoft excel

    Microsoft Excel tutorial

    Visit this channel Microsoft tips

    ReplyDelete