Conditional Formatting with Data Bars

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
    6
    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
    18,955
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 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
    6
    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
    18,955
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 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 11:18 AM.
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  5. #5
    New Member
    Join Date
    Mar 2016
    Posts
    6
    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
    18,955
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 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 02:38 PM.
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

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
  •  

 

 
DMCA.com