How to change bubble chart color based on categories in Excel?
When visualizing data using a bubble chart in Excel, it’s often necessary to use color to distinguish different data categories or groups for better analysis and readability. If you want to display bubbles in different colors according to their respective categories - as shown in the screenshot below - this guide will walk you through several effective solutions, including step-by-step instructions, explanations of key parameters, and tips for troubleshooting common issues.
Change bubble chart color based on categories
Change bubble chart color based on categories
Excel does not natively allow you to assign bubble chart colors directly by category in one simple setting. However, with a careful data arrangement and utilization of helper columns and rows, you can visually differentiate categories using color. This method is practical when you have a moderate number of categories and want flexibility in color assignment. The process involves preparing your data so Excel treats each category as a separate data series, letting you manually or automatically assign distinct colors. Common scenarios for this approach include demographic segmentation, market categorization, or any analysis where clear visual grouping is needed. Note that for a large number of categories or frequent updates, maintaining this layout may become cumbersome, in which case VBA automation (see further below) might be more efficient.
Follow these steps to manually assign bubble colors based on categories:
1. Copy your x-axis values and paste them into a blank column next to your original data set. This will be used as a helper column to reorganize your data for visualization.
2. In the row above your newly arranged data, input each category name corresponding to the data that will appear beneath. Leave a blank cell between every two different categories. This separation is important, as it helps later when Excel differentiates categories as new series in the chart.
3. Under each category name, fill the next cells with the label Y; under each blank cell, enter Bubble. This step creates the proper structure so Excel interprets each category's data separately for plotting bubbles with distinct colors.
4. In the cell directly beneath the Y label for the first category, enter the following formula (assuming your original 'Category' column is A, 'Y' values are in column C, and your helper headers are in F1, G1, etc. Adjust ranges as needed):
=IF($A3=F$1,$C3,NA())
This formula checks whether the category in the row matches the header for the current column. If so, it returns the Y value; otherwise, it returns #N/A (so Excel ignores it). Drag this formula over the range under all Y header cells for each category.
Parameters explained:
$A3: The cell containing the current row’s category.
F$1: The category name in the header row.
$C3: The Y value from your original data row.
Tip: Double-check that your references use the correct mixed/absolute reference styles ($A3, F$1, $C3) as shown, or the results may not align properly as you copy the formula.
5. Next, in the cell under the Bubble header (just right of the previous column’s formula), input this formula and drag it down as far as needed: =IF(ISNA(F3),NA(),$D3)
This checks whether the cell to the left (F3) is #N/A; if it is, the formula returns #N/A, otherwise it returns the bubble size value.
Parameters explained:
F3: The cell with the output of the previous Y formula.
$D3: The original 'Bubble' value from your core data.
Tip: Consistent usage of $ for fixed references ensures your formula remains correct as you copy it across and down.
Possible error: If you see inconsistent data or misplaced bubbles, re-check whether helper columns and category headers match your data arrangement.
6. With all formulas applied and results displayed in your helper area, select the newly structured data range (including category names, Y values, and bubble values). Then go to the Insert tab, click Other Charts, and choose Bubble. Select your preferred bubble chart style. In Excel 2013 or later, go to Insert > Insert Scatter (X Y) or Bubble chart > Bubble.
Note: Each separate category, arranged as a new series in your data, will now display its bubbles in a different default color. If you wish, you can manually edit each series' color via right-clicking on the bubbles and choosing Format Data Series > Fill. For many categories, consider a distinct color palette for better clarity.
Now, your chart visually differentiates categories by bubble color, making it easier to identify trends, groupings, or outliers at a glance. This method is recommended for small to moderate datasets or when you need precise control over data structure and appearance.
If you are interested in dynamically changing colors based on value instead of category, you can refer to this detailed guide: 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!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in