Often you may want to color the points in a bubble chart in Excel based on a value or category, similar to the plot below:
Fortunately this is easy to do in Excel.
The following step-by-step example shows exactly how to do so.
Step 1: Enter the Data
First, let’s enter the following dataset that contains information about points, assists, and years in the league for various basketball players:
Suppose we would like to use this data to create a bubble chart using Points as the x-values, Assists as the y-values and Years as the size of the points.
Step 2: Format the Data
Before we can create a bubble chart, we must first format the data in a specific manner.
First, we’ll enter the unique values for each team name along with the y-values and z-values to be used in the plot:
Next, type the following formulas into cells F3 and G3:
- F3: =IF($A3=F$1, $C3, NA())
- G3: =IF($A3=F$1, $D3, NA())
Next, highlight cells F3 and G3.
Click and drag these formulas to the right until you hit cell K3.
Then click and drag the formulas down until you hit cell K11:
Step 3: Insert the Bubble Chart
Next, highlight the range B3:B11. Then, hold Ctrl and highlight every cell in the range F3:K11.
Then click the Insert tab along the top ribbon and then click Bubble within the Charts group:
The following bubble chart will appear:
Each of the players from the dataset are shown in the bubble chart with each bubble colored based on the team name.
Step 4: Modify Appearance of Bubble Chart (Optional)
Lastly, feel free to modify the colors, point sizes, and labels to make the plot more aesthetically pleasing:
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
How to Create a Bubble Chart with Labels in Excel
How to Color a Scatterplot by Value in Excel
How to Add Labels to Scatterplot Points in Excel
