• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Changing data point or line colors in chart

Kimber

Kimber

Member
I apologize if this has been addressed previously, but my searches (based on what I thought would work) yielded no results.


I have a chart that graphs a daily rate in percentage form. The horizontal axis is the date and the vertical axis is the percentage. The GOAL is <1%. My boss has asked that I highlight or change the color when the percentage is above 1%. I think a conditional format changing the data point to red is what I'm looking for. While I can certainly accomplish this with conditional formatting in the spreadsheet, I'm lost as to how to set it up in a chart. I've pasted my data below:

'


Date A B C D E F G H

06/04/12 2.00% 1.30% 1.80% 2.20% 0.50%

06/05/12 2.80% 0.95% 3.10% 0.99% 0.29%

06/06/12 2.60% 7.60% 1.20% 0.63% 0.39%

06/07/12 2.90% 1.70% 0.09% 0.23% 0.35%

06/08/12 1.20% 17.00% 3.10% 0.15% 0.24%

06/11/12 2.30% 3.60% 1.96% 0.02% 0.24%

06/12/12 1.12% 1.13% 1.02% 0.09% 0.24%

06/13/12 0.58% 5.20% 1.20% 1.60% 0.00% 0.42%

06/14/12 0.59% 4.70% 0.24% 1.60% 0.19% 0.54%

06/15/12 1.50% 17.00% 5.50% 1.19% 0.16% 0.39%

06/18/12 0.56% 12.20% 6.10% 0.24% 6.50% 0.09%

06/19/12 5.60% 1.50% 8.90% 2.90% 0.09% 0.99%

06/20/12 0.40% 2.30% 2.10% 6.20% 0.24% 1.90% 0.43%


'


In addition to having the data point change color, my boss has requested that the chart title change colors. FYI -- I chart each letter individually and I have placed all 8 charts on one worksheet.
 
You have to create a new series. The data that will be in the series will be from formulas.

So this is what you do.

above the data put =if(datacell is <.01, b1,#N/A) then drag across.

Now go to graph, create a new series, make it red, and make the source all the cells with the above formula. Wallah!
 
Thank you for your quick response, Montrey. Your solution works like a charm (I changed "<" to ">" to achieve the red goals) when there is data in every cell. However, there is not data for every day. These days are being plotted as zeros so both series are showing up on the graph. Can you help with that?
 
Montrey -- no worries. I must have made a mistake that I corrected, because they work great now! My boss is very pleased, thanks to your help! Thank you and Chandoo for this great resource.
 
You must log in or register to reply here.
Back
Top