How to color a bubble chart by value in Excel?

---

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: