About Us

Del Cotter

On Fri, 8 Feb 2008, in microsoft.public.excel.charting,
said:

Suppliers across the bottom, with their respective brands above. The
position on the Y axis should be determined by the retail price.

Excel offers two kinds of line-and-marker graphs:

Line Chart = Interval vs. Category
XT (scatter) Chart = Interval vs. Interval

But a bubble chart is *only* offered as a type of scatter chart
(Interval vs. Interval), and the chart you're trying to design is a sort
of Interval vs. Category. So you need to be a bit sneakier than usual.

Create a range including a column that assigns each of your Suppliers a
number, and use that number as the X series on your graph,

Supplier Brand SupplierNo RetailPrice Volume
Smith XYZ 1 $1.00 1,000
Smith ABC 1 $2.50 2,000
Jones XYZ 2 $1.00 9,000

Now, using *only* the last three columns, use the chart wizard to make
your bubble chart. It would probably be best to remove the "SupplierNo"
label from the top of the column temporarily, to clue the wizard in to
the fact that this is the column you wish to be the X axis.

Now create another range of columns so that the SupplierNo is one of
three columns like so:

Suppliername SupplierNo Dummyprice DummyVolume
Smith 1 0 1
Jones 2 0 1
etc. 3 0 1

And create another series on the same chart, again using only the last
three columns. Each supplier appears just once in this series, and the
data point is at the bottom of the chart. Format this series so the
bubbles are invisible, and use one of the existing Excel Add-in
utilities to give them the labels defined in the first column. (Google
"Excel chart labeler tools" to find Add-ins) You can use the same
utility to label the bubbles with their brand.

Now you have a custom-made Category scale made from a dummy series!
(Google "Excel custom axis dummy" for worked examples)

The first two columns of this range can also be used as a lookup table
to decide what the SupplierNo values in the first range are going to be.

If you want each brand to have a different color bubble, separate the
products out into their own ranges by brand, and add them each as a
different bubble series, one range at a time (I have never succeeded in
arranging them all in a block so that the Chart Wizard makes them all
into bubbles in a well-behaved and predictable way).

--
Del Cotter
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Excel 'bubble' chart where colors are used instead of bubble size	paulmichael	Charts and Charting in Excel	3	December 5th 07 03:33 PM
How can I change the color of negative bubble in bubble chart	Jon Peltier	Charts and Charting in Excel	0	July 4th 07 03:29 PM
How can I change the color of negative bubble in bubble chart	Daniel	Charts and Charting in Excel	1	July 4th 07 03:25 PM
ho to change in the bubble chart the bubble position and size	laszlo	Charts and Charting in Excel	0	March 25th 05 04:45 PM
How do I move a hidden bubble to the front in a bubble chart in E.	Scott	Excel Discussion (Misc queries)	0	February 20th 05 07:55 PM

Menu

About Us