Bubble Chart in Excel chart or Excel Pivot Chart

[cuteajax]

I want to do a bubble chart using 3 variables (2 category and 1 numerical). I have attached a table of the variables and I have also attached a mock-up of the intended outcome. Can someone please help with how i can implement this on Excel chart or excel pivot-chart. Thank you

 

[Jon Peltier]

The data for this chart is shown below. There is the main data range with X and Y values and Bubble Sizes (plus the text labels for the axes, which are in the table for reference only). For the axis labels, we need a couple of extra ranges with data for the dummy series. Since we have to add dummy series using data outside of the main data range, we can't make a pivot chart. But if the main data range is a pivot table, we can make a regular chart from a pivot table (follow this link to read the tutorial).

Select the blue shaded range, and insert a bubble chart (chart 1 below). I've scaled the bubble size to 50 instead of 100, otherwise the bubbles are huge and overlapping.

Copy the orange shaded range ("X Axis"), select the chart, and use Paste Special to add the data as a new series, series name in first row, category values in first column. Copy the green shaded range, and paste special with the same settings. Chart 2 shows these dummy series along the X and Y axes.

I have added data labels to both dummy series in chart 3; the default Y values appear in the labels, which are aligned directly on top of the bubbles.

Select the X-axis, change the minimum value to 0, and change the axis label position to No Labels. Repeat for the Y-axis, and you'll have chart 4.

Select the X-axis labels, press Ctrl+1 to format them. Check Value from Cells, then select the range with the X-axis labels (the gold shaded cells next to the orange shaded cells), then uncheck the Y values box. Select Left for Label Position.

In the same way, select the Y-axis labels, press Ctrl+1 to format them. Check Value from Cells, then select the range with the Y-axis labels (the gold shaded cells next to the green shaded cells), then uncheck the Y values box. Select Below for Label Position. This results in chart 5.

Finally, format both of the dummy series to use no fill (to hide the bubbles). Select the plot area, and drag the left edge to the right and the bottom edge upward, to make room for the labels (chart 6).