How to Create a Bubble Chart in Excel

In investment banking, seeing and analyzing complex financial data is key. Bubble charts are a strong tool for comparing financial metrics and investments. These charts show data points as bubbles of different sizes, letting bankers quickly spot trends, outliers, and opportunities. By the end, you’ll really get how to use bubble charts to boost your financial analysis and decisions.

What is a Bubble Chart?

Before we dive into the nitty-gritty details, let’s make sure we’re on the same page about what a bubble chart really is.

A bubble chart is a visual tool that displays thre­e aspects of data at the same­ time. Each data point is represe­nted as a bubble, and the size­ of the bubble corresponds to a third variable­. In the finance industry, bubble charts are­ often used to compare companie­s or investments based on me­trics like market value, price­-to-earnings ratio, and year-to-date re­turns. 

Bubble charts offer seve­ral advantages in finance. They allow you to se­e multiple variables at once­, making it easier to identify re­lationships and patterns. The larger bubble­s for important data points make things clearer. Color coding can improve­ the chart’s readability and help distinguish be­tween differe­nt categories or sectors.

Understanding the Dataset

Got the basics down? Great, let’s peek under the hood at the dataset we’ll be working with.

For this blog post, we created a fictional datase­t designed for investme­nt bankers. The dataset include­s the following information about a range of companies:

• Company Name: Fictional companies like AxiorTech, ZenavaBio, etc.
• Sector: Diverse sectors, including Technology, Financials, Healthcare, Consumer Goods, Utilities, Materials, and Energy.
• Market Cap (B USD): Represented as the size of the bubble.
• P/E Ratio: Plotted on the X-axis to show company valuation.
• YTD Return (%): Plotted on the Y-axis to represent stock performance.

These­ metrics are crucial for investme­nt analysis, as they provide insights into a company’s size, valuation, and re­cent stock performance. By visualizing the­se metrics in a bubble chart, inve­stment bankers can quickly compare companie­s across sectors and identify potential inve­stment opportunities.

Download Excel Template

Preparing Your Data for the Bubble Chart

With our dataset at the ready, it’s time to whip it into shape for our bubble chart. Follow me!

Before creating the bubble chart, make sure your data is organized in Excel. Follow these steps to prepare your dataset:

1. Open a new Excel workbook.

2. Enter the column headers in the first row

• Company Name
• Sector
• Market Cap (B USD) 
• P/E Ratio
• YTD Return (%)

3. Fill in the data for each company, ensuring the information is accurate and relevant.

4. Double check your data for any errors or inconsistencies.

Tip: To maintain data integrity, consider using data validation techniques such as drop-down lists for the “Sector” column to avoid typos or inconsistent entries.

Creating a Bubble Chart in Excel

Data’s prepped and ready to go, so let’s jump into the fun part—creating our bubble chart!

Now that your data is ready, let’s get into how to make a bubble chart:

1. Select the data range, including the column headers.

2. Go to the “Insert” tab on the Excel ribbon and click on the “Insert Scatter (X, Y)” button.

3. Choose Bubble from the chart options.

4. Excel will automatically generate a basic bubble chart based on your data.

5. To adjust the chart’s data range, right-click the chart and pick “Select Data”. Make sure the correct data range is selected for the X, Y and bubble size values.

6. Customize the X-axis and Y-axis labels. To do this, right click on the respective axis and select “Format Axis”. Adjust the minimum and maximum values, tick mark spacing, and label positioning as needed.

7. To change the chart design, simply click on the chart and select from the Chart Designs.

8. To change the Chart Title, click on it and type your desired title.

9. To change the color scheme, click on the dropdown button on “Change Colors” and select your desired color.

That’s it! You’ve now created a customized bubble chart.

Key Considerations When Interpreting the Bubble Chart

To interpret the bubble chart, consider the following:

• The­ X-axis shows the price-to-earnings (P/E) ratio, which re­flects the valuation of each company. Companie­s positioned further to the right have­ higher valuations.
• The Y-axis repre­sents the year-to-date­ (YTD) returns, indicating the stock performance­ of each company. Firms higher up on the chart have­ achieved bette­r YTD returns.
• The size of e­ach bubble corresponds to the marke­t capitalization of the respective­ company. Larger bubbles repre­sent firms with higher market value­s.

Analyzing the Bubble Chart

Chart’s looking good! Now, let’s decode what it’s telling us and unearth the treasures hidden in our data.

With your bubble chart created and customized, it’s time to extract actionable insights. Here are some key aspects to consider when analyzing the chart:

1. Identifying overvalued and undervalued companies: Look for companies with high P/E ratios (further to the right) but low YTD returns (lower on the chart). These companies may be overvalued. Conversely, companies with low P/E ratios and high YTD returns may be undervalued and present potential investment opportunities.
2. Sector performance analysis: Observe the distribution of bubbles by color (sector). Are certain sectors clustered in specific areas of the chart? This can indicate sector-specific trends or performance patterns.
3. Relationship between market cap, P/E ratio, and YTD returns: Look for any apparent correlations between the size of the bubbles (market cap) and their position on the chart. Do larger companies tend to have higher or lower P/E ratios and YTD returns compared to smaller companies?

By asking these questions and exploring the relationships between the variables, investment bankers can gain valuable insights into market trends, company performance, and potential investment strategies.

Using Quick Charts by Macabacus

Creating charts natively in Excel can often be a time-consuming and complex task, especially for those frequently used in finance and consulting. With Macabacus, the Quick Charts feature streamlines the chart-creation process, enabling you to generate professional-quality charts with just a few mouse clicks.

When you start creating a new Quick Chart, you’ll see a preferences dialog box, which allows you to set your desired formatting, labeling, orientation, and sizing. Macabacus even remembers your preferences for future use, saving you precious time and effort.

Moreover, most Quick Charts come with in-worksheet options for further customization, allowing you to tweak the appearance to your liking even without Macabacus installed. Such a powerful automation tool simplifies chart creation, making it an indispensable asset for professionals looking to enhance their productivity.

Conclusion

Now, you’re ready to make bubble charts that tell your financial story and guide your investment strategies. Remember, it’s all about understanding your data, selecting the right metrics, and adhering to data preparation and design best practices. By getting really good at making bubble charts in Excel and using these tips, bankers and financial pros can step up their data visualization and make smarter investment and risk decisions.

Don’t hesitate to try different customizations to find what works best and clearly share your insights with clients and stakeholders. If you want to take your bubble charts to the next level, try Macabacus.

By sharpening your data visualization skills and applying what you’ve learned here, you’ll be ready to create bubble charts that not only inform but also motivate action. Whether you’re an experienced banker or a new financial analyst, we say go for it — use bubble charts to get deeper into your financial data and get better business results.

Happy chart making!