Automating bubble chart colours

Does anyone know if the colour of a bubble within a chart can be 
conditionally formatted through reference to a cell within the spreadsheet ?

I want the colour of each bubble within the chart to based on the result of 
a calculation within the spreadsheet. Currently the cells containing the 
calculation are conditionally formatted to give a red, amber, green result 
and I want to link the colour of the conditionally formatted cells to the 
colour of the associate bubble.
(At the moment I update the bubbles manually) 

0
Vdubboy (2)
6/9/2006 11:31:03 AM
excel.charting 18370 articles. 0 followers. Follow

2 Replies
582 Views

Similar Articles

[PageSpeed] 56

Hi,

The normal work around for making a charts formatting conditional is to 
use multiple series. Then via formula only plot those points 
(bubble/columns/markers/bars) that are valid.

See Jon's page for examples.
http://peltiertech.com/Excel/Charts/ConditionalChart1.html

Cheers
Andy

Vdubboy wrote:
> Does anyone know if the colour of a bubble within a chart can be 
> conditionally formatted through reference to a cell within the spreadsheet ?
> 
> I want the colour of each bubble within the chart to based on the result of 
> a calculation within the spreadsheet. Currently the cells containing the 
> calculation are conditionally formatted to give a red, amber, green result 
> and I want to link the colour of the conditionally formatted cells to the 
> colour of the associate bubble.
> (At the moment I update the bubbles manually) 
> 

-- 

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
0
andy9699 (3616)
6/9/2006 11:45:48 AM
That's perfect, thank you Andy

"Andy Pope" wrote:

> Hi,
> 
> The normal work around for making a charts formatting conditional is to 
> use multiple series. Then via formula only plot those points 
> (bubble/columns/markers/bars) that are valid.
> 
> See Jon's page for examples.
> http://peltiertech.com/Excel/Charts/ConditionalChart1.html
> 
> Cheers
> Andy
> 
> Vdubboy wrote:
> > Does anyone know if the colour of a bubble within a chart can be 
> > conditionally formatted through reference to a cell within the spreadsheet ?
> > 
> > I want the colour of each bubble within the chart to based on the result of 
> > a calculation within the spreadsheet. Currently the cells containing the 
> > calculation are conditionally formatted to give a red, amber, green result 
> > and I want to link the colour of the conditionally formatted cells to the 
> > colour of the associate bubble.
> > (At the moment I update the bubbles manually) 
> > 
> 
> -- 
> 
> Andy Pope, Microsoft MVP - Excel
> http://www.andypope.info
> 
0
Vdubboy (2)
6/9/2006 3:03:02 PM
Reply:

Similar Artilces:

Is it possible to resize chart itself (not chart area)
Hi I have several charts on one sheet and a couple have resized automatically after I had to make a change to the legend. I can't figure out how to re-size the chart itself as every time I click on it a section is selected e.g. axis, series, etc. Can anyone help please? EXCEL 2007 Take the following actions:- 1. Click in the Chart. Chart Tools (highlighted in green) should appear in the right hand side of the Title Bar. 2. Select Chart Tools / Layout tab. 3. Current Selection group / click the drop down arrow and select Chart Area. 4. Now go to one o...

Three line charts on same chart
can I break the y axis to plot three series with total different value on the same line chart and showing their clear paths. Chris, These sites might help: http://www.peltiertech.com/Excel/Charts/BrokenYAxis.html http://andypope.info/charts/brokencolumn.htm These examples deal with columns but you can easily apply the same concepts to line charts. ---- Regards, John Mansfield http://www.pdbook.com "Chris" wrote: > can I break the y axis to plot three series with total different value on the > same line chart and showing their clear paths. Chris - Here's anoth...

Pie Chart: want separate chart for each "RN"
Hi All. I have a report, which contains numerous amounts of data, so there are several subreport in the main report. Each subreport is broken out by "RN". The subreports contain rows of data, counts & percentages. Everything works fine, EXCEPT, the Pie Chart subreport. It is pulling in ALL "RN" #'s, making a very ugly pie chart. So, I want to run the data once for a "TEAM" of "RN's" but have the report separate out as many different reports as there are "RN's". Which it does this for ALL OTHER pieces of data, excep...

Chart Formatting Change
Why does chart formatting (font color, size) in 2007 change when the file is re-opened? (It looked correct when I saved and closed the file.) The changes are relatively random, sometimes occurring on only some of the graphs but sometimes on all of the graphs, and will happen when I open the file this morning but not when I open the file later in the afternoon. I have this same problem and came looking for an answer. Since I haven't found one, I'm going to use a marco that changes it back to my colors upon opening the excel file. If anyone out there has an easier way. I'd l...

Gantt chart in Excel
I have been given an assignment on Gantt charts for my unversity course. I know there are many programs that are made for using Gantt charts, but I have to use Excel. The snag is that the chart has to live, ie when my lecturer changes text values, the whole chart changes accordingly. This removes the obvious solution of just shading the required cells. I've tried several ways, but none of them have worked. Any suggestions would be greatly appreciated. Feel free to email me at justinwalls@hotmail.com Many thanks! Justin ...

Linking chart parameters to cells
Is anyone aware of a way of linking chart parameters normally set in the various dialog boxes, to a value in a cell. For example, suppose I'd like the value (Y) axis minimum and maximum value to change based on the value of a certain formula, can I somehow link those values to a cell in the spreadsheet. If not, it'd be cool if I could. Maybe the next version of Excel? >-----Original Message----- >Is anyone aware of a way of linking chart parameters >normally set in the various dialog boxes, to a value in a >cell. For example, suppose I'd like the value (Y)...

Broken Y Axis in Line Chart
Is it possible to create the broken Y-Axis on a Line Chart? A logarithmic axis is not appropriate form my data and I do not want to create a secondary axis (confusing for the reader). I have looked at the methods described by Jon Peltier, Andy Pope, and Tushar Mehta and (if I understand them correctly) they work for column charts and scatter plots respectively. I understand these techniques but my data is categorical (months on X-axis) and numerical on the Y-axis. A column chart is an option but does not convey the temporal trends ant I wish to illustrate. Any and all advice ap...

Scaling XY charts with suppressed starting value
The autoscaling feature of the Excel graphing function seems to have (limited) mind of its own. I'm trying to graph Y-values in the -1.5 t 1.5 range and X-values from 190 to 410 with the minimum from 190 to 29 and the max value from 290 to 410. The autoscaling of Y works fine, but the X-axis always start at 0 while the high value properly sets the maximum. Of course, I could fi the minimum at 190, but that is not what I had in mind. Does anyone know how to overcome this limitation without using a macro -- Message posted from http://www.ExcelForum.com Per - You need to make an XY Sca...

Using Access Excel Automation, can I delete or rename a worksheet
Hi I am using automation to produce an excel workbook from an access database. Everything is working fine, except that my workbooks always have 3 additional worksheets - sheet 1, sheet 2 and sheet 3. Can I delete these, or rename them? Stapes On 13 Sep, 11:04, Stapes <steve.sta...@gmail.com> wrote: > Hi > > I am using automation to produce an excel workbook from an access > database. Everything is working fine, except that my workbooks always > have 3 additional worksheets - sheet 1, sheet 2 and sheet 3. Can I > delete these, or rename them? > > Stapes Found ...

How to stop Pie Chart from using N/A# for values
Hello, I have 2 columns: Column 1 is customer name. Column 2 is sales for the period. If a customer had no sales for the period the sales column will either be N/A# or zero. When I plot in a pie chart, I only want to show customers who had sales for that period. Is there a way to only plot those customers with sales without having to change the range and sort every period? Customer Sales Cust1 $100 Cust2 0 Cust3 $50 Cust4 N/A The pie chart data labels show the customer regardless of sales and if I blank out the customer, the value data label s...

Automating a Summary Tab
I have a workbook that I need a summary page that will breakout 2 different tabs and subtotal it Is there a way that if 1 tab holds Part #, Desc, and Price Another tab holds Total Price for the type of project it is So that would be: Model, List Price In a Summary tab I would like to show Item # (this would automatically start numbering each row) depending on how many choices they made in the other tabs Part # Description Model Length of term # users List Price They could choose anywhere from 2 parts to 15 parts how do I get it to automatically populate for what they chosen in this new...

Revenue charting
I am trying to chart revenue trends over a monthly period. However, want the series line in the chart to stop at the last day revenue i shown: I want it to disappear from the chart entirely (not drop t zero). I am running into a problem with the following data: MON 2-Feb-04 ='Monthly Detail Summary'!C12 TUE 3-Feb-04 =IF(ISERROR('Monthly Detai Summary'!D12+B8),#N/A,IF('Monthly Detai Summary'!D12+B8=B8,#N/A,'Monthly Detail Summary'!D12+B8)) WED 4-Feb-04 =IF(ISERROR('Monthly Detai Summary'!E12+C8),#N/A,IF('Mo...

How do I make a vertical floating bar chart?
I want to make a floating bar chart in Excel, but I want the bars to be orientated vertically rather than the horizontal default. In other words, I want the range of the floating bars to be displayed on the y-axis rather than the x-axis. Can anyone help me? Shane Stamschror Hi, You need to use a stacked column chart with 2 series. The first series provides the 'float'. Format this series to have no fill or border. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "danke_shane" <danke_shane@discussions.microsoft.com> wrote in message ne...

Time chart
I need a time chart as in example A1= Henry B1=15 C1=21 A2= Sally B2=12 C2-=18 A3= George B3=11 C3=16 A4= Alex B4=10 C4=14 Henry ------------------------ Sally ----------------------- George -------------------- Alex ----------------- 10 11 12 13 14 15 16 17 18 19 20 21 You could simulate the chart with an XY Scatter chart of the data 15 4 21 4 12 3 18 3 11 2 16 2 10 1 14 1 Format the y-axes to show no labe...

chart with dates on the x-axe
how can you change the x-axe into a text x-as when the data are dates ? on specific dates a want to show a specific value. When I want to create e.g. a line-type chart, the values are ok, but the x-axe with the dates becomes a time-ax in stead of showing the real dates from the data. Is there a way to change the dates into text, without making it explicit text (with a ') because I want to use them also for calculating things. ??? -- chriske ------------------------------------------------------------------------ chriske's Profile: http://www.excelforum.com/member.php?action=getinf...

adding numbers in an excell chart automatically
How to create an ongoing addition of numbers in an excell chart. Or sum of Numbers I should Say. I need the info for work. I think there is an easier way than how I am doing it. Worksheets are the things to use for adding numbers. That's not what a chart is designed to do. -- David Biddulph "sanicay" <sanicay@discussions.microsoft.com> wrote in message news:E94E1035-DC2C-4698-AC32-3439BDB2BDA6@microsoft.com... > How to create an ongoing addition of numbers in an excell chart. Or sum of > Numbers I should Say. I need the info for work. I think there is an >...

lost data source to Word 2007 chart
I created a chart in Word 2007, which when opening and double clicking on the chart automatically opened an excel spreadsheet where the data is input. For some reason, the excel data source no longers opens. The Data section under Chart Tool: Layout; is now grayed out and cannot be selected. How do I locate the Excel Data Source? ...

Chart Wizard won't work
I have classwork to perfoerm on EXCEL, but I am unable to use the Chart Wizard because it is grayed out, and unavailable. I have made certain that I have selected ranges with data in them, so what do I do next??? Perhaps it's not installed? Some versions of MS Office do not install the wizards when the default install method is used. Use Custom install, and choose "Run all from my computer". ************ Anne Troy www.OfficeArticles.com "peg2unic" <peg2unic@discussions.microsoft.com> wrote in message news:255A90EF-B896-48EF-86DC-5988EEC707C1@microsoft.com....

Moving Data Points on Scatter Chart in 2007
I have used Excel to construct various ship curves in all versions until 2007. I would create a table of offsets (x,y points) and build a simple integration scheme around the table. I would then graph the offsets. I could go into the graph, grab a particular data point and move it either horizontally or vertically. This would update the table and update the integration. I would have the area (or volume) display on the graph. So I could fair a smooth curve by adjusting offsets (with the smooth lines turned on) until I got the area and centroid I needed. Until Excel 2007! I cannot...

auto position of data labels in a line chart
Hi All Does anybody know of a way of automatically having data labels either positioned above or below depending on another cell value? Basically when I update data behind a series of charts, sometimes (depending on the values) its best to have the value labels above and sometimes below. I want the chart to know which is best automatically without me clicking on each series of data labels and manually changing them. I was thinking I could link this condition to a cell (in which I would have a formula telling it what to do). I think the only way that this could be done would be withi...

charting pivot table results by month
I am trying to create a chart that shows monthly totals from pivot table results. Each record has a date in the first field in the format, 01/01/2004. I have been using the Month function on the date field to change it to a number corresponding to the month before creating the pivot chart. This gives me the monthly totals that I need but I think there must be an easier way. Any suggestions? Thanks. Ken Ken - If you use a custom number format of m or mm, your date will appear as just the month's number, 2 or 02 for February. mmm would give you Feb, and mmmm February. This wouldn'...

I need excel to only use the last 26 rows in a chart
I have a large spreadsheet that has data in it that is entered weekly. I would like for the chart to grab the most recent 26 weeks of data automatically. Does anyone know of a formula that will do this? Logan: If you haven't already, you may want to establish dynamic ranges for your data set. This will allow you to automatically update your chart(s) as new data is entered It will also allow you to plot the last number of days/weeks that you want. This post explains how to set up dynamic range names and plot user specified number of days. It should get you started on dynamic range ...

How do I set the size of a chart in excel (without using the mous.
How do I set the size of a chart in excel (without using the mouse)? You can set the size programmatically: With ActiveSheet.ChartObjects(1) .Width = 300 .Height = 200 End With katy wrote: > How do I set the size of a chart in excel (without using the mouse)? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html Hi, If you hold the shift key whilst selecting the chart you can move with the cursor keys. CTRL+1 will display the format dialog which will allow you to alter the height and width. Only thing is I don't know how to select t...

add individual data points to a bar chart of averages
I have groups of data points in categories. I can easily calculate the average in each category and show these as a bar chart, but is it possible to show the individual data pioints in each category as well? Thanks, Hi, How are you plotting the data points - as columns or line? and also could you show us dummy data layed out so we can understand what you data looks like. If you plot a clustered column chart you could show the average for each cluster as a line within the cluster or as a single point. -- Thanks, Shane Devenshire "lenos" wrote: > I have groups of dat...

Charts GONE after Windows XP SP3 Update!?!
I have Office 2007 SP1 Enterprise Edition and, until last night, Windows XP Professonal SP2 installed on my PC. Last night I went through the normal Microsoft Update site to upgrade to SP3 and everything seemed to go well. However, I noticed this morning that all of my Excel files I have that previously had worksheets on them showing charts are not displaying those charts at all. The worksheets that they were on are still in the files, but the charts themselves seem to have disappeared. Has this happened to anyone else and does anyone know of a way to resolve this? Thanks... Hi, ...