Future-Proof Your Career, Master Data Skills + AI

Blog

Future-Proof Your Career, Master Data Skills + AI

Blog

How to Create a Stacked Column Chart with Two Y-Axes in Excel

by | 6:24 pm EDT | August 30, 2023 | Excel

Making a stacked column chart with two y-axes in Excel helps you see your data better. It’s super useful when you have different data that need different scales.

The cool part is you can put lots of data on one chart, so it’s easy to see how things relate. Excel makes it look nice and helps you show off your data in a clear way. Your charts will totally impress everyone!

To create a stacked column chart with two Y-axes, you can:

  1. Prepare the data table.
  2. Select the entire data table, including headings.
  3. Create a stacked column chart.
  4. Select the data series that you want to move to the secondary vertical axis and right-click on it.
  5. Select “Format Data Series”.
  6. From the “Format Data Series” dialog box, select the “Secondary Axis” option.

Mastering the skill of making stacked column charts with two Y-axes can be really useful for understanding and presenting data. Excel makes this process easy, so anyone, regardless of their experience, can do it.

Let’s learn how to do it and use this graph to make our data more interesting and informative.

How to Create a Stacked Column Chart with Two Y-Axes in Excel

What are Stacked Column Charts

Stacked column charts help you to do the following:

  • Compare parts of a whole
  • Compare how parts of a whole change over time or across the categories

Take a look at the graph below:

Excel chart - Stacked bar chart

The graph lets you compare how much Product A and Product B are sold monthly. Plus, you can also see how the sales of both products change as time goes on.

In the above graph, there is only one x-axis and one y-axis.

Adding a secondary axis to a stacked column graph is useful when you have two sets of data that are measured in different units or not on the same scale.

It helps prevent one set of data from overwhelming or distorting the other. This way, you avoid any confusion and present your data more effectively.

You can create a stacked column graph with two Y-axes (dual axis chart) in Excel in the following two ways.

  1. Adding a secondary axis after creating a basic stacked column graph
  2. Creating a combo chart

Let’s see each method in detail way.

1) Adding a Secondary Axis After Creating a Basic Stacked Column Chart

This method is quite useful when you have already built a stacked column graph and you want separate primary and secondary columns of your graph for better data presentation.

For example, in the below table, you can see the monthly sales value and monthly sales quantity.

Data Table for a stacked column graph

If you create a basic stacked column graph for the above data values, it will look like this.

Stacked column graph example

As you can see, quantities and values are not on the same scale, it is difficult to see the patterns of quantity.

Further, sales values are measured in dollars ($) and quantities are measured in units. So, you can’t use a common axis title.

However, you can create a stacked column graph where sales values are shown on the primary y-axis and the sales quantities are shown on the secondary axis.

Let’s break the process down into the following 4 steps:

  1. Creating a Basic Stacked Column Chart
  2. Adding a Secondary Axis
  3. Fine-Tuning the Chart
  4. Adjusting Number Formats and Units

1) How to Create a Basic Stacked Column Chart

You can follow the steps given below to create a chart with two-axis titles:

Step 1

Arrange your data table as follows. You have to move secondary axis data to a new row.

Arrange the data table
Step 2

Select the range of cells containing your data, including the headers.

Select the data range
Step 3

Go to the Insert tab in the Excel ribbon.

Go to the "Insert" tab
Step 4

Click on the Column or Bar Chart icon in the Charts group.

Click on the Column or Bar Chart icon
Step 5

Choose the Stacked Column from the options available.

Choose Stacked Column

Then, you’ll get a stacked column chart like the one below.

Stacked Column - Example

2) How to Add a Secondary Axis

Next, you have to move quantities to the secondary axis. You can do it by following the steps given below:

Step 1

Select one of the quantity legends and right-click on it.

Right-click on one of the legends that you want to move to the secondary axis.
Step 2

Select “Format Data Series…” from the right-click menu.

Opening the "Format Data Series" Window
Step 3

Select “Secondary Axis” from the series options.

Select "Secondary Axis" from the series options.
Step 4

Repeat the above three steps for all the data that you want to move out from the primary y-axis.

Then, you’ll get the stacked column chart with two y-axes as shown below.

Stacked column graph

Your next step is to fine-tune the chart

3) How to Fine-Tune The Chart

After creating a stacked column chart with two y-axes in Excel, you can fine-tune it to make it more visually appealing and informative.

One aspect to consider is selecting the appropriate data to display on each axis, so the information is clearly conveyed to your audience.

To select data, click on your chart, and then click on the “Select Data” button that appears in the “Design” tab under “Chart Tools.”

From there, you can add, edit, or remove data series plotted on each axis.

Chart elements are another important factor in fine-tuning your chart.

Chart elements

Adding or customizing elements such as axis labels, titles, gridlines, and legends can significantly improve the readability of your chart.

To access and modify chart elements, click on the “+” icon next to your chart or right-click the chart and choose “Add Chart Element.”

Choose the element you want to add or modify and use the options available to customize its appearance.

You can double-click on the chart elements of your graph and open the format window of each chart element.

You can also use the “Format” tab and use the dropdown menu from the “Current Selection” group.

Selecting chart elements

4) How to Adjust Number Formats and Units

To create a stacked column chart with two y-axes in Excel, it is essential to adjust the numeric formats and units for clarity and consistency.

This section guides on customizing these settings to improve the chart’s readability.

First, it is critical to adjust the number format. Excel offers different options for formatting numbers, such as currency, percentage, or scientific notation.

To access these options, right-click on the axis you want to change, select “Format Axis,” and expand the “Number” category.

From here, choose a format that best suits your data and ensures easy understanding for the viewers.

Adjusting number format of bar charts axis values

In some cases, it might be necessary to modify the numerical units.

For instance, if dealing with large values (e.g., thousands or millions), consider using a base unit like “K” for thousands or “M” for millions. Changing the base unit simplifies the chart while retaining the necessary information.

In the “Format Axis” option, you can do this under the “Display units” drop-down menu; select the appropriate unit and apply the changes.

Chart display units dropdown menu

Another crucial aspect is adjusting the bottom value of the axis to create a meaningful starting point.

In a stacked column chart, the bottom value is often set to zero to provide a clear and accurate representation of the data.

To customize this value, navigate to the “Format Axis” panel, and under the “Axis Options,” set the “Minimum” value accordingly.

Lastly, using distinct colors for each data series helps differentiate them visually.

For example, you can consider using blue for one series and another contrasting color for the other series.

To change the color of a series, click on the specific data series to select it, and then right-click to choose the “Format Data Series” option. Under “Fill & Line,” select the desired color from the “Fill” options.

After you format your graph with a primary axis and a secondary y-axis, it is more clear for the user.

Clustered stacked columns with primary vertical axis and a secondary vertical axis

2) How to Create a Combo Chart

Sometimes you may get a data set that has different units of measurement. In such situations, consider using a stacked column chart along with another chart type.

For example, in the below data set, you have sales values and overall gross profit percentages for each month.

Data set

Now, let’s see how to create a stack column graph for sales and show the gross profit percentages in the secondary axes.

Step 1

Select the data table including headers.

Select the entire data table.

Step 2

Go to the “Insert tab”.

Go to the "Insert" tab

Step 3

Click on the “Combo charts” icon from the charts group.

Click on the Combo charts icon

Step 4

Select “Create Custom combo chart…”.

Select "Create Custom combo chart..."

Then, you’ll see the “Insert chart” dialog box.

The "Insert chart" dialog box

Step 5

Select chart types for each data series.

Combination chart - Selecting chart type

Step 6

Check the boxes of data series that you want to show in the secondary axis.

Combo chart - Secondary Axis

Step 7

Finally, click the OK button.

Now, you’ll get stacked column and line charts in one chart.

Combo chart

Learn filtering and joining data in Excel by watching the following video:

Final Thoughts

Making a stacked column chart with two Y-axes in Excel lets you compare two different sets of data using a picture.

You can either add the secondary axes after creating a stacked column graph or you can directly create a combo chart.

It’s important to set the Y-axes correctly by changing settings in “Format Axis.”

Remember to make things clearer by giving your chart a title, labeling the axes, and adding number labels. Also, consider changing colors and lines to make it easier to understand.

Always double-check your chart to make sure it shows the right information. This method helps you show data well, but you must be careful to avoid misunderstanding.

Frequently Asked Questions

In this section, you will find some frequently asked questions you may have when creating a stacked column chart in Excel.

How do I add a second Y-axis in Excel for a stacked column chart?

To add a second Y-axis in Excel for a stacked column chart, follow these steps:

  1. Select the chart to open the Chart Tools.
  2. Click on “Design” and then “Change Chart Type.”
  3. Choose “Combo” and then “Cluster Column – Line on Secondary Axis.”
  4. Select “Secondary Axis” for the data series you want to show on the second Y-axis.
  5. Ensure that the selected series is displayed as a line chart.
  6. Click “OK” to apply the changes.

What are the steps for creating a dual-axis line chart in Excel?

To create a dual-axis line chart in Excel, follow these steps:

  1. Create your chart as usual with one data series.
  2. Select the chart to open Chart Tools.
  3. Click on “Design” and then “Select Data.”
  4. Click on “Add” to add a new data series.
  5. Select the new data series and then click on “Format Data Series.”
  6. In the “Format Data Series” pane, under “Series Options,” select “Secondary Axis.”
  7. Click “Close” to finish.

Is it possible to have two Y-axes in a Google Sheets graph?

Yes, you can have two Y-axes in a Google Sheets graph. To do this, follow these steps:

  1. Create your chart as usual with one data series.
  2. Select the chart and click on the 3 vertical dots in the top right corner.
  3. Click on “Edit chart.”
  4. Under “Customize,” select “Series.”
  5. Scroll down and choose “Apply to: Series 2.”
  6. Check the box that says “Use a secondary axis.”
  7. Adjust the chart options to your liking and click “Close” to finish.

What is the process for creating a stacked clustered column chart with two Y-axes?

Creating a stacked clustered column chart with two Y-axes can be complex.

You may need to create a combination chart of a clustered column chart and a stacked column chart, or utilize third-party tools and add-ins that can provide this specialized chart type.

Can you explain how to add a secondary axis in Excel on a Mac?

To add a secondary axis in Excel on a Mac, follow these steps:

  1. Select your chart to reveal the Chart Design tab.
  2. Click on “Change Chart Type” in the Chart Design tab.
  3. Select “Combo” from the list of chart types.
  4. Choose the desired secondary axis chart type for the data series that you want to display on the secondary axis.
  5. Check the box that says “Secondary Axis” for the chosen data series.
  6. Click “OK” to apply the changes.

Related Posts