Free Microsoft Excel 2013 Quick Reference

- Conditional Formatting with Style
- Relative References in Conditional Formatting (bars & scales)
- Conditional Formatting - Objects
- Conditional Formatting and Moving a Trend Line
- Conditional Formatting in Excel 2007 - Format of one area determined by value outside
- Help with Conditionally formatted line graphs
- Conditional formatting in charts
- Need help with conditional formatting a spreadsheet
- Conditional Formatting to Charts/Graphs?
- Conditional Format A Bubble Chart
- Conditional Formatting a data series in a graph.
- Conditional formatting chart data table
- Conditional format each series in a bar chart
- Checking if a graph sheet exists
- Number Format for #N/A
- Conditional Formatting For Series On Graph
- Different Colour On X Axis Graph
- Formatting Bar Graph Datapoint Color
- Charts: Conditional formatting within a data series
- Graph multiple Start Stop time in one bar graph

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 course...so 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

number.

Is there a good option here? Thanks!

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?

Thanks!!!

John

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!

David

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.

--------

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

=SERIES(Data!$W$2;Data!$A$75:$A$86;Data!$W$75:$W$75;1)

and likewise if My series is

=SERIES(Data!$W$2;Data!$A$75:$A$86;Data!$W$75:$W$86;1)

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.

Thanks

Rgds

Daniel

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.

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!

Woody

<5 - Red

Between 5 and 10 - Yellow

>10 - Green

Thanks!

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.

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:

VB:How could I modify this equation to check if a graph sheet called Graph1 exists?(ISERROR(INDIRECT("'Page1'!A1")))If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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

Many, many thanks

Adam

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

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

Cheers

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.

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

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!