Last week I showed you how to create a Gantt chart in Excel by tricking a regular bar chart into thinking it could be a Gantt.
At the time I mentioned that another way to create Gantt charts in Excel is with Conditional Formatting, but I said 'that was a topic for another day'.
When I said ‘that was a topic for another day’, I wasn’t meaning the very next post, but I got so many emails and comments about it I thought I’d better deliver sooner rather than later.
So here it is, a Gantt Chart template using Conditional Formatting.
Conditionally Formatted Gantt Chart
Download the Gantt chart template (Excel workbook) here. Note: this is an .xlsx file; please ensure your browser doesn’t save it as a .zip file.
I’ve colour coded the cells as follows:
Where a task hasn’t yet started it’s colour coded light green to show Estimated Days only.
Because we don’t work on weekends I’ve used the WORKDAY.INTL function to calculate the Completion Date in column F.
If you’ve got Excel 2007 or Excel 2003, you can use the WORKDAY function instead.
This ensures Excel doesn’t calculate a Saturday or Sunday as the completion date.
The syntax for WORKDAY.INTL is:
WORKDAY.INTL(start_date, days, [weekend], [holidays])
My formula in column F is:
Which in English reads:
If B5 is blank then return a blank (this is because if I haven’t put a start date in for a task I don’t want it returning some crazy date in column F), otherwise take the start date in B5 less 1 day (so it doesn’t think the start date is already over), plus the completed days + remaining days, and by the way, we have Saturday and Sunday off so be sure to skip them.
Note: I’ve left the [holidays] argument blank but you can also reference a list of holidays you’d like Excel to skip.
Now for the Conditional Formatting….
Conditional Formatting for Gantt Charts
As you can see there are only 3 rules which are applied to the date columns G:Z.
Remember, Conditional Formatting formulas must evaluate to TRUE or FALSE. A TRUE outcome applies the format, whereas FALSE doesn’t.
When you enter your rule you enter it from the perspective of the first cell in your range. In my case this is G5. This is important when considering when to absolute or relative a reference, or part thereof.
Translated into English my formulas read:
Completed Days: Check that the Start Date (in B5) is less than or equal to the first date (in G4), AND take the start date (in B5) less 1 + the Completed days (in D5) and check it’s greater than or equal to the first date (in G4). If both of these arguments are TRUE colour the cell in dark green.
Estimated Days: Check that the Start Date (in B5) is less than or equal to the first date (in G4), AND take the start date (in B5) less 1 + the Estimated days (in E5) and check it’s greater than or equal to the first date (in G4), AND check that the Estimated number of days is = to the Remaining days. If all of these arguments are TRUE colour the cell in light green.
Remaining Days: Check that the Start Date (in B5) is less than or equal to the first date (in G4), AND take the start date (in B5) less 1 + the Estimated days (in E5) and check it’s greater than or equal to the first date (in G4). If both of these arguments are TRUE colour the cell in medium green.
Conditional Formatting Formulas Tips
Testing: I like to test my formulas in the workbook before creating my conditional formats. This allows me to quickly see if it's going to evaluate correctly by displaying the TRUE/FALSE outcomes.
So how about when you download the workbook you paste the formulas from the Rules Manager into cell G5 and then copy it to the remaining cells in the chart (one at a time) to see how each one evaluates.
Order: Another point to mention is that the order of the rules being applied is important. Have a play around with them and see what happens.
Perspective: As I said above, remember when you enter your new rule you enter it from the perspective of the first cell in your range when making references absolute.
You can read more on how to work with formulas in Conditional Formatting here.
So go ahead, download the workbook and modify it for your needs.
I’m sure you can make improvements since mine is really just for the purpose of teaching you how you can use Conditional Formats to create Gantt charts in Excel.
If you liked this please show me by using the buttons below to share it with your friends and colleagues on Google +1, LinkedIn, Twitter, Facebook (or all of them 😉 ), or leave a comment below and tell me what you think. I read and reply to them all.