How to Normalize Bubble Charts in Excel

Bubble charts are a fantastic way to display three sets of related data on a single two-dimensional chart, but their default settings in Excel can sometimes be visually misleading. To make your bubble charts clear and accurate, you need to normalize the data that determines the bubble size. This article will walk you through why normalization is important and provide a step-by-step guide to doing it in Excel.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

What Exactly Is a Bubble Chart?

Think of a bubble chart as a variation of a standard scatter plot. In a scatter plot, you plot data points using X and Y coordinates. A bubble chart does the same but adds a third dimension, represented by the size of each "bubble" at each coordinate. This makes it an effective tool for comparing relationships and identifying patterns between three variables.

Each bubble on the chart represents a single data point, where:

• The horizontal position (X-axis) represents the first value.
• The vertical position (Y-axis) represents the second value.
• The size of the bubble (Z-value) represents the third value.

For example, a marketing team might use a bubble chart to visualize campaign performance. They could plot the number of leads generated (X-axis) against the conversion rate (Y-axis), with the total ad spend for each campaign represented by the bubble size. This allows them to see at a glance which campaigns are efficient (high leads, high conversion) and costly (large bubble) versus those that are less expensive but perform just as well.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Why Default Bubble Sizes Can Be Inaccurate

The main problem with standard bubble charts in Excel arises from how it interprets the data for bubble size. By default, Excel uses the area of the bubble, not its diameter or radius, to represent the Z-value. This can distort the perceived differences between your data points.

Let's consider a simple example. You’re mapping product sales across two regions:

• Region A: $50,000 in Revenue
• Region B: $100,000 in Revenue

Your brain naturally compares the size of things by their width or height (diameter). You’d expect the bubble for Region B to be twice as wide as the bubble for Region A. But that’s not what Excel does. It makes the surface area of Bubble B twice as large. Because of the geometry involved (Area = πr²), this means the radius (and diameter) of Bubble B will only be about 41% larger than Bubble A (the square root of 2 is ~1.41). Visually, this dramatically under-represents the fact that Region B’s revenue is double that of Region A.

Another common issue is dealing with outliers. If you have one bubble representing a value of $5,000,000 and another representing $20,000, the smaller one might be so tiny it becomes nearly invisible on the chart, rendering it useless for comparison.

This is where data normalization comes in. It’s a technique to rescale your bubble size data into a common range, giving you full control over the chart's visual representation.

Data Normalization Explained

Normalization is the process of translating data into a standardized scale, typically between 0 and 1, or sometimes between another defined range like 1 to 100. By doing this, you're not changing the intrinsic relationship between the data points - the largest value remains the largest, and the smallest remains the smallest - but you are controlling the scale to prevent visual distortion and make outliers more manageable.

For bubble charts, normalizing the Z-value data solves two main problems:

1. Proportional Representation: It helps ensure that the size differences between bubbles accurately reflect the mathematical differences in your data.
2. Visibility: It prevents extremely large values from making smaller values disappear and ensures all your data points are clearly visible on the chart.

By creating a "Normalized Size" column in your dataset, you can build your chart from that data instead of the raw numbers, leading to a much more insightful and honest visualization.

Step-by-Step Guide: How to Normalize Bubble Charts in Excel

Let's walk through the process using a sample dataset. Imagine we are a SaaS company analyzing different pricing plans.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Step 1: Set Up Your Data

First, organize your data into columns. For our example, we'll use Pricing Plans, Monthly Customers (X-axis), Customer Lifetime Value or LTV (Y-axis), and Monthly Recurring Revenue or MRR (Bubble Size).

Your table in Excel should look something like this:

(Include your actual data here)

Step 2: Calculate the Normalized Size Using a Formula

Now, let's add our helper column, "Normalized MRR," to scale our bubble sizes. We’ll use the MIN-MAX normalization formula, which rescales numbers to a range between 0 and 1.

The formula is: (Current Value - Minimum Value) / (Maximum Value - Minimum Value)

In our Excel table, our MRR data is in cells D2 through D5. In cell E2, enter the following formula:

=(D2-MIN($D$2:$D$5))/(MAX($D$2:$D$5)-MIN($D$2:$D$5))

Formula Breakdown:

• D2 is the current value for the row you're on (the "Pro Plan").
• MIN($D$2:$D$5) finds the lowest MRR in the entire dataset.
• MAX($D$2:$D$5) finds the highest MRR.

Pro Tip: The dollar signs ($) are important. They create an absolute reference to your data range, so when you drag the formula down to the cells below, the MIN and MAX references won't change. After entering the formula in E2, click the small square in the bottom-right corner of the cell and drag it down to E5.

Your table should now show the normalized values, all between 0 and 1.

Step 3: Create the Bubble Chart

Now for the fun part! Let's build the chart.

1. Highlight all of your data, including the headers and the new normalized column.
2. Go to the Insert tab on the ribbon, click on the Scatter (X, Y) or Bubble Chart icon, and select the Bubble chart type.

Excel will likely guess the data sources incorrectly at first, so we'll need to adjust them.

Step 4: Configure Your Chart's Data Series

Right-click anywhere on the chart and choose Select Data...

Excel often creates separate series for each numeric column. You should see a box labeled "Legend Entries (Series)." Remove any existing series you don't need until there is only one left, and then select that one and click Edit. Now, specify the correct ranges:

• Series Name: Click in the box and then select the cell with your desired title, for example, cell A1 ("Pricing Plan Analysis").
• Series X values: Select the data for your X-axis. In our case, that’s cells B2:B5 for Monthly Customers.
• Series Y values: Select the data for your Y-axis, which is LTV in cells C2:C5.
• Series bubble size: This is the most important step! Delete whatever is in this field and select your normalized values from E2:E5 ("Normalized MRR").

Click OK. You now have a bubble chart where the bubble sizes are scaled correctly and proportionally. Go ahead and add a chart title and axis labels to make it easy to understand.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Tips for Refining Your Chart

Once you’ve created your normalized bubble chart, here are a few extra tips for making it even better.

Add a Scaling Factor

A normalized range between 0 and 1 sometimes results in bubbles that are still a bit small overall. To fix this, you can multiply your normalized value with your own desired scale factor. For instance, to scale them from 1 to 100 you would amend the original formula like so:

= ((D2 - MIN($D$2:$D$5)) / (MAX($D$2:$D$5) - MIN($D$2:$D$5))) * 100 + 1

This will scale every value and make every bubble more noticeable on the chart. The +1 addition ensures the minimum value doesn't have a bubble size of zero. Try to find a scale that suits your data best by trying various numbers like a range from 5, all the way up to 200 depending on what looks best for your situation.

Display the Original Values in Labels

Since your bubble sizes now correspond to normalized data, the standard data labels won’t show the original MRR figures. Here's how to fix that:

1. Right-click on any of the bubbles, and choose Add Data Labels. Numbers from the Y-axis (LTV) will probably pop up by default.
2. Now, right-click on one of the new data labels and select Format Data Labels...
3. The Format Data Labels pane will open on the right. In the "Label Options" section, check the box for Value From Cells.
4. A dialog box will appear asking you to select the data label range. Select your original MRR data (cells D2:D5) and click OK.
5. Finally, uncheck the "Y Value" box, leaving only "Value From Cells" checked, and set your ideal label position from the "Label Position" menu. "Center" often works really nicely for bubble charts with labels, so try this one out next time around!

There you go! Your chart is accurately scaled and clearly shows the original MRR data for each plan.

Final Thoughts

Normalizing a bubble chart's data can be a simple but powerful technique to transform a chart that could have otherwise turned out quite confusing into an accurate and incredibly useful one instead. By rescaling the data you want to show as Z values (the bubble size), you gain a lot more control over how your chart is shown, making your insights more credible in turn!

Maintaining reports like this in Excel is useful, but it can quickly get complicated when your reporting process combines multiple sheets and dozens of SaaS applications such as Google Ads or Shopify. We've built Graphed because we wanted to create an easy way for you to connect to your online data sources and ask your questions directly in normal speech and see your visual response as a dashboard in just a few seconds rather than hours!