Today during my first class we had a lecture on Project Management. Our teacher brought up the design and usage of a Gantt chart. I have previously had my experience with Gantt charts during my secondary school so it was nothing particularly new. What caught my attention though was that our teacher primarily talked about how Microsoft Project could generate Gantt charts, elimininating the somewhat tedious process of creating one. The problem is that Microsoft Project, though seemingly a great tool for project management, cost an exhausting $1,160! (And that’s a lot for someone living on student loans).

So I thought I’d share my experience with creating Gantt charts in Microsoft Excel instead. As a student of Uppsala University we are provided with Excel, along with the full suite of Microsoft Office, at no charge, so this tutorial should become handy.

Disclaimer: Bear in mind that this is just the way I did it back in secondary school. I’m sure that there are several, far more extensive and user-friendly variants of creating a Gantt chart in Excel but this should serve as some sort of general procedure as how it functions.

Gantt chart in Excel Tutorial

What we should get when we are finished is an Excel file displaying something similar to this:

1. Preparing columns

Start by creating a new Excel workbook. We’ll be needing seven columns:

• Task
• Start Date
• Estimated Time
• Completion Percentage
• End Date
• Done
• Left

The last three columns are not user-inputted but instead filled out by Excel using formulas described later in this post. As so, I like to put a few extra empty column between the first four and the last three, just in case I want extra parameters (e.g. comments).

Now format the cells below each column (Right-click -> Format Cells) according to these values:

Note that cell formatting actually is optional as Excel converts the different format to a single unit during the formulas. Estimated Time is entered in hours, but if you instead want to input them in days you will need to divide the hours by 24.

Explaination of the columns

The columns all have different purposes. ‘Task‘, ‘Start Date‘, ‘Estimated Time‘ and ‘% Complete‘ are all self-explanatory. As for the generated columns, they can be somewhat unclear.

End Date displays the computed estimated end date by adding your Estimated Time to your Start Date for a given task.

Done is a value that represents the part of a task that has been completed. The value is presented in days.

Conversely, Left is a value that represents the part of task that is still to be done, also given in days.

The Gantt diagram uses these values to fill out the bars correctly, as you will see later.

2. Formulas

2.1 Today’s Date

Before we dive into the main cell formulas we need to set up the ‘Todays Date‘ block on the Gantt chart. This step is optional. The today-date block is just another task, but instead of having a set Start Date the start date is instead set to the current time. This is done by selecting the cell below Start Date and enter in the formula bar:

=NOW()

Leave ‘Estimated Time‘ blank. For the column ‘% Complete‘ set it to ‘100%‘. For the column ‘End Date‘ enter in the formula bar:

=NOW()+0,125

Where 0,125 is the width of the today-date bar, where 1.0 is a bar covering an entire day.

The column ‘Left‘ is dependant on the ‘Start Date‘ column and the ‘End Date‘ column. Assuming your ‘Start Date‘ column is B,  ‘End Date‘ column is H and your current row is 2, enter in the formula bar:

=H2−B2

Set the column ‘Left‘ to ‘0‘ and we’re done with the today-date row. It should look something like the one below.

Lets move on to the main cell formulas.

2.2 Main formulas

The first four column as user-inputted as previously mentioned. The last three column have special formulas that has to be entered. Assuming your columns look like the ones above, enter these formulas:

End Date

=B3+(C3/24)

Done

=(J3−(C3/24)

Left

=(C3/24)−((C3/24)*D3)

 With some test value you should see something similar to this:

3. Diagram

Now for the best part, the actual Gantt chart!

I like to keep my actual Gantt chart in an Excel workbook on a separate page, but you can put it anywhere. For the sake of this tutorial I have it on the same page.

Goto Insert -> Bar -> Stacked Bar. You should now have a diagram on your spreadsheet. Delete the labels on the right side as we won’t be needing them.

Select the horizontal axis labels (the numbers below the chart), Right Click -> Format Axis and give it the following settings:

Select the vertical axis and give it the following settings:

Time to give the chart the correct data values. Right click anywhere on the chart and select Select Data. The Series (left-side portion) are going to contain three entries: Start Date, Done and Left. To do that, first remove any entries already there from the initial creation of the chart. Then press Add and a dialog should pop up. From here, set the Series name to whatever you want (preferably Start Date, Done and Left for each of the entries). Set the Series values to the data range of the values below the column we’re entering from, i.e.:

Do this for the three entries/columns Start Date, Done and Left.

For the Categories axis (right-hand side in Select Data dialog) press Edit and select all task labels like so:

Now we’re almost done! Select the Start Date bar (in this case the blue one), Right click -> Format Data Series, goto Fill and set No Fill. Select the other bars and set their fills to a fill of your choice (where the red bar in this picture is the incomplete part and the green bar the complete part), and that should be it! You now have a functioning Gantt chart made in Excel. Try playing around with the settings to get a look and feel you like.

One big note however, as I said earlier in this post, is that this Gantt chart is not that great. One thing is its lack of flexibility. For each new Gantt chart you’re doing, you need to:

• Set the fixed date values for the horizontal axis (above: 41530 and 41532) according to your project timeline.
• Adjust the data range of Start Date, Done, Left, and the Categories axis (task labels).

For small project that are concise and have a set timeline and activities then this is a trivial task, but for larger, more dynamic project this particular Gantt chart is perhaps not the optimal choice. Nevertheless it’s a great tool as is, and with a few settings and more tasks it can look quite pretty. For example, by adding a title (Goto the toolbar, Layout -> Chart Title ->Above Chart), adding glossy bars (Design -> choose from the list the re-edit the fill values by preference) changing the date-display format and some overall theme changes we can end up something with this:

So that’s it, thanks for reading!

About Kenth Ljung

2012 Programming

View all posts by Kenth Ljung →