Why this is probably the best spreadsheet Gantt Chart Template you’ll find this year [Project Management with OpenOffice & Excel]

Amazon affiliate link: Introduction to Project Management

Hubby hit me with a request for a Gantt chart in Excel this week, which is OK. I can colour the background of cells with the best of them!

But then I decided to turn this into a project where you get Excel and OpenOffice templates to use, free!

As I was putting it together, however, I was starting to wonder if there was a way that I could automate the creation of the bars based on Conditional Formatting. I’m sure I could have Google’d it and come up with a spreadsheet, however this way I ended up with:

1. An Excel Spreadsheet that works on versions of Excel that don’t support the NetworkDays() Function (before 2007 I believe)
2. An Excel Spreadsheet that uses the NetworkDays() Function
3. And an OpenOffice version

Even though this site has historically been all about OpenOffice, I figured I may as well share the love on the Excel versions as well, as DH’s company uses Excel, and it was the foundation for the OpenOffice version. I am using Openoffice 3.2.0.

Now I’m not going to go through how I put this together. What I will cover in this outline is as follows:

1. Date formatting – you can format this spreadsheet either by the days of the week (a hidden row on this spreadsheet), or by weeks (as displayed above).
2. I’ll show you how to hide and show rows
3. Conditional Formatting – how and where it is used on this spreadsheet to create the bars of the Gantt chart
4. How the number of days in the project are calculated – given that we are only counting working days), and how I’ve used 2 different methods to do this.

Presentation – Days of the Week or By Week?

Originally I set this up so every day of the week was dated across the top of the Gantt Chart. Dear Husband (DH) said that this was too cluttered so we decided to use the ‘Week Commencing’ title and settle on the Monday date as the date that defines the week.

You can see that I’ve hidden a row in the spreadsheet – Row 3 in the example below – which has all the dates from Monday to Friday (however the columns are very narrow to allow for more weeks to fit on the page). I didn’t delete this row even though I chose to display on a weekly basis as I use it for calculating the format for the bars – see the section below ‘Putting the Bars In’.

Start Date

The first date in the chart is best calculated as the first date of the project dates, so in cell E3, I’ve used the MIN() function to calculate the earliest date out of all the dates in column C.

Note: I would ideally make this always a Monday. The spreadsheet would go wonky if it wasn’t. Something to consider for a future revision – i.e. calculate the first Monday before the earliest date, if the earliest date is not on a Monday.

Calculating Days – Method 1: NetworkDays()

There are a number of day calculations that are necessary to show the start and end dates of tasks, of activities, and to calculate the number of working days it takes to complete a task or activity.

Just to be clear, I’m using the terms activities and tasks, meaning that an Activity (like Perth (WA) in the figure below) is made up of a subset of tasks.

Here is a view of one set of tasks and activities, with the forumlae shown:

From the above, we can see:

• Start dates should be before end dates (logical – but check this as it can throw your spreadsheet if you accidentally put in the wrong year, for example)
• The min() of all the task start dates should indicate the start date for the whole activity
• The max() of all the task end dates should indicate the end date for the whole activity
• NetworkDays() can be used to calculate the number of working days between the start date and end date. Note that NetworkDays() is not available in all versions of OpenOffice or Excel.

Note: Another future improvement I intend to add is to include a list of holidays in a range outside of the Gantt chart, which NetworkDays can refer to ensure that it doesn’t include these days in the calculation. For this spreadsheet, DH wasn’t concerned with this, so it wasn’t something I included.

Calculating Days – Method 2: SumIf()

This will make more sense after you read the section below, so I’ll continue it after…

Putting the Bars in – Conditional Formatting

Now I probably could have done this without the 1 and zero calculations below, however because the NetworkDays() formula didn’t work for DH’s version of Excel, this turned out to be quite useful.

For each cell I took the individual date value in (hidden) Row 3, and I compared it to the start date and finished date. If it satisfied both of these tests, then it would return a one – meaning that it fell on or between the date periods. If it failed one or both of these tests, it would return a zero and mean that it fell outside of the date period. See the figure below for an example.

So all that was left for me to do was to test each date in row 3 against the task start and end dates, and return a 1 or 0 to the cell. Then I could tell the spreadsheet to format the cell depending on the value returned. This resulted in bars. If you are not sure about conditional formatting, check out my post on conditional formatting.

I added a layer of complexity for this by having different background colouring for activities (green) and tasks (grey). I cut and paste this manually, but maybe another iteration of this spreadsheet will have something where this is worked out automatically.

Calculating Days – Method 2: SumIf() (continued)

So in an earlier version of Excel or OpenOffice where NetworkDays() doesn’t work, the above 1 and 0 method for formatting allowed a really easy way for me to calculate the number of days duration for a project (Column B). You can see this in the figure below:

And that’s pretty much all there is to it!

I tidied it up with a little formatting (made some of the rows quite narrow), but other than that, it’s your basic gantt chart. I have protected the cells so that I don’t accidentally edit them, but you can remove the cell protection by:

• In OpenOffice, select Tools –> Protect Document –> Sheet (no password)
• In Excel, select Tools –> Protection – Unprotect Sheet (no password)

Please suggest any improvements in the comments!

Download

>> View the download…

To download the file, you will need to be a registered member of this site, due to spammers and the like stealing my bandwidth. It’s free, and other than the occasional email (less than once a month), you won’t be harrassed by me! If you are not already subscribed, please subscribe to this site using this form.