Use conditional formatting to create a Gantt Chart

 Sep 15, 2014

Efficient Project Management involves displaying the project plan in a visual format. The tool used to do this is called the Gantt Chart. The Gantt Chart is a list of tasks drawn across a time scale using shaded cells. It shows the start date and the end date of each task. It helps the Project Manager to identify dependencies between project tasks. It also helps the user to develop a project plan schedule with check points.

A Gantt Chart is a very useful tool in monitoring and controlling the project progress which is a crucial task in Project Management. With the help of this tip, you can easily create a project plan that includes a Gantt Chart in Microsoft Excel if you do not use any other project management software.

Follow the steps listed below in order to create a Gantt Chart:

Use conditional formatting to create a Gantt Chart

Use conditional formatting to create a Gantt Chart

 

Use conditional formatting to create a Gantt Chart

  1. Select cell A1, type Step.
  2. Select cell B1, type Start Date.
  3. Select cell C1, type End Date.
  4. Select cell D1, type Duration.
  5. Select cells D2:D11 and type the formula =C2-B2 to calculate the difference between tasks the End Date and the Start Date.
  6. Press CTRL +Enter to enter the formula to all selected cells.
  7. Select cell E1 and type the formula =B2.
  8. Select cells F1:AB1 and type the formula =E1+1 to enter the calendar days.
  9. Press CTRL +Enter to enter the formula to all selected cells.
  10. Select cells E2:AB11.
  11. On the Home tab, Styles group, click Conditional Formatting and then New Rule.
  12. Select Use a formula to determine which cell to format option.
  13. Type the formula =AND(E$1>=$B2, E$1<=$C2).

    Use conditional formatting to create a Gantt Chart

  14. Click the Format button.
  15. Click the Fill tab.
  16. Select a blue colour.
  17. Click OK, then click OK again.

Your Gantt Chart should look like the figure below.

Use conditional formatting to create a Gantt Chart

RELATED COURSES

Excel 2016

Microsoft Project 2016

PRINCE2 Foundation

PRINCE2 Agile

PRINCE2 Practitioner

BSB51415 – Diploma of Project Management

How do your Excel skills stack up?   

Test Now  

 

How do your Excel skills stack up?   

Test Now  

About the Author:

Magdalena Todor  

With over 20 years experience as a facilitator and university lecturer, Magda is one of our most senior and experienced trainers. With previous practical on the job experience as a project manager she embodies a brilliant balance of training experience and business knowledge. With every event she delivers, Magda takes a wholehearted approach to ensure every course is impactful, relevant and a genuinely positive learning experience for all.

Read full bio
top