August 16, 2007 Automate your manual Gantt chart in Excel 
The unstoppable Dave Seah recently posted an Excel file he uses to manually create Gantt charts. It’s a nice layout, but it’s manual: You still need to fill in the schedule and apply the cell background colors to define durations on the timeline.
About a year ago I put together a spreadsheet in Excel that does a lot of that work for you. I’d definitely call it a 0.5 version of what it could be. It’s pretty basic, and relies on some hard-coded values, but it also puts to use Excel’s date functions to dynamically define durations that skip weekends and holidays, color-code durations by label ("Agency" or “Client” in this version), and do all this just by entering a start date and the number of business days to spend on a task.
A few things to note:
- This may require one of the Analysis Toolpaks or just a recent version of Excel. I seem to remember adding one of these (Add-Ins under the Tools menu) when I put this together, but my current installation of Excel doesn’t have any checked. Perhaps I found a workaround.
- This is, of course, unsupported. Download it, play with it, improve it, put your logo on it and call it your own. I’m just sharing some knowledge and a starting point here.
- Column A is hidden. In Column A I stored the holidays we were given off where I worked so that, in addition to weekend dates defined by Excel, the chart would know additional days to exclude from the schedule.
- The Start Date in Task 1 is a hard-coded value. The End Date is determined by the number of days allocated, and subsequent Start Dates are determined sequentially following that task. This probably isn’t always the case, but the values can be overridden as you need to.
- To use this functionality, do a Fill Down (Ctrl+D) on fields B:G for the number of Tasks you need, editing the Task, Days, and Owner fields for each new row.
- Dates across the timeline are linear and the timeline can be expanded by doing a Fill Right (Ctrl+R). The first date should be entered manually.
- The duration stripes use conditional formatting. (Conditional Formatting under the Format menu) They’re in an if...else sequence that first blocks off weekends and holidays in gray, then applies a background color for Agency tasks, and then a background color for Client tasks. It does this based on a formula that checks if the date above is within the range of the task (Start Date/End Date, already calculated or manually entered).
- The values that correspond to “Agency” and “Client” are in the conditional formatting formulas. You can add additional values there.
- The conditional formatting grid can be expanded out and down by highlighting cells and doing a Fill Down or Fill Right (Ctrl+D or Ctrl+R).
That should be enough to get a somewhat savvy Excel user started. This won’t work magic on its own. It does have enough of the thinking done, though, that it can be expanded and styled to cut down on the number of steps to put together a project schedule.
