Gantt Chart With Sliding Time Scale

[Vertex42]

Although using conditional formatting to create gantt charts in Excel is not a new trick, there is a limit to how many days you can show in the gantt chart because of the maximum number of columns available in Excel (pre Excel 2007 at least). To overcome this obstacle, and still have each column represent a single day, you can make the dates depend upon an arbitrary starting date. You can then control the starting date with a slider scroll bar (from the Forms toolbar). Below is a screenshot of an example spreadsheet.



If you exclude weekends, there are almost enough columns available to show a full year at a time when printed, but the scrolling technique can let you create plans that span more than a year (although you can only view 11 months at a time). Using the stacked bar chart technique doesn't have the limitation associated with the maximum number of columns, but the point of this example is to show the technique of using the scroll bar along with conditional formatting, which may be useful in other applications as well.

Below is a link to the example spreadsheet.

Excel Gantt chart with sliding time scale

Enjoy,
Vertex42