Conditional charts

Is there a way to change the color of a chart bar based on the value of the
cell that generates that particular bar?  In other words, I have a chart
with 15 vertical bars and I want to change the color of one or more of the
bars if its value goes above 100% (but not all of the bars).

JWeinberg


0
jjweinb (9)
2/3/2005 5:04:23 PM
excel 39879 articles. 2 followers. Follow

4 Replies
837 Views

Similar Articles

[PageSpeed] 45

Jon Peltier has instructions for conditional charts:

    http://www.peltiertech.com/Excel/Charts/format.html#CondChart



Jeff wrote:
> Is there a way to change the color of a chart bar based on the value of the
> cell that generates that particular bar?  In other words, I have a chart
> with 15 vertical bars and I want to change the color of one or more of the
> bars if its value goes above 100% (but not all of the bars).


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
2/3/2005 5:11:48 PM
Thanks Jon.  A great help.  One other question.  How do I keep drawn lines
and text boxes on charts from disappearing when I either click on the chart
itself or off the chart?  Some lines and text I add to the chart stays
there, others don't.  I spend most of my time "fighting" with Excel to get
the chart to be what I want it to be.

JWeinberg

"Debra Dalgleish" <dsd@contexturesXSPAM.com> wrote in message
news:42025B54.10202@contexturesXSPAM.com...
> Jon Peltier has instructions for conditional charts:
>
>     http://www.peltiertech.com/Excel/Charts/format.html#CondChart
>
>
>
> Jeff wrote:
> > Is there a way to change the color of a chart bar based on the value of
the
> > cell that generates that particular bar?  In other words, I have a chart
> > with 15 vertical bars and I want to change the color of one or more of
the
> > bars if its value goes above 100% (but not all of the bars).
>
>
> -- 
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>


0
jjweinb (9)
2/3/2005 9:14:10 PM
If you want to add a drawing object to a chart, select the chart first.

When the chart is selected, it will have selection handles -- small 
black boxes (or white circles) at the corners and on the sides.

With the chart selected, you can add text boxes, lines, etc., and 
they'll remain visible.

If the chart is not selected, and you draw objects on top of it, the 
objects will be hidden when you activate the chart.

Jeff wrote:
> Thanks Jon.  A great help.  One other question.  How do I keep drawn lines
> and text boxes on charts from disappearing when I either click on the chart
> itself or off the chart?  Some lines and text I add to the chart stays
> there, others don't.  I spend most of my time "fighting" with Excel to get
> the chart to be what I want it to be.
> 
> JWeinberg
> 
> "Debra Dalgleish" <dsd@contexturesXSPAM.com> wrote in message
> news:42025B54.10202@contexturesXSPAM.com...
> 
>>Jon Peltier has instructions for conditional charts:
>>
>>    http://www.peltiertech.com/Excel/Charts/format.html#CondChart
>>
>>
>>
>>Jeff wrote:
>>
>>>Is there a way to change the color of a chart bar based on the value of
>>
> the
> 
>>>cell that generates that particular bar?  In other words, I have a chart
>>>with 15 vertical bars and I want to change the color of one or more of
>>
> the
> 
>>>bars if its value goes above 100% (but not all of the bars).
>>
>>
>>-- 
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>>
> 
> 
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
2/3/2005 9:21:09 PM
Thank you Debra.

Jeff

"Debra Dalgleish" <dsd@contexturesXSPAM.com> wrote in message
news:420295C5.9060305@contexturesXSPAM.com...
> If you want to add a drawing object to a chart, select the chart first.
>
> When the chart is selected, it will have selection handles -- small
> black boxes (or white circles) at the corners and on the sides.
>
> With the chart selected, you can add text boxes, lines, etc., and
> they'll remain visible.
>
> If the chart is not selected, and you draw objects on top of it, the
> objects will be hidden when you activate the chart.
>
> Jeff wrote:
> > Thanks Jon.  A great help.  One other question.  How do I keep drawn
lines
> > and text boxes on charts from disappearing when I either click on the
chart
> > itself or off the chart?  Some lines and text I add to the chart stays
> > there, others don't.  I spend most of my time "fighting" with Excel to
get
> > the chart to be what I want it to be.
> >
> > JWeinberg
> >
> > "Debra Dalgleish" <dsd@contexturesXSPAM.com> wrote in message
> > news:42025B54.10202@contexturesXSPAM.com...
> >
> >>Jon Peltier has instructions for conditional charts:
> >>
> >>    http://www.peltiertech.com/Excel/Charts/format.html#CondChart
> >>
> >>
> >>
> >>Jeff wrote:
> >>
> >>>Is there a way to change the color of a chart bar based on the value of
> >>
> > the
> >
> >>>cell that generates that particular bar?  In other words, I have a
chart
> >>>with 15 vertical bars and I want to change the color of one or more of
> >>
> > the
> >
> >>>bars if its value goes above 100% (but not all of the bars).
> >>
> >>
> >>-- 
> >>Debra Dalgleish
> >>Excel FAQ, Tips & Book List
> >>http://www.contextures.com/tiptech.html
> >>
> >
> >
> >
>
>
> -- 
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>


0
jjweinb (9)
2/3/2005 10:55:03 PM
Reply:

Similar Artilces:

show another column when hovering on a point in a chart
Is it possible to show another column when you hover on a point? I have 3 columns, A, B, and C for a scatter plot I have B and C as x and y coordinates of a point, and I'd like to have Excel show A when I hover on the point. thanks, Wei ...

Has Anyone ever seen a chart like this?
Looking for some help. I need to know what this column chart is called? Or how to make it? 'CLick Here to View Chart' (http://www.f150online.com/galleries/pictureview.cfm?pnum=163585&anum=11244) I can't find any option in Excel to make this chart. Maybe it was not made in Excel? Or I need a specific plugin? -- TsunamiBob ------------------------------------------------------------------------ TsunamiBob's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36202 View this thread: http://www.excelforum.com/showthread.php?threadid=559874 Hi, It...

charting newdata
Formula or VBA? I am trying to create a formula to assign a region to populate a chart. I know there is a way to use the OFFSET function to point ot a certain section of a data sheet, but what if i insert a line between the title and the data.(done programmactically)? the problem is that inserted lines do not automatically include into the data range for a chart. The data wants to be sorted from newest to oldest. Would it be easier to create a formula to assign a 'chart data area' or simply reassign the data area every time the table is updated Thanks for the input. ...

chart label reference based on the column number
In a worksheet with an embedded chart, I have a cell, S4, where I enter the number of the column I want to chart (these numbers are listed as labels in cells A2:R2). I can't figure out how to translate the number in S4 into the corresponding column letter. For example, if S4 contains "3", the chart title should be =$C$2. I think working with absolute references, R1C1, might be easier here, but somehow what I've tried, didn't work (the entire spreadsheet is based on relative addresses (A1). z.entropic in a cell (Z100) put =index(A2:G2,1,S4,1) Change G2 to whatev...

DEADLINE... PLEASE HELP! Stacked Bar chart?
I'm not even sure how to ask the question so here's what I have... 2003 2004 2005 Actual/Goal Actual/Goal Actual/Goal Me 1009/1061 591/866 658/897 Comp. A 966/1012 633/811 624/808 Comp. B 699/744 450/593 480/607 Comp. C 957/1005 642/821 665/838 I wanto to show a bar for each competitor, for each year, so there will be 4 bars for each year. Each bar showing Actual performance & Performance Goal...

Core Chart
Need a picture of an opened sphere showing core and layers. Can you help? After managing to set up OE-QuoteFix on his new PC, Ed reads a message from Isabel <ialduen@catholiccharitiesden.com>... > Need a picture of an opened sphere showing core and layers. a) The core of what? A black hole? An apple? A nuclear power plant? b) This is a Microsoft Publisher group. Your question is hardly relavent to the topic of discussion really. Why did you choose this group? -- Ed Bennett - MVP Microsoft Publisher http://www.mvps.org/the_nerd/ Before reading this message, view the disclaimer...

z-order of various lines and bars in an Excel 2007 chart
What is the procedure for changing the z-order of various lines and bars in an Excel 2007 chart, such as drop lines, high-low lines, up/ down bars, and error bars? I observed that when I add drop lines to an Excel 2007 chart, they appear in front of the underlying data -- in my case, the underlying data is displayed as a plotted area. I want the plotted area to be fully visible with no lines over it. I would like to send the drop lines to the back and keep the plotted area in the front, but there is no option to do this. So, is there a procedure for adjusting the z-order of drop...

Hierarchy Pyramid Chart
Is there a way (Excel 02), to create a chart that is in the shape of a pyramid/triange (i.e. like a pie graph, but in a triangle instead)??? It doesn't seem to be an option, but maybe some type of addin??? Thanks in advance Nevermind - I realized that the pyramid is actually a diagram. So I inserted a Diagram - Organizational Chart instead and it's just what I needed!!! "Keep It Simple Stupid" wrote: > Is there a way (Excel 02), to create a chart that is in the shape of a > pyramid/triange (i.e. like a pie graph, but in a triangle instead)??? > It doesn't ...

Background changes conditionally
Hi all, I have a spreadsheet that shows the floor plan layout of my call centre. On each desk, the desk number and identity are displayed. I would like to colour the background for those pcs that are on the same subnet. I have a sheet called data - it contains Table Number, Desk Number, PC ID, IP Address. How could I get it to change the background colour if the pc is on subnet 162, and set a different colour for those on subnet 167 any ideas? -- PeterG ------------------------------------------------------------------------ PeterG's Profile: http://www.excelforum.com/member.p...

How do I lock a chart so it will not update?
That's the question. I have my data in Excel and the chart in Excel but not all the data cells are used. Everytimg I open the chart it wants to update and I want it to stay the same. Any ideas on how to lock the chart? Hi Just a few ideas: You could lock the cells that are shown in the chart. Or you could copy the cells and paste as values (assuming formulas were used that update when other cells change). -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "Locking a Chart" wrote: > That's the question. I have my data in Excel and the chart in ...

How can I reset the default pie chart label "Other" to "Equity"?
I show Cash, Equities, and Fixed Income in the large pie to the left. The equity slice is further split into large, medium, and small. The problem is that the Equity slice gets relabeled as "Other". When I manuallly change it back to Equity, I lose the ability to link to any new data...so I can't use the pie chart as a template that would allow me to put new data in the table and have the pie chart adjust automaticially. How can I reset the default pie chart label to "Equity"? or is there another solution. Instead of making pie charts (which are notor...

SUMIF with two conditions ?
:cool: OK I have tried just about everything and can not find the righ combination for seems like a simple problem: IN A1 IF N79:N83 >=1 AND IF N79:N83 <=7 THEN SUM K79:N8 -- Calvi ----------------------------------------------------------------------- Calvin's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=590 View this thread: http://www.excelforum.com/showthread.php?threadid=26341 Hi Calvin one way =SUMPRODUCT(--(N79:N83>=1),--(N79:N83<=7),N79:N83) Cheers JulieD "Calvin" <Calvin.1d4dfz@excelforum-nospam.com> wrote in message...

Chart Wizard #5
Does excel 2007 have a chart wizard? If so, how do I access it? The chart wizard no longer exists in Excel 2007, RIP. You create a chart using the various chart buttons on the Insert tab, then struggle through the three Chart Tools contextual tabs looking for the controls to set up the chart. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Liberty" <Liberty @discussions.microsoft.com> wrote in message news:7C1186EF-4C15-4E3D-A642-D0EA334C1192@microsoft.com... > Does excel...

Easy dynamic chart.
Creating a dynamic chart is difficult, there should be a dynamic chart wizard that will create it with you. Also if my serie value is a name range, it would be nice to just right the Name range directly not with the complex formula. ='xyz.xlsx'!name range but just name range. xyz and name range is a fictive value. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the...

Custumize Chart Tittle Description
How can I make the Chart Tittle to be equal to a specific cell located in one sheet. For example, I have a description on the cell "D10" located in the sheetname "Black" . I want this description to be the Chart Tittle because is a number that will change in the daily basic. I have been using this formula "=Black!D10"; however, is not working. Thanks in advance. Maperalia Click on the title, type =, and then click on D10 on Black. Or click on the title, and type =Black!$D$10 -- Rob Powered by Creative Laziness "Maperalia" wrote: > How can I...

My charts do not show when moved as a separate chart tab...
In excel 2007 my charts are displayed when inserted in a a sheet but they do not show when moved as separate tabs ...

conditional formmating
In conditional formatting I would like a certain row of dates to turn a colour when a report is sue in 60 days and they a different colour when there due in 30 days. Also included in the formula I need the colour to remain blank if there are no dates in the column In A9 I have the date 1/Nov/2009 In B10 I have =A9+15 and this is copied across to L9 So I have a row of dates in 15 day steps I selected A9:L9 and used this formula in the Conditional Formatting dialog =A9>TODAY()+60 (colour red) Then I added =A9>TODAY()+30 (color blue) So I want to know what dates are 60 day...

Cell Shading > 3 conditions
I have a spreadsheet that contains up to 6 different text strings. I'd like to automatically change the text color or the cell shading depending on the specific condition. For example, if the cell contains "A", make the cell shading red. If it contains "B", change the font color to red. Excel's conditional formatting limits me to 3 conditions. How can I accomplish this objective? I'm using Office 98 on a Mac, and worse, I'm not VBA smart. Thanks. rick -- rabsparks ------------------------------------------------------------------------ rabsparks's ...

Conditional ComboBox
I have a combo box that allows users to choose a staffID, however if a certain item is selected in a previous combo box then my list of staffID's needs to be limited to a certain few. Currently this is what I have. If 27 < Service.Value < 40 Then Me.STAFFID.DropDown="24","159","176","321","326","337","383","124","390", "409" End If The certain item is the service. So if the service.value is between the range then the staffID's should be limited to only certain ID's otherwise...

Creating a chart with varying data value ranges
I have a chart that I want to show home price data with. The chart will have data that has very significantly varying data values. For example, one line graph will be 50-100, one will be 400,000 to 500,000, one will be 90% to 110%. I would like to show these line graphs on one chart. How to I make a chart with differing ranges on the x (vertical) axis? Take a bit of seutp work, but the end result here is impressive: http://peltiertech.com/Excel/ChartsHowTo/PanelUnevenScales.html BTW, the vertical axis is the y-axis. -- Best Regards, Luke M "Anthony Blackburn&quo...

Copy & Paste Chart
I can Copy & Paste an Excel Chart from one Workbook to another. However, another User, when copying an Excel chart, and Pasting into another Workbook, gets the Chart essentially as a "Snapshot" with "EMBED". Ideas??? TIA - Bob What's your question? You don't say what you're looking for. What does the formula bar say in parentheses after "EMBED"? You can copy the chart as a picture: hold Shift while selecting the Edit menu, choose Copy Picture, and choose the On Screen and Picture options. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutor...

Bubble Charting
Hi There Hope you all can help, i've looked everywhere but have found it very difficult to achieve what I want. I'm not familiar with VB but i know how to copy and paste into modules! Here is the data (column by column): Name --- X --- Y --- Bubble Size --- RAG Status OK, here are my requirements... - Each bubble to be labeled with column name - RAG status to conditionally format the colour of the bubble. Thanks in advance! Hi, You can do the colour coding by using multiple series and some simple formula to create conditional charts, this will give you more detail on the subj...

Is it possible to create Graphs and Charts?
Our company is interested in making nice graphs and charts to use as maketing tools for some data we have collected. Is Publisher capable of creating graphs and charts. Excel doesn't give us the professional quality we are trying to achieve. Thanks!! Our corporate office uses Corel Draw and the graphs look amazing. If Excel can create graphs like that then I need to know how. "Ed Bennett" wrote: > Erin LRX wrote: > > Our company is interested in making nice graphs and charts to use as maketing > > tools for some data we have collected. Is Publisher capable ...

charts and cells
I have a worksheet with a name in cell A1. There are 18 charts on the worksheet. I'd like to have the chart headings read the name in cell A1 and display that as part of the chart heading on each of the 18 charts. Then, I'd also like to create multiple copies of this worksheet, change the name in cell A1 on each of the copies, and have the new names from cells A1 of each worksheet appear on the 18 charts on that page. I have a less than satisfactory workaround when there's only 1 page. It involves creating a picture of cell A1 and overlaying it where the chart headings are. ...

Conditional Format
Hi All, I require a Conditional Format Formula to Highlight every 3rd Cell in Red that is Offset one column to the Right of the Dynamic Range called POSITION - starting from Row19. Also, is it possible for the Formula to return the number 3 in the highlighted Red Cells? Thanks Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200508/1 Assuming that Column I is the column next to your dynamic range, try the following... Conditional Formatting: a) Select cell I19 b) Format > Conditional Formatting > Formula Is c) Enter the following formula:...