Free Microsoft Excel 2013 Quick Reference

Conditional formatting in graphs Results

Worksheet 1 has a dropdown list that uses a lookup function to retrieve
data from Worksheet 2. There is one graph in Worksheet 1 that is based
on the numbers retrieved.

The problem we have is that the numbers in Worksheet 2 can be dollars
or percentages or numbers. The graph on Worksheet 1 will always
reflect the formatting that is in Worksheet 1, of it needs
to be changed manually when the dropdown choice changes from a dollar
field to a percentage field, from number to dollar, etc.

I've tried conditional formatting, but I don't think you can use it
with Style. I've tried using the =dollar() function in a workaround,
but that isn't helpful without similar functions for percentage and

Is there a good option here? Thanks!

I am an Equipment Operator with the Navy Seabees, and the Yard Boss at Camp Moreell in Kuwait. We have CESE (Civil Engineering Support Equipment) going into IEM (Inactive Equipment Management). What this means; our CESE has to be cycled (checked for preventative maintenence issues, started up, ran...) once a week if it hasn't been used within a seven day period, but CESE going into IEM only needs to be cycled once per month.

Column E in my report reflects that as a two part eight day graph, with the formula =TODAY()-8 for the shortest end of the bar graph and the red of the graded color scale, and =TODAY() for the long end of the bar and the green of the color scale. If a piece is cycled today I enter today's date and the bar shows full and the scale is green. As that date approaches 8 days past, the bar moves to the left and the scale changes yellow to red.

I need that graph (column E) to change to a thirty day scale when I change the LOCATION (column D) to IEM, back to an eight day if I change it to DISPATCH, and to clear out completely if I change to any other location.

I've tried a few different things, but can't seem to get any of them to work. One thing I tried that I thought would work--but didn't--is to make column H (which I planned to hide) equal eight if the corresponding row in column D equals DISPATCH (using an IF formula), and to equal thirty if it's IEM. Then I wrote my graph formula a few different ways to the general effect of =TODAY()-H:H but no way that I wrote it worked. I couldn't figure out a way to just make DISPATCH equal a hidden value of eight, and IEM to equal thirty, but since the formula I did for the method I tried didn't work anyway, I wouldn't have gotten that to work either. I was able to get column H to reflect eight or thirty in accordance with column D, but I can't get the graph to cooperate.

I also tried entering the formula =IF(D:D="IEM",TODAY()-31,TODAY()-8) into the Conditional Formatting formula field for the color scale and data bar, but I get the error "You cannot use relative references in Conditional Formatting criteria for color scales, data bars, and icon sets."

What's my work around?


I have two arrows:
- Red Arrow points down to signify negative change
- Green Arrow points up to signify positive change

These arrows look exactly like the Excel 2007 conditional formatting arrows you would apply to a cell - the only difference is that I have inserted them as shapes so I can float them over a graph.

GOAL: Corresponding with the graph, if a cell shows a (+) change, then I display green arrow and hide red arrow. Vice versa for a (-) change.

Does anyone know how to hide a shape based on a value in a cell and display a shape based on a value in a cell?



I have attached a screen shot of my xls and I am trying to do the following:

1) Apply conditional formatting so that if the date in Column B is in the future, the text is grayed out. (At the moment I am doing this manually).

2) I am trying to move the red trend line automatically on the graph to represent the current month. Again, I am doing this manually.

Thanks in advance for your help - this has been bugging me for ages!


I am attempting to have an area of a graph format differently depending upon the value in a cell outside of this range. (over a certain value - one format, under that value another). The value of the cells in the range do not enter into this calculation.

Is there a way to do this? Most of the conditional formulas seem to be based on the value or contents displayed within the cells of the range.

My questions are listed in the attached workbook and also below.
I'm in the process of creating a line graph that has varied color points based on their values. As of now, I have two separately plotted lines, and I want just one. The kind of graph that I want is similar to the XY/Scatter graph from this PeltierTech article. I'm just having trouble making sense of the last steps...

I have the difficult part setup, and it's just a matter of making a few tweaks, I think. See my attached Excel workbook.

Once I get this first chart completed, I'm going to need... about 30 more identical graphs, each with their own data sets that can be changed. What's the simplest way to do this in Excel 2000?

Thanks in advance!


I was given a workbook containing a chart from a collegue .I saved the
format of this chart as a custom Chart format

The Chart is used for measuring/showing monthly sales against budget, and
also contain a MAT line.

My question is : I have I have data in my Targets Column, with the range
shown as follows =SERIES(Data!$X$2;Data!$A$75:$A$86;Data!$X$75:$X$86;2)

But I only See this target data only when I the series for my Sales Data
has the same rows.
I.e If my sales series is as follows, can only see the data in X75


and likewise if My series is

Can I see on my chart all target data in the RAnge X75:x86.

It appears all other series's are dependent on whether there is a sales
value in each month before other values ( i.e Targets) are shown

Can someone help - I've looked in VB Editor, and cannot see any code relting
to the graph, unless it is hidden.




I am trying to make a graph for work that shows the deviation of a timed process in a row on a spreadsheet. I have a row of cells from H1 to AG1. Cell G1 will have a numeric value that can be anywhere from “0” (zero) to “25” (twenty-five). Based on that number in cell G1, I want to place a colored icon in a particular cell from H1 to AG1. The ranges I am trying to focus on are that cell H1 would represent 0; I1 would represent 1; J1 – 2; K1 – 3; etc. Zero to Three would be “green”, Four to Ten would be “yellow” and, Eleven to Twenty-Five would be “red”. For example: If the number in G1 is “3”, then I would have a round green icon in cell K1; if the number were “4”, then a round yellow icon would be in cell L1; “11” would have a round red icon in cell S1, and so on.
I have toyed around with conditional formatting, but the rules are limiting and my knowledge of formulas is also limited. Any suggestions would be greatly appreciated. Hopefully this is not too confusing to visualize in written form.

After reading this post, I realized that it is not worded properly, which is probably the reason that it has many viewers and no replies. I will attempt to reword this and post it to another site. Thanks, anyway.

Hey all,

I was just wondering... is there a way to apply conditional formatting to say, a bar graph? So that is one value is lower than another, it changes a different color? Thanks in advance for any insight!


I am attempting to provide conditional formatting for a bubble graph in which the color of the bubble will bet set depending upon certain criteria. I wish to provide formatting without having to go down the VBA path.

<5 - Red
Between 5 and 10 - Yellow
>10 - Green

I need some help with some conditional formatting if you don’t mind. I have a spreadsheet (I attached it). It is a stacked bar chart. I want to make each letter a specific color for example, for all the H’s I want it to be one color, for all the HL’s I want it to be one color, for all the A’s one color. I have been trying and trying but I cant seem to get it. Would you please help me.


Hello all,

I am trying to set conditional formats to the data table portion of my chart with no success. Under the graph (Plot Area) is the data table portion of the chart where I need to set up some conditional formatting. I need to color the cells according to what their number is. The way I have been getting around this is creating a text box, color fill it, then paste it over the cell in the data table. Doing some research I found that excel is very limited when it comes to formating the data table portion of a chart. Is there any way around this like a VB script? Hope I explained it well and thanks for any help. I am using excel 2003.

See attached chart. How do I format the bar graph according to below.......

Q1 - Red if below .60, yellow if between .60 and .666, green if above .666

Q2 and Q3 - Red if below .465, yellow if between .465 and .533, green if above .533

Q4 - Red if below .722, yellow if between .722 and .778, green if above .778

Q5 - Red if below .829, yellow if between .829 and .872, green if above .872


The following Excel equation can be used to check if a worksheet called Page1 exists:


If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
How could I modify this equation to check if a graph sheet called Graph1 exists?

I cannot use code, as I need this equation for conditional formatting.

Many, many thanks



I've been lurking in this forum for a week or two. Learned a lot already just by reading questions and responses! Thanks everyone!

In fact, I just learned that graphs will ignore data points that have the #N/A error. I often have graphs that have a "Planned Value" curve that is set up at the beginning and then an "Earned Value" curve that is updated each month. To date, I have had to update the graph each month, keeping the cell empty until there is meaningful data. But it would be nice to have all the links set up and include a formula like...

[#] =IF(C28

I have a table which provides the source data for a Bar Chart style graph. I need the colour of the different Bars to change depending on the values in the table.

For example. If the value is over 1% then the bar will change to the colour Red otherwise it will stay Green.


Sorry I am new to this thread and hope I have not broken any rules yet.

I would like to have all months of 2007 in red, all months in 2008 in Blue, and all 2009 in Green, along the x axis of my graph. I do not need the source data which are columns to be a different colour just the axis labels. At the moment they are all the same colour. I have come across the example of the conditional formatting on this thread but I thought there must be an easier way in VBA? If there is not, then I will have to use this example.

Many thanks for your help.

I have a cell which contains two conditions to change its color based on the value. This cell is also a Datapoint in a Bar Graph. I would like the Bar to change color with the cell. Is this possible? How?

Hi all

Possibly dreaming the imposible dream but would like to change the colours for a data point in a graph dependent upon upon a second series for the particular data point.

Any clues or suggestions would be welcome.

Regards Bob

I have some data as below related to general activity and I need to combine it to graph when there was something going on

User Start Stop
User1 9/1/10 8am 9/1/10 8:12am
User1 9/1/10 8:45am 9/1/10 11:03am
User1 9/1/10 10am 9/1/10 11:35am
User2 9/1/10 8:47am 9/1/10 11:46am

I want the graph to show one bar that has User1 and the times that they are busy from 8-8:12 & from 8:45-1135. Then a second bar that has User2 busy from 8:47 - 11:46

The full set of data has over 21,000 lines and spans a week with about 360 "users", but I attached a sampling it if it helps

Gantt Chart doesn't work as it shows each line seperately, plotted histogram doesn't get me there, I even tried conditional formatting (although I am not sure I had this formula correct)

Please help!

No luck finding an answer? You could always try Google.