banner



How To Create A Bubble Chart In Excel 2016

This tutorial will demonstrate how to create a quadrant chart in all versions of Excel: 2007, 2010, 2013, 2016, and 2019.

Quadrant Chart – Free Template Download

Download our free Quadrant Chart Template for Excel.

Download Now

In its essence, a quadrant chart is a scatter plot with the background split into four equal sections (quadrants). The purpose of the quadrant chart is to group values into distinct categories based on your criteria—for instance, in PEST or SWOT analysis.

Unfortunately, the chart is not supported in Excel, meaning you will have to build it from scratch on your own. Check out the Chart Creator Add-in, a newbie-friendly tool for creating advanced Excel charts in just a few clicks.

In this step-by-step tutorial, you will learn how to plot this highly customizable Excel quadrant chart from the ground up:

How to create a quadrant chart in Excel

Getting Started

For illustration purposes, let's assume you have set your mind to track the performance of every marketing channel your high-end brand is using and separate the wheat from the chaff. What should be prioritized, and what should be cast aside?

In this case, the quadrants will split the chart into four areas, effectively grouping together the best- and worst-performing options to help you make well-informed decisions.

Here is a sample table showing the amount of money spent on each marketing channel along with the revenue it generated:

Quadrant chart original data

So, let's get started.

Step #1: Create an empty XY scatter chart.

Why empty? Because as experience shows, Excel may simply leave out some of the values when you plot an XY scatter chart. Building the chart from scratch ensures that nothing gets lost along the way.

  1. Click on any empty cell.
  2. Switch to the Insert tab.
  3. Click the "Insert Scatter (X, Y) or Bubble Chart."
  4. Choose "Scatter."

Create an empty XY scatter chart

Step #2: Add the values to the chart.

Once the empty chart appears, add the values from the table with your actual data.

Right-click on the chart area and choose "Select Data."

Select data

Another menu will come up. Under Legend Entries (Series), click the "Add" button.

How to insert data into an Excel chart

Next, do the following:

  1. For "Series X values," highlight all the values in column Expenses (column B).
  2. For "Series Y values," select all the values in column Revenue (column C).
  3. Select "OK" to set the series.
  4. Select "OK" again to close the dialog box.

Add the original table values to the chart

In addition, delete the gridlines by right-clicking on the chart element and choosing "Delete." And don't forget to change the chart title.

Step #3: Set the rigid minimum and maximum scale values of the horizontal axis.

You need to set the horizontal axis scale in stone as a means to prevent Excel from rescaling it—and shifting the chart around that way—when you alter your actual data.

To do that, right-click on the horizontal axis (the numbers along the bottom of the chart) and choose "Format Axis."

Modify the horizontal axis

In the task pane that pops up, do the following:

  1. Switch to the Axis Options tab.
  2. Set the Minimum Bounds value to zero.
  3. Change the Maximum Bounds value to the maximum number based on your data (in our case, that's 30,000).

Set the rigid minimum and maximum scale values of the horizontal axis

Step #4: Set the rigid minimum and maximum scale values of the vertical axis.

Without closing the pane, switch over to the vertical axis and repeat the steps outlined above.

Set the rigid minimum and maximum scale values of the vertical axis

Step #5: Create a new table for the quadrant lines.

Here comes the hard part. Having laid the groundwork, you now need to place four dots on each side of the chart to draw the accurate quadrant lines based off of the axis numbers.

To make it happen, set up the following table next to your actual data:

Create a table for the quadrant lines

While each element is pretty much self-explanatory, calculating the X and Y values for each category might sound complicated at first, but in reality, you will get through it in less than three minutes.

So how do you figure out your values? Just follow the instructions below:

  1. For Horizontal line (left):
    1. Set the X value to zero.
    2. Set the Y value to half of the vertical axis Maximum Bounds value, which you have previously set (140,000/2=70,000).
  2. For Horizontal line (right):
    1. Set the X value to the horizontal axis Maximum Bounds value (30,000).
    2. Set the Y value to half of the vertical axis Maximum Bounds value (140,000/2=70,000).
  3. For Vertical line (bottom):
    1. Set the X value to half of the horizontal axis Maximum Bounds value (30,000/2=15,000).
    2. Set the Y value to zero.
  4. For Vertical line (top):
    1. Set the X value to half of the horizontal axis Maximum Bounds value (30,000/2=15,000).
    2. Set the Y value to the vertical axis Maximum Bounds value (140,000).

Here is how it looks:

Fill the helper table

Step #6: Add the quadrant lines to the chart.

Once you have set up the table, it's time to move the values to the chart.

Right-click on the chart, choose "Select Data," and click "Add" in the window that appears.

First, let's add the horizontal quadrant line.

Click the "Series X values" field and select the first two values from column X Value (F2:F3). Move down to the "Series Y values" field, select the first two values from column Y Value (G2:G3). Under "Series name," type Horizontal line. When finished, click "OK."

Add the horizontal quadrant line

Next, tackle the vertical quadrant line. Click the "Add" button again and move the remaining data to the chart.

Click the "Series X values" field, select the remaining two values from column X Value (F4:F5). Move down to the "Series Y values" field and select the remaining two values from column Y Value (G4:G5). Under "Series name," type Vertical line. When finished, click "OK."

Add the vertical quadrant line

Step #7: Change the chart type of the newly-added elements.

The worst is behind us. Now it's time to connect the dots.

Right-click on any of the four dots and pick "Change Series Chart Type" from the menu.

Change the chart type of the newly-added elements

In the "Chart Type" dropdown menu next to the two series representing the quadrant data ("Vertical line" and "Horizontal line"), choose "Scatter with Smooth Lines."

Scatter with Smooth Lines

Step #8: Modify the quadrant lines.

Let's slightly alter the quadrant lines to make them fit the chart style.

Right-click on the horizontal quadrant line and choose "Format Data Series."

Modify the quadrant line

Once the task pane pops up, do the following:

  1. Switch to the Fill & Line tab.
  2. Click the "Fill Color" icon to pick your color for the line.
  3. Set the Width value to 1 pt.

Format the horizontal quadrant line

Repeat the same process for the vertical quadrant line.

Step #9: Add the default data labels.

We're almost done. It's time to add the data labels to the chart.

Right-click any data marker (any dot) and click "Add Data Labels."

Add the default data labels

Step #10: Replace the default data labels with custom ones.

Link the dots on the chart to the corresponding marketing channel names.

To do that, right-click on any label and select "Format Data Labels."

Format data labels in Excel

In the task pane that comes up, do the following:

  1. Navigate to the Label Options tab.
  2. Check the "Value From Cells" box.
  3. Select all the names from column A.
  4. Click "OK."
  5. Uncheck the "Y Value" box.
  6. Under Label Position, select "Above."

Replace the default data labels with custom ones

You can customize the labels by playing with the font size, type, and color under Home > Font.

Step #11: Add the axis titles.

As a final adjustment, add the axis titles to the chart.

  1. Select the chart.
  2. Go to the Design tab.
  3. Choose "Add Chart Element."
  4. Click "Axis Titles."
  5. Pick both "Primary Horizontal" and "Primary Vertical."

Add the axis titles

Change the axis titles to fit your chart, and you're all set.

Quadrant chart in Excel

And that is how you harness the power of Excel quadrant charts!

Download Quadrant Chart Template

Download our free Quadrant Chart Template for Excel.

Download Now

How To Create A Bubble Chart In Excel 2016

Source: https://www.automateexcel.com/charts/quadrant-template

Posted by: cliftonhowles1979.blogspot.com

0 Response to "How To Create A Bubble Chart In Excel 2016"

Post a Comment

Iklan Atas Artikel

Iklan Tengah Artikel 1

Iklan Tengah Artikel 2

Iklan Bawah Artikel