Thanks:  0
Likes:  0

# Thread: Conditional Formatting with Data Bars

1. ## 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. ## 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

3. ## 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. ## 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

5. ## 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. ## Re: Conditional Formatting with Data Bars

Originally Posted by JHaugland
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

## User Tag List

#### Posting Permissions

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