Creating a dual axis chart
In my opinion, charting in Excel is an art that needs to be practiced extensively to be an expert. As I demonstrate many examples in class I like to give additional info here for reference, for past, or future students. In this example I will show you how to create a dual axis chart using a small worksheet with some sample data. This may also be referred to as a Combo Chart in Excel. Our sample data is shown below.
To create the chart highlight the data that needs to be included in the chart itself. Remember do not select the Total row, as this will skew the chart layout. In the above example we will highlight the range A1:C8
Once highlighted from the INSERT menu select the Recommended Charts icon as shown here.
This will bring up an “Insert Chart” window with many different chart types to choose from. On this windows click on the “All Charts” tab at the top. We are going to use the “Combo” option at the bottom of the list on the left hand side.
In the window shown, see the bottom section called “Choose the chart type and axis for your data series”. Leave the Chart type as “Line” for our “% of Sales” and select the checkbox under Secondary Axis. This will create the axis on the right hand side of your Excel chart. See our example below.
Choose your secondary data series
Selecting the above options will generate the chart shown below. You can now adjust the size and placement of the chart on your Excel worksheet by dragging the chart to the desired location. To adjust the size you can click and drag the small circles in the corners and\or edges to increase or decrease the chart size.
Customize Your Chart
After your chart has been added there are many options for customization. Next, we are going to change the chart title and add a style to the chart next.
Update the chart title
To update the chart title I find that it is easiest to double click the chart title and then edit the text directly on the chart. In our example below I am going to update the chart title to “Product Sales Distribution”
In addition, after the chart title update I will update the chart style. To do this, I will click on the chart and find the new CHART TOOLS menu added to the ribbon in Excel. After that, under the CHART TOOLS menu, I click on the DESIGN tab and find the Chart Styles icon group. I personally like the dark color charts but you may choose your favorite. Our final dual axis chart is shown below for your reference.
In conclusion, as you can see from the steps above, it is not difficult to create a dual axis chart that shows two series of data. However, it is worth mentioning that even though this is a simple process you must make sure your data is accurate before calling any worksheet or chart as final or complete. In other words, if the data is wrong the chart will also be wrong.
Click here for more Excel Tips and Tricks. Please post any comments or questions below or if you would like to share your dual axis chart examples.