+ Reply to Thread
Results 1 to 8 of 8

Gantt Chart - Different colors on the bars

  1. #1
    Registered User
    Join Date
    09-27-2009
    Location
    Dublin
    MS-Off Ver
    MS 365
    Posts
    97

    Gantt Chart - Different colors on the bars

    Hi,

    I've a gantt chart and i'd like to be able to change the color of the bars based on the project they belong to.

    Lets say i've 30 tasks on the chart. and Project 1 has 5 tasks / project 2 has 18 tasks and project 3 has 7 task.

    Can i have something like

    Project 1 = blue
    Project 2 = green
    Project 3 = orange

    These would all still appear in the one/single gantt chart, but would clearly show which project they belong to.

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,531

    Re: Gantt Chart - Different colors on the bars

    It's hard to say without seeing how you have set up your chart. In general, each series is a different color, but I am guessing you have a single series. You can manually make an individual bar any color you want by right-clicking it and going to Format. Here are two examples of a charts that use different colors for different projects, but you would probably have to do your setup from scratch.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    09-27-2009
    Location
    Dublin
    MS-Off Ver
    MS 365
    Posts
    97

    Re: Gantt Chart - Different colors on the bars

    Thanks for the reply.

    I'm only building the chart so don't mind starting over to get what I'm looking for.

    How do i set up a series and assign it a different color on the chart?

  4. #4
    Registered User
    Join Date
    09-27-2009
    Location
    Dublin
    MS-Off Ver
    MS 365
    Posts
    97

    Re: Gantt Chart - Different colors on the bars

    Ok i've attached a spread sheet of what i've been working on.

    I've 4 projects on this sheet and there will be more added.

    I'd like the bars to be the same color as the project color on the left ( with the name web site 1 etc )

    I changed the color of the second set of bars by hand just to set things out.

    Can you help ?
    Attached Files Attached Files

  5. #5
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Gantt Chart - Different colors on the bars

    I would follow something like this (https://peltiertech.com/conditional-...-excel-charts/ ) where each "project"/"color" is added as its own data series. Note in the tutorial that most of the work is in the spreadsheet to setup each data series range. In your sheet, I:

    1) Widen column A, unmerge the cells, rotate the text horizontal, then copy each "group name" into each cell (so copy A5 "web site 1" into A6 to A13, same for other projects). This will be important later when I set up the IF() function for each data series so that function will know which project goes with each row. This step may not be necessary, but it will make that future IF() function a lot easier.
    2) In V4 I enter "web site 1", in W4 I enter "web site 2", and so on for as many projects as you ever expect to have.
    3) In V5, I enter my IF() function =IF($A5=V$4,$E4,NA()). Note the mix of relative and absolute references to make copying easy. Copy and paste down and across as far as needed.
    4) In the chart, move the existing data series so it refers to column V. Then add columns W, X, etc as additional data series.
    5) Format each data series to have the desired color, and any other formats as needed.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,598

    Re: Gantt Chart - Different colors on the bars

    I put one together in Post #11 of this thread:

    https://www.excelforum.com/excel-pro...ck-events.html

    which has a similar layout to yours. This uses two colours through conditional formatting, although the colours are dependent on the values in column F.

    Although you say that you have up to 30 projects, I don't think it would be necessary to have 30 colours - you could get by with just 2, so that you have alternate colours for each project (which occupy several lines for the different stages, anyway).

    Hope this helps.

    Pete

  7. #7
    Registered User
    Join Date
    09-27-2009
    Location
    Dublin
    MS-Off Ver
    MS 365
    Posts
    97

    Re: Gantt Chart - Different colors on the bars

    Quote Originally Posted by Pete_UK View Post
    I put one together in Post #11 of this thread:

    https://www.excelforum.com/excel-pro...ck-events.html

    which has a similar layout to yours. This uses two colours through conditional formatting, although the colours are dependent on the values in column F.

    Although you say that you have up to 30 projects, I don't think it would be necessary to have 30 colours - you could get by with just 2, so that you have alternate colours for each project (which occupy several lines for the different stages, anyway).

    Hope this helps.

    Pete
    Thanks.

    I don't have 30 projects I've 3 or 4 but each project has a few tasks so i was saying i've 30 tasks.

    I like your spread sheet and probably going to use this setup.. but I've a question.

    How are you changing the colour from yellow to grey.

    I know it's looking for the numbers in F - if it's 0 = grey and anything over = yellow

    You say you are using conditional formatting.. but how is it working in this case ?

    If I set a rule to change colour on items over 20 for example it's looking for numbers.. and there are no numbers in the boxes where the colour is being applied ( calendar area )

    I wanted to set up a few different ones given each project a number in Column F so the conditional formatting will look for the number and know this number = this colour

    Example

    1= red
    2= blue
    3= yellow
    4= pink

    So if there is a date range for anything marked with a number 1 it will show the date range in red and same for others.

  8. #8
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,598

    Re: Gantt Chart - Different colors on the bars

    You can see how the calendar part works by looking at the formula in cell I7, i.e.:

    =IF(AND(I$5>=$D7,I$5<=$E7),"x","")

    This formula is copied to all the cells in the calendar part, and basically it is checking to see if the date for that cell (in I5) falls between the start and end dates (in D and E), and if so it returns an "x", otherwise leaves it blank. If I didn't do anything else to that area, then you would just see a row of x's covering the date span on each row, so the "trick" is to convert these x's into something else, and that is where the conditional formatting comes in.

    You can see the details of the CF settings if you click on Conditional Formatting | Manage rules, and you will see a number of them displayed - select one and then click on Edit Rule to see the details (and to change any of them). This is the formula that governs the bulk of the yellow cells for I7 (which applies to all other cells on that row):

    =AND(I7<>"",$F7>0)

    So, if that cell is not empty AND if F7 is greater than zero, then set the background AND foreground colour of the cell to yellow (effectively making the "x" invisible), and also show a top and bottom border to make it stand out. There are two other special cases for the same colour - if the date is the start date (or end date) then show a left-hand (or right-hand) border as well. Thus there are 3 conditions for each colour used. Grey is used to indicate if F7 is equal to zero (this was originally for a conference organisor, who wanted to be able to see quite easily where the numbers for the conference had not been confirmed).

    The CF rules for row 7 apply to cells only on that row, but if you look at subsequent rows you will see that the Applies To range is for rows 8 to 20.

    So, if you want to change colours you will need to do this 3 times, and you must make the foreground and background (Fill) colours the same. If you want to change the threshold number, then you will need to amend the formula used (again, three times).

    Feel free to post back if you have any other questions.

    Hope this helps.

    Pete

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Changing the color of the bars in a Gantt chart based on a value
    By jeannelacombe in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 08-07-2018, 04:28 PM
  2. [SOLVED] Does conditional format apply to bars of a Gantt chart ?
    By adriano.r.marques in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 07-01-2015, 03:27 PM
  3. Replies: 0
    Last Post: 06-21-2013, 10:57 AM
  4. Dynamic colors for excel gantt chart
    By sine.grujica in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-17-2012, 04:12 AM
  5. Replies: 5
    Last Post: 05-20-2011, 10:00 AM
  6. Shading weekends in Gantt chart bars
    By neobavesten in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-01-2010, 07:34 AM
  7. Bars in Gantt chart showing values of certain cells?
    By darelooney in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-20-2008, 10:10 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1