Gantt Chart - Planned vs Actual

HI ,

I am using MS office 2016 and need an excel template of Gantt chart which shows progress of defined activities in planned vs actual form. I have tried to explore on google but not found as desired.

Sample sheet attached for reference.


Nitesh

One way of doing this is to use this formula in cell H6:

=IF(AND($E6<=H$5,$F6>H$5+7),"x","")

Copy across to AJ6 and then down to AJ19. You will see that x has been pasted into the cells to represent the activity bands. You can then use Conditional Formatting to change to the appropriate colour, i.e. select H6:AJ6 then click on Conditional Formatting | New Rule | Use a formula... , then put this formula in the dialogue box:

=H6="x"

Click on the Format button | Fill tab, and select blue, then click on the Font tab and in the Color drop-down select blue again (for foreground colour), then OK your way out.

Repeat this for the selected range H7:AJ7 and use this formula for the CF rule:

=H7="x"

This time, choose green for the foreground and background colours.

Then you can select the range H6:AJ7, click on Copy, then move the cursor in turn to H8, H10 etc. and paste those rules into the alternate rows.

Hope this helps.

Pete

Forgot to add the file - attached.

Pete

Thanks a lot Pete , it works fine

Glad to hear that, and thanks for marking the thread as Solved.

You might also like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete