How to change bubble chart color based on categories in Excel?
If you want to change different bubble colors based on different categories in a bubble chart in Excel as below screenshot shown, this tutorial can help you.
1. You need to copy your x axis values and paste them in a blank column next to your original data range firstly.
2. Then type the category names into the cells right above the data range, and keep a blank cell between every two categories. See screenshot:
3. Then in the cells under each category, type y, and type Bubble in to the cells under each blank cell. See screen shot below:
4. Type this formula =IF($A3=F$1,$C3,NA()) into the cell under Y cell, and drag the fill handle to the range you need.
Note: $A3 indicates the category name in your original data range, and F$1 indicates the category you need in this column, $C3 indicates y value in your original data range.
5. Then type this formula =IF(ISNA(F3),NA(),$D3) into the cell under Bubble cell and drag fill handle to the range you need.
Note: F3 indicates the cell left to the active cell, and $D3 indicates the bubble value in your original data range.
6. Select the new data range you create in above steps and click Insert > Other Charts > Bubble, and select the bubble type you need.
In Excel 2013, click Insert > Insert Scatter (X Y) or Bubble chart > Bubble.
Now you can see the same category with the same bubble color.
If you want to change chart color based on value, you can go to this tutorial How to change chart color based on value in Excel.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!