Change column color in chart when column value is over/under goal

No use of VBA or macros expected. It is believed to be Excel chart feature.
Any ideas on how to change column colors (Red/Green) if value exceeds or 
meets the preset goal.
Eg. if goal is 4% - anything at or above 4 should show green and under 4 
should be red.
0
Charts (7)
12/10/2007 7:41:04 PM
excel.charting 18370 articles. 0 followers. Follow

2 Replies
694 Views

Similar Articles

[PageSpeed] 20

Assuming your data is in A2:A20

B2: =IF(A2>=4,A2,NA())
C2: =IF(A2<4,A2,NA())

copy B2:C2 down, add some labels to B1:C1, then chart B1:C20.

This will give you two series, one for the aboves, one for the rest.

Select each data series, right click, choose format, and set the colour as 
required.

-- 
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Excel Charts" <Excel Charts@discussions.microsoft.com> wrote in message 
news:5BFEF594-C13F-4D60-AE83-9C35DF9F6F25@microsoft.com...
> No use of VBA or macros expected. It is believed to be Excel chart 
> feature.
> Any ideas on how to change column colors (Red/Green) if value exceeds or
> meets the preset goal.
> Eg. if goal is 4% - anything at or above 4 should show green and under 4
> should be red. 


0
bob.NGs1 (1661)
12/10/2007 8:01:31 PM
Here's an illustrated explanation of a conditional chart:

http://peltiertech.com/Excel/Charts/ConditionalChart1.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Bob Phillips" <bob.NGs@somewhere.com> wrote in message 
news:%23oGg9d2OIHA.2208@TK2MSFTNGP06.phx.gbl...
> Assuming your data is in A2:A20
>
> B2: =IF(A2>=4,A2,NA())
> C2: =IF(A2<4,A2,NA())
>
> copy B2:C2 down, add some labels to B1:C1, then chart B1:C20.
>
> This will give you two series, one for the aboves, one for the rest.
>
> Select each data series, right click, choose format, and set the colour as 
> required.
>
> -- 
> ---
> HTH
>
> Bob
>
>
> (there's no email, no snail mail, but somewhere should be gmail in my 
> addy)
>
>
>
> "Excel Charts" <Excel Charts@discussions.microsoft.com> wrote in message 
> news:5BFEF594-C13F-4D60-AE83-9C35DF9F6F25@microsoft.com...
>> No use of VBA or macros expected. It is believed to be Excel chart 
>> feature.
>> Any ideas on how to change column colors (Red/Green) if value exceeds or
>> meets the preset goal.
>> Eg. if goal is 4% - anything at or above 4 should show green and under 4
>> should be red.
>
> 


0
jonxlmvpNO (4558)
12/10/2007 11:08:34 PM
Reply:

Similar Artilces:

copying workbooks with charts and changing data
As I've got to process several data of same kind, I create several copies of one workbook (1) that contains several charts When I use a copy of workbook 1 (2) with new values, the charts always refers to data in (1)...how can I do to use the values of (2)....?? How are you copying the original chart? If the chart is in the same workbook as the data, you should have no problem if you do Save As with the entire workbook, or if you make a copy of the file in Windows Explorer. When you start copying charts separately from copying their data, the charts always point to the data you don&#...

How do I change the default number format in Excel?
I want to know if it is possible to select a different default number format in Excel 2003 so that it will apply automatically when I open a new workbook. Create a new workbook. Change the normal style to what you want. format|style|select normal|click modify change the number format Save this file as book.xlt in your XLStart folder. Excel will use that template file for any new workbook. Be aware that dates are numbers, too and you'll be affecting those. I don't think I'd do this. DJMLLC wrote: > > I want to know if it is possible to select a different default num...

Setting checkbox value in subform
Hi All, I am wanting to set the value of a checkbox on each line of a subform based on a value on the main form and a value on the subform. the code I hoped would work is below. Clearly it doesn't as the checkbox I'm wanting to set is always set to True irrespective of the value of the other subform checkbox. Private Sub Form_Current() If Forms![Events]![EventTypeID].Value = 1 And Forms![Events]![Events Subform].Form![L2 Attended].Value = True Then Forms![Events]![Events Subform].Form![Attended].Value = True ElseIf Forms![Events]![EventTypeID].Value = 2 And F...

Logic Formula using colors in the formula?
Hi: I am using Office (Excel) 2007, and for some reason I thought that a change was made in this version of Excel, that allows for formulas which take into account the font color. Here is what I'm trying to accomplish: In Columns L & M I have dates that identify a beginning and end date for a class that a student has signed up for. If the student hasn't paid in full, the class dates are in bold red font. Once payment has been made, I change the font to black (and unbold it). The two issues I'd love to do with formula (to make things easier) are the following: ...

Row Height or Column Width not Showing
When I left click on the row it use to give me the row height and now for some reason I can't see that anymore. I have to right click and select the row height to see the size. I already put the CD in and ran the repair on it and that didn't work. Any ideas onwhat to try? Do not click on just the row header. Hover the cursor at the line between two row headers and you will see a two-headed arrow like a cross. Click on that. Gord Dibben MS Excel MVP On Fri, 19 Mar 2010 11:26:01 -0700, Brian <Brian@discussions.microsoft.com> wrote: >When I left click ...

Unable to change logo object in personal information dialog
I am not able to edit the exisiting saved logo object that I had previously saved in the personal information dialog, or even add a default logo to any new perrsonal information sets. Any reason why this add/edit logo in the personal information set is disabled? Which version of Pub are you using? -- JoAnn Paules MVP Microsoft [Publisher] "Mark McD" <anonymous@discussions.microsoft.com> wrote in message news:10fc701c4411c$ba3ac540$a601280a@phx.gbl... > I am not able to edit the exisiting saved logo object > that I had previously saved in the personal informat...

Cannot change a formula
I am using a worksheet that counts the number of days employees have worked. The worksheet was written by someone else who have left. I need to change the number of some rows in this formula. =COUNTIF(D$14:D$160,$V32)+COUNTIF(D$175:D$225,$V31)+M250+M238 M250 and M238 are the results of another countif formula. When I try and change the number of the rows to M251 and M 239 the formula no longer returns a value and I see the formula in the cell on the worksheet. Also the different values of the rows and columns become colored. How can I change the formula so it returns the value its su...

Can default "collate" option be changed?
Hi, By default, in every Office-application, the print-option "collate" is checked. Is there any way to permanently change this to unchecked (maybe with regedit)? Thanks. Fred Commissaris. ...

Column multiplied by a matrix
Hello Can anyone tell me how to multiply all the numbers in a column by all the numbers in a matrix? eg Column of numbers in A, multiplied by matrix of numbers in C1:E3 A B C D E 1 3 6 7 8 2 4 7 8 9 3 5 6 8 9 So I would like a formula which SUMS 3*6 + 3*7 + 3*8 + 4*7+4*8+4*9 + 5*6 + 5*8 + 5*9 thanks S Can this be done without creating a second matrix with column A spread out? =SUMPRODUCT(A1:A3*B1:D3) give 274 with your data - as required NOTE that =SUMPRODUCT(A1:A3,B1:D3) will give #V...

How populate multi-column Listbox control?
Ok, I'm back with more questions about the Listbox control. The help documention is not very helpful, so I gotta keep asking here, haha. Anyways, my listbox control is set to have 3 columns, but I just can't figure out how to put data into any column except column 1. How the heck can I put data in columns 2 or 3??? BTW, I tried the following code which I found through Google, but it doesn't work: ListBox1.AddItem "here is some text" ListBox1.List(1, 1) = "more data" ListBox1.List(1, 2) = "even more data" Please help. t...

Can I change the folder squence?
Hi All, If I create below folders: 1st folder a folder b folder c foder Then in outlook 2003, the order will be that, so can I change the squence? maybe as: b folder 1st folder c folder a folder Thanks for your help. -- Yours, Lv You can do that with folders you make. You cannot rename the standard set of Outlook folders (Inbox, Outbox, Calendar, Notes etc) Regards Judy Gleeson MVP Outlook Trainer and Consultant There are various articles about using Outlook here: www.judygleeson.com Canberra, Australia "Lv" <rebelmouse@163.com> wrote in message news:65700158-EA...

Just want to Change color of Font??
Version: 2008 I am used to using a PC and this is my first Mac- I am not used to this version of word. I would like to do simple things like change the color of the font or make the font bold or underlined but I don't know where to go for those options. There are tables and columns and other elaborate applications but I would just simply like the basics. So, if anyone could point me in the right direction that would be tremendous! View Menu > Toolbars> click on Edit. Edit Toolbar comes up gives you all kinds of formatting tools including Font color, back Ground Color , Bold, ...

Not allowing duplicates to be entered in a column
I am trying to ensure that users entering data into an Excel workshee only enter unique values in a particular column (values provide earlier in the column cannot be duplicated). Does anyone know a effective way to approach this requirement -- Message posted from http://www.ExcelForum.com Check out http://cpearson.com/excel/NoDupEntry.htm In article <blstamps.156mte@excelforum-nospam.com>, blstamps <<blstamps.156mte@excelforum-nospam.com>> wrote: > I am trying to ensure that users entering data into an Excel worksheet > only enter unique values in a particul...

FRX Error Message "The Fiscal Year Row Must be Provided for GL Columns"
Help! What does this error message mean? How do you fix it? Any advice will be helpful. Thanks. Sounds like the Fiscal Year cell in one of your Column Layouts is not filled in properly. -- Victoria Yudin GP MVP "Catbird" <pork@xsite.net> wrote in message news:1140738782.827093.164780@j33g2000cwa.googlegroups.com... > Help! > > What does this error message mean? How do you fix it? > > Any advice will be helpful. > > Thanks. > ...

Copy Color Formats Based On Column Date Values
I currently have this code, where HMLoc is the starting point for a color paste at C6. Instead of having this a constant field, I need it to search for today's date in the cell above it (C5) (The dates are given in the range C5:BO5), and then color accordingly. I have struggled with this all morning and can't get it to work the way I want it. Please help me! Option Explicit Const LegendLoc = "BH3" Const HMLoc = "C6" Const None = 0 Sub ColorHM() Dim theRow As Integer Dim theCol As Integer Dim NumX As Single Dim Color1 As Integer Dim Color2 As Integer Dim Color3 A...

Track changes #7
Where is the person who made changes to a shared workbook derived from? If I logon via one machine it gives my full name in the track changes history. If I logon from another machine, it gives my first initial and last name. I would like to know why it is different and where the identifier comes from. On the two systems, do you have different profile details? I believe TC is shown as the name from Tools, Options, User Name. R. Rainone Wrote: > Where is the person who made changes to a shared workbook derived from? > If I > logon via one machine it gives my full name in ...

Copy/Paste Values code
Is there a faster way to copy and paste one entire worksheets data into another existing worksheet? I do NOT want to rename the sheets. Code below is what I'm using now but it seems that there should be a simpler way. Thanks! Sub CopySheets() Sheets("Sheet2").Select Cells.Select Selection.Copy Sheets("Sheet1").Select Range("A1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False End Sub Give this a try... Sub...

Publisher Color Printing problem
My brochure which I have printed many times before now will only print in B/W. It shows in color but not in Priint preview or in actual print. Can anyone help? Thanks Janet Might be a corrupted driver. Go to the manufacturer's web site and see if there is an updated printer driver. If not, uninstall your printer and reinstall It is always better to completely uninstall your printer before reinstalling/installing. Bruce Sanderson's Windows Web How to clean up printer drivers http://members.shaw.ca/bsanders/CleanPrinterDrivers.htm -- Mary Sauer MSFT MVP http://office.micros...

Unbind Charts From Data?
I've got an Excel book with two tabs - one tab with a bunch of data and one tab with 6 charts based on that data. I want to export the tab with the charts into a new Excel book, but I basically want the charts to be "snapshots" - not dependant on the source data. Is there any way to do this? -- Hmm...they have the Internet on COMPUTERS now! Jon Peltier has instructions for delinking chart data: http://www.peltiertech.com/Excel/ChartsHowTo/DelinkChartData.html MDW wrote: > I've got an Excel book with two tabs - one tab with a bunch of data and one > tab wit...

Changing Sent Items mailbox.
Is there any way to save sent messages to a mailbox other than Sent Items? When using IMAP, if you need to keep copies, you can run out of space on the server. Is there a way to save these to a set of private folders? ...

Macro to add value if cell has a color :-s
I don't know if this exists, but is it possible to check if a certain cell has a color? Normally a cell is blank, but someone asked me to see if it is possible to check if a cell is colored.. Any suggestions? Thanks!!! Is this sufficient? Sub ifcolor() If Range("b2").Interior.ColorIndex > 0 Then MsgBox "yep" End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com <test@testnospam.nl> wrote in message news:obht5456aq6ur5gbqmasacqke9p2g13o7i@4ax.com... >I don't know if this exists, but is it possible to check if a cer...

Help Programming an XY scatter Chart
I have a user form that inputs data onto a spreadsheet. I have a command button that runs a macro that activates the sheet, selects the data, and creates the XY Scatter chart. My Problem is that I don't know how to set a loop to set each series' properties . (The number of series changes every time) Bellow is an example where I manually set the properties for three of the series. Any Ideas on how to create a loop to set all. Range("A:A,C:C,D:D").Select Range("D1").Activate Charts.Add ActiveChart.ChartType = xlXYScatter ActiveChart.SetSourceData Sour...

Desktop color
Using P2003, I changed my display resolution from 800x400 to a higher one, and my screen colors changed, both on my Windows desktop and my Publisher desktop. I can't remember, and couldn't find it in Help, how do I change my desktop within Pub to be something other than white? Control panel, display folder, appearance tab, advanced button. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "ladyt" <user@domain.invalid> wrote in message news:ONlxMXLjHHA.1624@TK2MSFTNGP06.phx.gbl... > Using P2003, I changed m...

Show month in different color?
I use excel at work and we have gages that are due calibration. I wanted to know how to put in a formula or something to show each gage that is due in red at the current month. That way when the next month comes the gages due that month are in red and the other months are normal. Use conditional formatting. A1 has the date, B1 has the gage parameter / reading Select B1. Go to format > conditional formatting. Select 'Formula is'. And enter in the box: =MONTH(A1)=MONTH(TODAY()) click on format button and in patterns tab select the red color. Mangesh "OneGoose" <O...

Changing numbers
I have about 5000 product code entries, but they all need changing, want to get rid of the last two digits: e.g 01234500 to 012345 Is there a way to do this for all the entries quickly -- cj2 ----------------------------------------------------------------------- cj21's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2567 View this thread: http://www.excelforum.com/showthread.php?threadid=39123 If all numbers are the same number of chaacters you could use th following formula to get rid of the 0's If 01234500 is in cell A1 then in cell B1 enter =MID...