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
639 Views

Similar Articles

[PageSpeed] 52

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:

Creating a Difficult Chart
Data Set 1: Q1 with a %, Q2 with a %, Q3 with a % and Q4 with a %. This data needs to be shown as the left-side numbers on a bar chart. Beside these %ages, there needs to be a line running thru the bar at this point. No shading of the bar should occur. Data Set 2: Q1 with a %, Q2 with a %, Q3 with a % and Q4 with a %. These percentage numbers are different numbers than Data Set 1. These numbers need to be shown on the right-side, AND the percent "fill" needs to be shaded. There should be a different shade for each Q. How do I do this? Any help will be most appreciate...

Today Conditional Format
I have a column which calculated a date based on 3 working days from a date input into a diff column. What I want to do I have the calculated date appear bold if that date has passed based on the current date. For example if I input 01/08/11 the next column calculates 04/08/11 (3 working days). I want the caluclated date to appear bold when I open the spreadsheet on the 05/08/11 ro show the calculated date has passed. I can use conditional formatting but can't get the syntax right Regards Andy Win XP Pro Office 2010 Andy Roberts presented the following explanation : > I ha...

Perserving secondary axis in pivot chart
I have a Pivot Chart with two series of data, one of which is plotted on the secondary y axis. Every time I refresh the chart, it defaults back and I lose my secondary axis. I then have to manually change this every time. Hi, This is a know issue. XL2000: Changing a PivotChart Removes Series Formatting http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q215904 Cheers Andy David wrote: > I have a Pivot Chart with two series of data, one of which is plotted on the > secondary y axis. Every time I refresh the chart, it defaults back and I > lose my secondary axis. I then...

Two Condition Vlookup?
I am currently trying to create a function that searches through an array for two exact column values which allows me to retrieve a third column value. For example: Type of Fruit Date Packaged Amount of Fruit Apples June 100 Oranges June 50 Apples July 75 Grapes July 50 In this example, I would be looking to retrieve the Amount of Fruit (Apples) that was packaged in July. I would be putt...

Query condition by date range
I'm trying to create a query to base a report from. I need it to return records within a date range that needs to be specified, as in specifying the beginning date and having the current date as the end date. A field is included in the query that has listed dates in the format mm/dd/yyyy. How would I write the criteria? I have part of it ready - [Please enter starting date:] . I know that's how you get the little question window. On Tue, 4 Dec 2007 14:51:00 -0800, silva wrote: > I'm trying to create a query to base a report from. I need it to return > records within a...

Chart
I'm trying to generate a chart with tons per hour on the y-axis and efficiency on the y-axis as well (opposite the to the first), and the date on the x-axis. So, I guess what I want to do is have the ability to have two y-axis. any help will be appreciated! First generate the chart with 2 variables (ton/hr and efficiency) with the date on the X-axis, then once the chart is built, click the one that you want on the right y-axis, right click and select Format Data Series, from there go to the tab Axis and select Secondary axis. Now you have independent scale for each variable, once...

Need help with conditional formula (Excel 97)
Can anyone give me the correct syntax for the following formula: if A1 is blank, and B1 is more than zero, then display B1, otherwise display nothing (blank cell) Thank you JD =IF(AND(A1="",B1>0),B1,"") Success! -- met vriendelijke groetjes "Jake D" <JakeD@djhtend.com> schreef in bericht news:crkto4lv30u7uujv27ek8okr0us5t9oj10@4ax.com... > Can anyone give me the correct syntax for the following formula: > > if A1 is blank, and B1 is more than zero, then display B1, otherwise > display nothing (blank cell) > > Thank you > > ...

Floating Bar CHarts
How can I create a flaoting bar chart with values which vary in range? On Tue, 25 Sep 2007, in microsoft.public.excel.charting, Rahul <Rahul@discussions.microsoft.com> said: >How can I create a flaoting bar chart with values which vary in range? http://www.google.com/search?q=Excel+floating+bar-chart -- Del Cotter NB Personal replies to this post will send email to del@branta.demon.co.uk, which goes to a spam folder-- please send your email to del3 instead. ...

Conditional formatting if condition of an if statement is true and a string value is displayed
Dear Experts: I got a nested 'IF'-Formula in an excel cell that returns nothing if the condition is false. I would like to conditionally format this cell with grey shading if the condition is true and a string value is displayed. How can this be achieved using excel conditional formating functionality ? Help is much appreciated. Thank you very much in advance. Regards, Andreas Let's say your doing this conditional formatting in cell K17. In conditional formatting (xl2003) choose the FormulaIs: option and enter: =$K$17<>"" choose your formatt...

Conditional Field in Queries
Is there a way to make a field in a query conditional so that it only displays the value if the value is equal to something specifically set? Thanks. Nick Nick.Korynski@gmail.com wrote: > Is there a way to make a field in a query conditional so that it only > displays the value if the value is equal to something specifically > set? Thanks. > > Nick Explain "the value is equal to something specifically set". -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com ...

How do i use Conditional formatting for 3+ conditions ?
I have a text box in a report which highlights in colour current members in a list of past and present member names. I have six types of current members that I want to highlight, but with conditional formatting I can only use 3 conditions in the members [Type] field. I have tried [Type]="Full member" Or "Life member" but the "Or" doesn't work ! Is there a way to do this ? thanks .. Roger CF is limited to 3 contitions, but you can use OR in those expressions. If you set Condition1 to Expression, you can use: ([Type] = "Full member") Or (...

Help with Conditional formatting with Dates
Hi there, I have a spreadsheet in which I have to monitor various dates which are in different columns (i.e. (1) Date Tender Released, (2) Date Company Selected, (3) Date Draft Contract Forwarded, (4) Date Contract Signed etc...). Would if be possible to Conditional Format and have diffirent ROWs highlighted for each individual Contracts (I have 300 running contracts running) every time a date is filled in these different columns: Initial Step (1) = Yellow (Call for Tender released) Step (2) = Blue (Company Selected) Step (3) = Brown (Contract under Process) Step (4) = Green (Contract Si...

chart scale
I have a chart that shows pay per hour and pay in thousands. On the chart the pay per hour bars do not display, and you can only see the year salary in thousands. The range of the chart is 0-140,00. I need to expand the 0-30 range without throwing the rest of the chart off whack. I can not figure it out! Help! You could plot one series on the secondary axes. Double click the series, and on the Axis tab, choose Secondary. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Gia" <Gia@discussions.microsoft.com> wro...

Conditional null value
Wondering if anyone can suggest a way to simplify the following code, which I’m using in the OnFormat event of a report. If Not IsNull(DLookup("[ICD2]", "EntryICDs", "[EntryNum] = " & Me! [PtAcct#])) Then Me![Field192] = Replace(DLookup("[ICD2]", "EntryICDs", "[EntryNum] = " & Me![PtAcct#]), ".", " ") End If If Not IsNull(DLookup("[ICD3]", "EntryICDs", "[EntryNum] = " & Me! [PtAcct#])) Then Me![Field194] = Replace(DLookup("[ICD...

Help
Forgive me if "Conditional Sums" is not the accurate word for what I am trying to accomplish... I'm at a loss for what to call this situation. What I have is a spreadsheet totaling attendance figures for classes taught by two people (Dave and Cary). Column A lists their name, and next to it in Column B is the number of people who attended that class. What I need is a formula that will look at the sheet and report in a given cell the total for attendance for each person. I can work basic formulas like SUM, but that will give the total for the entire column, when what I n...

how do i do a comparison bar chart
I have to do two bar charts, one is a comparison of male and female IT skills the second is a comparison of male and female IT skills training wanted. I have done bar charts before, but this really has me stumped Hi, Show us a sample of your data and how its laid out. Cheers, Shane Devenshire "Moira D" wrote: > I have to do two bar charts, one is a comparison of male and female IT skills > the second is a comparison of male and female IT skills training wanted. I > have done bar charts before, but this really has me stumped ...

Area chart, help needed.
Can anyone help me with the attached chart? The first chart is how it currently looks, if you look at the small black section you can see it tapers off. however i would like it to look like the second chart and end abruptly in a straight line. Im using a combination of Line and Area charts. the data is in a 3 rows. Budget: cumulative Won: this only shows data in the past. (also cumulative) Forecast: only shows data in the future. (also cumulative) i.e month 1 | 2 | 3 | 4 Budget: 1,000|2,000|3,000|4,000 Won: 800|1,600| 0| 0 Forecast: 0| ...

XML Receipt and Conditions
I wanted to make a small adjustment to the receipt when a certain condition is met. If a particular word (eg. family) is typed into the comment on an item I wanted something slightly different to happen to the receipt I've just about got it, just stuck on the condition tag <IF> <CONDITION>len(Entry.Comment)</CONDITION> <THEN> I can get it using len but this makes it happen for any word, is there anyway I can make it so that it is more specific ie Entry.Comment contains '%family%' try <IF> <CONDITION>Entry.Comment like '%family%'&...

conditionally restricting values in excel
Hello, I want to restrict values in a particular column based on the value in other column. For eg. if column A has value int then column B should be restricted to values 1,2,3,4,5 if column A has value bool then column B should be restricted to values true and false. if column A has value string I dont want any validation to be done and the list box should not appear. Is this possible..... How do I achieve this ? Can I use macros to do this? How? Thanks Hi as a starting point: http://www.contextures.com/xlDataVal02.html >-----Original Message----- >Hello, >I want to restrict ...

How to chart a profit & loss statement/Profitability analysis statement in Excel Charts
Hello All I want to chart a profit & loss statement / profitability analysis statement in Excel Chart. Essentially I have the numbers for the following in one of the Excel worksheet: ========================================= Gross Sales A sales & Admin Expenses B COGS (Cost of Goods Sold) C ========================================= Gross Profit D = A-B-C ========================================= Selling Expenses E General & Admin Expenses F ========================================= Total Expenses G = E + F ...

Conditional formating condition..?
Hi All I have 2 CF conditions associated with a cell (C14), one of which is: Formula is =OR($C$4="Fred SOMEBODY",C14<>"10:30:00") What happens when this is true isn't important/relevant. The value in the cell appears as 10:30 (formatted as custom/hh:mm) but the value in the 'formula window' is actually 10:30:00. This type of condition works fine when the cell contains a simple number (or text) but I can't get it to work for either times or dates. I have tried it with and without the inverted commas (around the time value) and have tried using 10:30 ...

Print Chart Idea Needed
I have a number of charts "stacked" one over the other - the user can only see one at a time (done by VBA). Now I want to put a feature on the worksheet where the user can select a chart and print it. Need ideas... If only one chart is visible at any given time, it kinda restricts the ability to select any chart. Or am I missing something? -- Regards, Tushar Mehta, MS MVP -- Excel www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article <025a01c3c599$d92c1980$a301280a@phx.gbl>, anonymous@discussions.micro...

Condition Formatting!
How do I make a row of cell (shading) that change to red color when the word "trial" or "limit" is entered? i.e cell 3f contain the word "trial". From the column of trial to the 1st column, the entire row 3a to 3f will be red? a b c d e f 1 2 3 Trial 4 5 6 Here's some VBA you could paste into the codebehind page for that sheet: Private Sub Worksheet_Change(ByVal Target As Range) If UCase(Target.Value) = "TRIAL" Or UCase(Target.Value) = "LIMIT" T...

Simple Stock Watchlist Chart
Dear all, Does anyone have a spreadsheet that they could send me that allows you to enter purchase price/date of a paticular stock and quantity purchased, and it will graph/chart the net loss/profits from a paticular stock or portfolio. Any help or direction would be appreciated, benengel14@NOSPAMhotmail.com From Ben {Delete the "NOSPAM" in the email address if you wish to send me an email} I have this in Microsoft Money. "Ben Engel" <benengel14@NOSPAMoptusnet.com.au> wrote in message news:41be70af$0$4533$afc38c87@news.optusnet.com.au... > Dear all, &g...

chart conditional formatting
I have a line chart with 3 lines, red, blue and green. Is there any way I can conditionally format some, but not all, points on the red line to colour white - ie make them invisible. I have looked at Jon Peltier's site - he addresses conditional formatting - but cannot see a solution there. Thanks in advance to all who try to help. -- bob777 ------------------------------------------------------------------------ bob777's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28504 View this thread: http://www.excelforum.com/showthread.php?threadid=488635 Hi, Ha...