Create Conditional Formatting Gantt

[mycon73]

Hi,

Seeking a template that will utilize 2 or 3 color scheme for a Gantt. I think I want to use conditional functions that will shade a series of cells or rows & have 2 or 3 color schemes rather than a chart.

I would like to have Planned vs. Actual & Milestone Symbol.

Who can suggest or provide some good templates?

Thanks for the assistance.

[mycon73]

Hi,

Forgot to mention,

I know there are many templates out there, but ones seen are too complex to use. Seeking something relatively simple & may have multiple milestones during on the timeline.

[newdoverman]

This might give you some ideas. It is a Gantt chart produced by worksheet formulae and conditional formatting. Milestones are entered with the data and are represented on the chart with their own colour.

[mycon73]

Hi Newoderman,

Thanks for the example. It almost works for me. I'm looking to do this on a single bar, showing planned vs. actual & for longer duration deliverables, show several milestones within a single row.

Is this doable?

I attached a simplistic template that I will later take its functions & put into my master template.

Within the attached template, I would like column G modified somehow to show 2 bar shades - Planned vs. Actual. I would also like to show a milestone symbol & the progress bar (column H) applied across date durations - Preferably weeks - Not shown yet

With this or any other template, is this doable?

Thanks

[newdoverman]

Something happened to my reply....disappeared into the "ether"!

I haven't found anything that will do what you want. The closest thing is the Data Bars that are in column H. I have an extensive collection of Conditional Formatting examples and there is nothing remotely like what you want.

[mycon73]

Hi Group,

Requesting assistance from form to make a 2or 3 shaded bar / row based from:
* Start / Finish Dates
* Milestone Dates - Like symbol for this
* Actual (Status)

Basically making a Gantt chart but with a different intent.

Not certain if & how to do through multiple functions or some type of conditional formatting (function).

Please advise.

Thanks

[newdoverman]

I don't know what values you are wanting to use and where you want the results. An example of manually formatted data would certainly help.

I am just guessing but I think your choices would be data bars, or 2 or 3 colour scales depending upon what the end result should look like.

The present formatting of the worksheet makes it rather difficult to work with...and to read (at least for me).

[mycon73]

Hi newdoverman,

Do you have a way to take your example & combine the functions so the shadding will be in 1 row instead of 2? That's primarily what I'm seeking. Would also be nice to have a Milestone symbol if I indicate that the task is a milestone.

Thanks

[newdoverman]

Unfortunately, I don't know of a way to make that work.

[mycon73]

Hi Group,

Attached is an example of a template that I may mimic for my purpose. What I would really like to be able to see & control is in Column G. Right now it's showing percent difference & making a single shaded bar. Later, I would like to modify to show Budget (Planned) vs. Actual & have Column G show 2 shaded bar in a single row - 1 for each respectively.

I would also like to add in a column (not yet added in) & put a milestone status with a Yes / No option. If it's a "Yes" milestone, then have a symbol shown on the 2-shaded bar.


Also desired, but not critical to have is some type of duration reference on the bar, such as Start & Finish.

I'm trying to avoid the standard Gantt type chart because my project will be large & those tend not to show well on multi-sheet WBS or project plans.

Thanks

[mycon73]

Hi Group,

I found a better example of the look that I'm kind of seeking. It has the same intent but it appears that the different shades & symbols are simply drawn in rather than controlled by functions or conditional formatting.

Perhaps, with this more direct picture, someone can provide a functional Gantt example or template.


Thanks

[mycon73]

Hi Group,

Seeking assistance. Please refer to my last 2 attachments.

Thanks

[newdoverman]

Your first example in message 10 are data bars which are in Conditional Formatting, Format all Cells Based on Their Values. The values that the bars are based on are actually in the cell where the data bars are and the data bars cover the values.

The second example is a manually created Gantt Chart.

This is as close as I can come to the Gantt Chart not knowing what data is actually going to start the chart as there isn't and start date. The start dates can be entered and the start and end dates for the chart can also be entered.

[mycon73]

Hi newdoverman & Group

This is almost what I'm seeking in terms of functionality & looks.

In your example, you have the yellow bar which could represent the planned duration. Now, with the same row, it would be good to see the actual or anothe shade or symbol being represented - Perhaps, through a % status.

As for your diamond symbol, I would like to control through another column, such as a Milestone column & have a Yes / No option. If Yes, then show symbol. If no, then do not show symbol.

Is this doable?

Thanks

[newdoverman]

Here is a Gantt chart developed by Khaled Elshaer (BIM centre) that you might be able to adapt to your needs. It shows % complete (some of which is a manual calculation) and has an overall start and completion vs expected start and completion with a % for all the constituent parts.

Excel doesn't have built in Gantt Charts so the creation of one is strictly up to the imagination of the creator. Unfortunately, the formatting is either an "ON" or "OFF" situation under most situations allowing only 1 choice.

The chart that I created is like this but not so fancy.

[mycon73]

Hi newdoverman,

Not too bad of a template. I had something like this in the past. However, trying to avoid this type of template because it takes up a lot of extra space or lines.

Now, if we get these shaded bars on top of each other & apply a milestone symbol where desired, I could have a good template. Any suggestions group?