Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Conditional Formatting with Data Bars

  1. #1
    New Member
    Join Date
    Mar 2016
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Conditional Formatting with Data Bars

    Is there a way to change the data bar color for a condition? I have six conditions and would like each one to represent a different color.

    Example, 1 = Blue 2 = Green 3 = Yellow, etc.

  2. #2
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,569
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Conditional Formatting with Data Bars

    Hi
    Welcome to the board

    A simple solution is to divide your original series into 6 new series.
    - each series has the the value of the original series or 0, depending on the condition
    - each series has the colour corresponding to the condition value
    - the series are stacked bar
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  3. #3
    New Member
    Join Date
    Mar 2016
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting with Data Bars

    Can you elaborate. I'm not very savvy w/excel (well not as savvy as most on this forum!).

    Basically I am trying to create a training matrix and with six different skill levels but I want the color of the bar for each skill level to be different for visual purposes.

  4. #4
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,569
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Conditional Formatting with Data Bars

    Hi

    I post a simple example for 3 conditions.


    In this example my original values are in D3:D8, can have values 1,2 and 3. I want the columns with the same value to have the same colour.

    First thing, I created the 3 series in columns G:I with very simple formulas

    In G3: =IF(D3=1,D3,0)
    In H3: =IF(D3=2,D3,0)
    In I3: =IF(D3=3,D3,0)

    Copy down

    this way I separated the values each to 1 specific series.

    I now create a stacked column chart and add 3 series with values:

    Series 1: G3:G8
    Series 2: H3:H8
    Series 3: I3:I8

    and use for the horizontal axis: C3:C8

    Because each of the series has a different colour and each values only appears in 1 series you'll see the columns in the chart with the same colour when they have the same value.

    Does this help?



     ABCDEFGHIJ
    1          
    2  Horiz axisOriginal values  Series 1Series 2Series 3 
    3  A1  100 
    4  B3  003 
    5  C2  020 
    6  D2  020 
    7  E3  003 
    8  F1  100 
    9          
    [Book1]Sheet2
    Last edited by pgc01; Jun 8th, 2016 at 10:18 AM.
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  5. #5
    New Member
    Join Date
    Mar 2016
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting with Data Bars

    Yes, greatly. Would it be possible to send you my workbook to look at? I cant quite wrap my arms around what you are trying to explain.

  6. #6
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,569
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Conditional Formatting with Data Bars

    Quote Originally Posted by JHaugland View Post
    Yes, greatly. Would it be possible to send you my workbook to look at? I cant quite wrap my arms around what you are trying to explain.
    Sorry, this is a very busy time for me and that will not be possible.

    Anyway, I found a tutorial by Jon Peltier for chart conditional formatting.

    This has detailed instructions about how to set up the data, as well as the images of the resulting charts.

    I hope this will help.

    Conditional Formatting of Excel Charts - Peltier Tech Blog
    Last edited by pgc01; Jun 9th, 2016 at 01:38 PM.
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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