To make a proportional area chart in Excel, use a bubble chart: arrange each category’s X position, Y position, and size value in separate numeric columns, select those columns, then choose Insert > Insert Scatter (X, Y) or Bubble Chart > Bubble. Excel sizes the bubbles to represent the third value, so the chart can show relative magnitudes by area; add category labels and adjust the layout to make the comparison clear.
What a Proportional Area Chart Shows
A proportional area chart uses the size of a shape—often a circle—to communicate a number. When the areas are proportional, a value twice as large should be represented by twice the area, not by a circle with twice the diameter. This distinction matters: doubling a circle’s diameter makes its area four times larger.
Excel does not offer a chart type specifically named proportional area chart. In everyday spreadsheet use, people often mean a bubble chart, because it represents a third numeric value through bubble size while using horizontal and vertical positions for two other values. If you only need to compare category totals, you can use the horizontal position as an organizing device and hide the axes. If you need a hierarchy, such as sales by department and product, a treemap may be a better fit.
The phrase can also be confused with an ordinary area chart. An area chart fills the space beneath a line to emphasize change over an ordered sequence, usually time. The filled region is not generally intended to encode each category’s total by its two-dimensional area. Choose a bubble chart when the size of individual marks should carry the comparison.
When to Use a Bubble Chart
A bubble chart is useful when a dataset has three meaningful numeric measures. For example, a business might compare product profitability (X position), customer satisfaction (Y position), and annual sales (bubble size). The chart lets a reader see whether high-sales products also score well on the other two measures.
For a simple proportional-area comparison, only the size measure may be important. You can still use a bubble chart by assigning each category a different X position and giving every point the same Y value. In that case, position is just a way to separate the bubbles, not an additional result. Make that clear in the title or a note so readers do not mistake the artificial positions for data.
Bubble charts are less suitable when there are many categories, values are very close together, or labels need to fit inside every mark. Overlapping circles make relative size hard to judge, and people are generally better at comparing aligned lengths than areas. For precise comparisons, consider a sorted bar chart alongside or instead of the bubble view.
Prepare the Data
Use one row per category, with a text label and three numeric fields for X, Y, and bubble size. For a basic category comparison, X can be a sequence of positions and Y can be the same number in every row. Bubble size should contain the nonnegative quantity you want the chart to represent. Do not include the category-name column in the three numeric columns selected for the chart.
For example, a small shop might compare four product groups with sales of 40, 30, 20, and 10 thousand dollars. Put the group names in column A, positions such as 1, 3, 5, and 7 in column B, the constant value 1 in column C, and the sales figures in column D. The gaps between X positions give large bubbles room; they are not sales values. If your X or Y measures are meaningful, use those actual values instead.
Check the size values before charting. Blank cells, text-formatted numbers, negative values, and accidental totals can produce confusing results. Keep units consistent, and decide how to handle zero: a zero-sized bubble may not be visible, so a table or a separate label may be needed to show that category.
Create the Bubble Chart in Excel
- Place the numeric X, Y, and size columns next to each other, with headers in the first row. For the example above, select B1:D5.
- Choose Insert > Insert Scatter (X, Y) or Bubble Chart > Bubble. Pick the standard Bubble option for ordinary circles. Use the 3-D option only when the perspective effect is genuinely useful; it can make size comparisons harder.
- Check that Excel used the intended columns. If it did not, select the chart, go to Chart Design > Select Data, choose the series, and edit the X values, Y values, and bubble size ranges. Keep all three ranges aligned row by row.
- Give the chart a meaningful title, such as Product-group sales. If X and Y are merely spacing coordinates, remove or hide their axes and explain that the horizontal placement is for layout only.
Excel bubble charts need X and Y coordinates, unlike a pie chart, which can compare a single series of category values without coordinate columns. If the bubble chart initially looks like a scatter plot with no obvious category order, that is expected: its position comes from the X and Y data, while the third column controls bubble size.
Add Labels and Make Size Meaningful
Category labels save readers from having to match circles to a separate legend. Select the chart, then use Chart Design > Add Chart Element > Data Labels > More Data Label Options. In the Format Data Labels pane, select Value From Cells and choose the category-name cells. Clear other label options, such as Y Value, if they would add unwanted numbers. The exact pane options can vary slightly with the selected chart element and Excel build.
For a straightforward category display, use a contrasting fill, a thin border, and enough space between bubbles to prevent overlap. Avoid assigning a different color to every category unless color also communicates a meaningful group. If it does, include a clear legend. Keep the background and gridlines quiet, and use readable labels that do not collide with one another.
Excel’s Bubble Size Scale setting changes the overall visual scale of the series; it does not change the underlying values. Select a bubble, open Format Data Series, and look under Series Options for Scale bubble size to. Increasing the scale can help small marks become visible, but it may also make the largest bubbles overlap. Keep the same scale when comparing charts side by side; otherwise, identical values can appear to have different importance.
Worked Example: Compare Four Sales Totals
Suppose the four product groups have sales values of 40, 30, 20, and 10 thousand dollars. Their total is 100 thousand dollars, so their shares are 40%, 30%, 20%, and 10%. If you want to show the percentage beside each category in the worksheet, put the sales values in D2:D5. With those sales values in D2:D5, enter this formula in E2 and fill it down through E5:
=D2/SUM($D$2:$D$5)
Format E2:E5 as Percentage. For the example numbers, the formula returns 40% in E2, 30% in E3, 20% in E4, and 10% in E5. The values add to 100%, which provides a useful check on the arithmetic. These percentages are optional labels or supporting calculations; the bubble-size column should still contain the original sales figures if the chart is meant to represent sales.
Do not make circle diameters equal to the sales figures. A circle representing 40 should have twice the area of one representing 20, which means its diameter should be about the square root of two times as large—not twice as large. Excel handles bubble sizing from the size values, so you normally enter the original magnitudes rather than calculating custom diameters yourself. The chart’s display scale affects the appearance, but not the underlying comparison.
Common Problems and How to Fix Them
- Bubbles overlap: Increase the spacing between X positions if those positions are only for layout, enlarge the chart’s plot area, or reduce the bubble-size scale. If the positions carry real meaning, do not distort them just to eliminate every overlap; use labels or a different chart instead.
- The chart shows the wrong values: Open Chart Design > Select Data and confirm the X, Y, and bubble-size ranges. A common mistake is including a header, selecting the label column as a numeric field, or offsetting one range by a row.
- One large bubble dominates: Check for a mistaken extra zero, inconsistent units, or a very large outlier. If the outlier is real, retain it and explain its effect rather than silently changing the data. A sorted bar chart may make the full range easier to interpret.
- Labels are unreadable: Shorten category names, enlarge the chart, or place labels beside the chart in a table. Excel’s automatic label placement is not always ideal, especially when circles are crowded.
- Readers assume position is meaningful: State that the X and Y positions are for separation if they do not represent actual measures. Remove unnecessary axes and add a caption that explains what bubble area represents.
Bubble Chart Versus Other Excel Charts
A bar chart is usually the strongest alternative when the goal is to rank categories or compare close values. All bars share a common baseline, so small differences are easier to see than they are with circles. Use a bubble chart when area itself is part of the visual story or when the chart also needs to show two other numeric dimensions.
A treemap is useful for hierarchical data, such as revenue divided into regions, then product lines. Rectangles represent parts of a whole, and nesting displays the hierarchy. In Microsoft 365 for Windows, select the category and value data and choose Insert > Insert Hierarchy Chart > Treemap. A treemap is not a substitute for a bubble chart when meaningful X and Y values are central to the analysis.
A pie or doughnut chart can show a few parts of one whole, but comparing many slices or similar-sized values is difficult. A line or area chart is more appropriate for trends across time. The best choice depends on the question: use bubbles for three numeric dimensions, bars for accurate ranking, treemaps for hierarchical composition, and line or area charts for ordered trends.
Frequently Asked Questions
Does Excel have a chart type called proportional area chart?
No. Excel does not list a chart type with that exact name. A bubble chart is the usual choice when circles should represent relative magnitudes, while a treemap can show proportional rectangles for hierarchical categories.
Are bubble areas proportional to the size values in Excel?
Bubble charts are designed to encode the third series through bubble size, and the values determine the relative sizes. Treat them as a visual comparison, not as precisely measurable printed areas: chart dimensions and the bubble-size scale affect how large the marks look on screen.
Can I make a bubble chart with category names instead of X and Y values?
A bubble chart requires numeric X and Y coordinates as well as a size value. Assign separate X positions and a constant Y value to create a row of category bubbles, then use data labels from cells to show the names. Explain when those coordinates are only for spacing.
How do I show category names on Excel bubbles?
Add data labels, open the Format Data Labels pane, select Value From Cells, and select the cells containing the category names. Turn off numeric label options you do not need. If the labels overlap, increase chart space or use a nearby table.
Should I use a bubble chart or a treemap?
Use a bubble chart when you need to compare a numeric size measure and possibly two other numeric variables. Choose a treemap when the data has parent-and-child categories and you want to show how each part contributes to the whole. For precise ranking, a bar chart is often clearer than either.
Related
- Excel Number Format Arrow: What It Does and How to Use It
- Excel Number Format Dropdown: What It Does and How to Use It
- Number Bullets in Excel: Add, Format, and Manage Lists
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Column Sparklines in Excel: A Step-by-Step How-ToTo insert column sparklines in Excel for Microsoft 365 on Windows, select the cells where you want the…
- Excel Pareto Chart Template: Build One Step by StepYes. In Excel for Microsoft 365 on Windows, a reusable Pareto chart template can be made from a…
- How to Edit an Org Chart in Excel: Steps and FixesTo edit an org chart in Excel, click a box and change its text, or select the chart…
- How to Change the Key on a Pie Chart in Excel Step by StepTo change the key, or legend labels, on a pie chart in Excel, edit the category names in…
- Excel Line Chart by Date: Make Dates Plot CorrectlyTo make an Excel line chart by date, put real Excel dates in one column and the values…
- Excel HR Dashboard Template: Metrics, Setup & TipsAn Excel HR dashboard template is a workbook that turns organized employee and workforce data into a concise…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.