Make an Excel Bubble Chart Use Probability Values on the X-Axis
Question details
The user needs an Excel bubble chart to use Probability values as the horizontal X-axis instead of row numbers or ID values.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Creating a probability-versus-consequence bubble chart, such as a risk matrix visualization.
- Observed behavior
- The horizontal axis displays values from 1 to 8 based on row positions or IDs rather than the Probability range from 1 to 5.
Confirm that Probability, Consequence, and Bubble Size are stored in separate numeric columns with matching rows. Select only the data rows for the chart and exclude header cells when assigning series ranges.
Edit the Bubble Chart Series to Use Probability as X Values
Use this method when the bubble chart already exists but its horizontal axis is showing IDs, row numbers, or another incorrect range.
A bubble chart is an XY chart, so its X-axis must be assigned from the series X values. The ID column should not be used as the X range; use it only for labels if you need to identify each bubble.
Click the existing bubble chart, then right-click it and choose Select Data.
In the Select Data Source dialog, select the plotted series under Legend Entries (Series), then click Edit.
In Edit Series, enter or select the cells in the Probability column in the Series X values box. Do not select the ID column or row-number cells.
Select the Consequence column for Series Y values and select the numeric Bubble Size range for Series bubble size. Each selected range must contain the same number of data points.
Click OK to close the dialogs. The horizontal axis should now scale according to the Probability values, such as 1 through 5.
Create a New XY Bubble Chart from the Correct Columns
Use this method when Excel created a category chart or when editing the existing series is more difficult than rebuilding the chart.
Use WPS Spreadsheets to Plot Probability on a Bubble Chart X-Axis
WPS Spreadsheets lets you create and edit XY bubble charts with Probability assigned as the X-value range, Consequence as the Y-value range, and a separate column for bubble sizes. You can also keep IDs out of the axis and display them as labels instead.
- 1. Prepare the chart data: In WPS Spreadsheets, place Probability, Consequence, and Bubble Size in separate numeric columns.
- 2. Insert a bubble chart: Select the three numeric columns, then choose Insert > Chart > Bubble.
- 3. Open the series editor: Click the chart and use Chart Tools > Select Data, then select the series and choose Edit.
- 4. Assign the Probability cells: Set the Probability column in X Values, set Consequence in Y Values, and select the Bubble Size column for bubble sizes.
- 5. Label bubbles with IDs: Select the bubble series, open Chart Elements > Data Labels, and choose cell values from the ID column if identifiers are needed.
Frequently Asked Questions
Why does my Excel bubble chart show 1 to 8 on the X-axis?
Excel is likely using category positions, row numbers, or an ID column instead of numeric X values. Right-click the chart, choose Select Data, edit the series, and set Series X values to the Probability cells.
Can I use an ID column as the X-axis in a bubble chart?
You can, but IDs usually do not represent the intended numeric position. For a probability chart, assign the Probability column to Series X values and show IDs with data labels instead.
What chart type should I use for Probability and Consequence data?
Use an XY Scatter chart or a Bubble chart. A Bubble chart is appropriate when you also have a third numeric measure that should control each bubble's size.
Do the X, Y, and bubble-size ranges need the same number of cells?
Yes. The Probability, Consequence, and Bubble Size ranges must contain the same number of corresponding data rows, or Excel may omit points or return an invalid reference error.
How can I display names or IDs next to bubbles without changing the axis?
Select the bubble series, add Data Labels, then open More Options and choose Value From Cells. Select the cells containing the names or IDs while leaving the Probability range assigned to the X-axis.
