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).
