Meraz Al Nahian
The article will show you how to create an Excel Bubble Chart for categorical data. Bubble Charts are useful when you work with more than two variables in an Excel sheet. It represents the data in the form of bubbles of different sizes. Suppose you have two data: 10 and 5 units of measurement. When you put them in the Bubble Chart, you will see the bubble for the 10 unit measurement is twice the size of the bubble for the 5 unit. You can also categorize the bubbles in the chart. Let’s follow the next sections of this article on how we can create an Excel Bubble Chart for categorical data.
Download Practice Workbook
6 Easy Steps to Create Bubble Chart for Categorical Data in Excel
Suppose a businessman wants to sell some products in different regions of the country and he picks some dates, the quality of the product, and the amount of product he targets to sell on corresponding dates. The data looks like the following picture.
We are going to show you how you can represent the Target Sales amount in the Bubble Chart with categories in the following parts of this article.
Step-1: Inserting Necessary Parameters
First of all, we need to insert the necessary parameters to create the Bubble Chart. Please follow the instructions below.
- First, make some columns to insert the parameters.
The X and Y Values refer to the position of the corresponding Bubble. For example, the bubble that represents 20 target sales in New York on the 6th October, 2021 will be on the (1, 1) point of the chart. You can vary the values to change the location of the bubble in the chart if you want. The purpose of having the Helper column will be explained later.
Read More: How to Use Excel IF Function with Range of Values
Step-2: Creating Time Category
Here, we will make a time category for the Bubble Chart. The period of a year will be divided into 4 intervals. Just stick to the procedure below.
- First, create a column to store the time category and type the following formula in cell E5, and press ENTER. You will see the year and corresponding quarter of the period for the date in C5.
=IF(C5="","","Q"&ROUNDUP(MONTH(C5)/3,0)&" "&YEAR(C5))
The formula uses the IF, ROUNDUP, MONTH, and YEAR functions to divide the date in C5 into its corresponding quarter and year. The MONTH and YEAR functions return the month and year of a date, so here it will return 10 for the 6th October and 2021 for the year. Dividing 10 by 3 and rounding up its decimal value will give us the quarter.
- After that, use the Fill Handle to AutoFill the lower cells.
Read More: How to Use Multiple IF Statements with Text in Excel (6 Quick Methods)
Step-3: Creating Bubble Chart
In this step, we finally create the bubble chart. You need to go through the following description for that purpose.
- First, select the range F4:H11 and then go to Insert >> Scatter Chart >> Bubble Chart.
After that, the Bubble Chart will appear.
Step-4: Formatting the Bubble Chart
In this step, we will format our Bubble Chart to make it look convenient. Please follow the instructions below.
- First, click on the Plus icon of the chart and uncheck the Gridlines and Chart Title.
- Next, right-click on the Horizontal Axis and then select Format Axis.
- In the Format Axis window, change the Bounds Minimum to 0 and Maximum to 8. The reason for choosing this interval is that we have 7 data entries.
- Similarly, you can change the Y Axis Right-click on the Y Axis and then select Format Axis. Change the Bounds as we did previously.
- Finally, we will get a nice and clean Bubble Chart.
Step-5: Creating a Category for Data Label
We will also create a category for Data Labeling. Let’s go through the process below.
- First, create a column to store the region and quality together and type the following formula in cell C14, and press ENTER. Also copy the Time Category range and Paste it as values from F14 to F20. There is another column for the bubble size which will be applied later.
=CONCAT(B5, ", ",D5)
The formula uses the CONCAT function that joins the texts in B5 and D5 with a comma.
- After that, use the Fill Handle to AutoFill the lower cells.
Read More: How to Write Greater Than or Equal To in Excel IF Function
Step-6: Adding Categories in the Bubble Chart
In this step, you will learn the process of inserting the categories in the Bubble Chart. The following description will lead you to the path of solution.
- First, click on the Plus icon of the chart and select Data Labels >> More Options…
- Next, check the Value From Cells option from the Format Data Labels
- In the Data Label Range dialog box, select the range C14:C20.
- After that, click OK.
- Thereafter, uncheck Y Value from the Format Data Labels
- Later, you will see the region and quality categories added to the Bubble Chart.
- Now, we will add the Time Category in the Horizontal Axis. For that reason, right-click anywhere in the Plot Area and select Select Data.
- After that, select Add from the Select Data Source
- Thereafter, we fill up the Edit Series dialog box with the following ranges.
- First, give this series a name. Here, I named this series Category 2.
- Next, the range for the X values is F5:F11.
- Here, we want the Time Category on the Horizontal Axis, so we made a range of zeros in I5:I11 and selected this for the Y values.
- Later, we select a range of ones for the bubble size (F14:F20).
- Finally, click OK.
You will see 7 bubbles of the same size on the Horizontal Axis after this operation.
- After that, right-click on any of the new bubbles and select Format Data Labels… Make sure all the small bubbles get selected.
- We are going to add the Time Category from the Format Data Labels. Open it and select Value From Cells…
- Next, select E14:E20 for labeling which is the Time Category range in the Data Label Range dialog box.
- Thereafter, click OK.
- In addition, uncheck the Y Value and Show Leader Lines options for convenience.
- Also, select Below for Label Position.
- Finally, your Bubble Chart is complete with categorical data. Spread the chart so that the bubbles and Axis Labels don’t overlap. You may change the font size of the text if that suits you the best.
Thus you can create an Excel Bubble Chart for categorical data.
Note:
There’s an option for you to scale the bubble size. To change the scale,
- Right-click on any of the bubbles and select Format Data Series…
- Next, scale the bubble size to your choice. Here, I set it to 81.
After that, your Bubble Chart becomes more understandable
- In addition, you can also separate the region by color. For that purpose, select a bubble and right-click on it.
- After that, select Fill and choose any color.
By following this approach, your bubble chart will become more fascinating and mind blowing.
Read More: How to Use Multiple IF Statements with Text in Excel (6 Quick Methods)
Practice Section
Here, I’m giving you the dataset of this article so that you can practice these methods on your own.
Conclusion
In the end, we can conclude that you will learn how to create an Excel Bubble Chart for categorical data after reading this article. If you have any better suggestions or questions or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles. For more queries, kindly visit our website ExcelDemy.
