Creating a bubble chart with 4 variables

Hi all,

Having some issues creating a bubble chart with 4 variables.

Attached is a generic example of what I am trying to do with some real data.

In the example, how would i format the chart to look as follows:
- y-axis: share price
- x-axis: number of employees (ascending)....(side note, in my actual data the x-axis would be a forward time series)
- The bubble size for each company is its market cap.
- Chart legend: shows the colors that represent the companies

Any help is appreciated.

Originally Posted by MrShorty

As near as I can tell, when you specify "vary colors by point" in the format data series dialog, Excel uses the X values for the legend entries, and I see no way to change this behavior. I see two possible approaches to getting around this limitation:

1) Use Data labels (values from cells) instead of the legend to indicate which bubble belongs to which company (if you are unfamiliar with addiing and editing data labels: ). Done correctly, this will place the company name adjacent to the corresponding bubble. The main downside to this approach is when several bubbles are very close together (as with Microsoft and Apple in your sample data), it can become more difficult to tell which company name goes with which bubble.

2) Add each company to the chart as its own data series (I usually use the Select Data dialog to add data series in these scenarios). By specifying the company name in each serie's "name" field, the legend can show the company name adjacent to the corresponding color. The main downside to this approach is that adding multiple data series at chart creation is tedious. Therefore, the main goal of the spreadsheet programmer is to design the spreadsheet to avoid creating the chart anew with each new data set. Design the spreadsheet so that one need only paste new data into the sheet, and the chart automatically updates accordingly.

How would you like to proceed?

Thanks! I used the second approach and it worked well. We can consider this problem solved. I will also look into option 1) just for my understanding.
Appreciate the help!