{"id":2821,"date":"2015-06-22T14:27:49","date_gmt":"2015-06-22T14:27:49","guid":{"rendered":"http:\/\/www.microsofttraining.net\/b\/?p=2821"},"modified":"2026-01-02T05:40:15","modified_gmt":"2026-01-02T05:40:15","slug":"how-to-build-an-automatic-gantt-chart-in-excel","status":"publish","type":"post","link":"https:\/\/www.stl-training.co.uk\/b\/how-to-build-an-automatic-gantt-chart-in-excel\/","title":{"rendered":"How to Build an Automatic Gantt Chart in Excel"},"content":{"rendered":"

An easy to update\u00a0Gantt chart in\u00a0Excel<\/h2>\n

Building a Gantt chart in Excel is pretty easy. But what if you needed to update any of the tasks? This would usually mean lot’s of time consuming manual editing of cells. \u00a0But there is an easier way….<\/p>\n

Step in Excel and the ever flexible conditional formatting function! Yes by deploying some neat conditional formatting tricks you can produce a presentable usable Gantt chart in Excel. You can also learn conditional formatting by attending\u00a0Microsoft Excel London training course<\/a>.<\/p>\n

Recap: A Gantt chart is made up of task bars, one for each of the tasks required to complete the project in hand. The task bars typically have start and finish dates and therefore have different lengths on duration.<\/p>\n

By using Excel’s Conditional Formatting feature\u00a0we can create our project task bars for our Gantt chart. We will be able to different colour task bars conveniently managed by conditional formatting. An example is shown below on how cell colours can be changed;\u00a0sales figures green if they exceed a target figure of 1200.<\/p>\n

\"Target\"<\/a><\/p>\n

Let’s create a Gantt chart in Excel<\/h3>\n

By using conditional formatting we can easily create the following.<\/p>\n

\"Gantt<\/a><\/p>\n

Creating the conditional formatting rule<\/strong><\/h3>\n

Step 1.<\/strong> Start by selecting the range of cells where bars are to be displayed. In the above example range F2:T11.<\/p>\n

Step 2.<\/strong> Select the Home tab then choose Conditional Formatting.<\/p>\n

Step 3.<\/strong> Pick New Rule then Choose a formula to determine which cells to format.
\nIn the empty box box (format values if formula is true) enter the formula:<\/p>\n

=AND($D2<=F$1,$E2>=F$1)<\/p>\n

This formula returns True if a task\u00a0Start Date is before or equal to the chart date (entered in row 1)\u00a0AND the Finish Date is after or equal to the chart date.<\/p>\n

AND<\/strong> means both conditions within the brackets must apply.\u00a0The $<\/strong> sign before a letter means keep that column fixed when applying the rule to the range of cells.\u00a0Similarly, the $ before a number means fix that row when applying the rule.<\/p>\n

Step 4.<\/strong> Now click the Format button and choose a fill colour then OK.<\/p>\n

In the example all cells that obey the rule are coloured blue.<\/p>\n

Adding more rules<\/strong><\/h4>\n

Additional rules can be added for example if certain tasks are designated critical by typing CT in the C column then colour the bars pink.<\/p>\n

\"Project<\/p>\n

To do this create a new rule as before but include a third condition where the type is CT.<\/p>\n

=AND($D2<=F$1,$E2>=F$1,$C2=”CT”)<\/p>\n

Click Format and choose pink as the fill colour.<\/p>\n

Tips<\/strong><\/h4>\n

For consistency you can choose specific fill colour by selecting Fill, More Colours, Custom then entering\u00a0255,153,255 for Red, Green and Blue.<\/p>\n

Adding a white top and bottom border as part of the Formatting rule can help to separate the bars when touching.<\/p>\n

To make changes to colours or to formulas you must highlight the exact range containing the conditional formatting then select Conditional Formatting, Manage Rules. To help do this quickly select\u00a0Home, Find & Select, Conditional \u00a0Formatting.<\/p>\n

Conditional formats can be copied using the Format Painter<\/a>.<\/p>\n

Additional Resources<\/h4>\n

Present your data in a Gantt chart in Excel<\/a><\/p>\n

 <\/p>\n","protected":false},"excerpt":{"rendered":"

An easy to update\u00a0Gantt chart in\u00a0Excel Building a Gantt chart in Excel is pretty easy. But what if you needed to update any of the tasks? This would usually mean lot’s of time consuming manual editing of cells. \u00a0But there is an easier way…. Step in Excel and the ever flexible conditional formatting function! Yes […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"singular-low-link.php","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[4,6],"tags":[89,293],"class_list":["post-2821","post","type-post","status-publish","format-standard","hentry","category-excel-training","category-hints-tips","tag-change-colours-in-gannt-chart-in-excel","tag-how-to-create-project-task-bars-in-excel"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/2821","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/comments?post=2821"}],"version-history":[{"count":4,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/2821\/revisions"}],"predecessor-version":[{"id":7827,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/2821\/revisions\/7827"}],"wp:attachment":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/media?parent=2821"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/categories?post=2821"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/tags?post=2821"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}