Creating a Two Axis Chart in Excel 2007 / Excel 2010 / Excel 2013

Up to Microsoft Excel 2003, there was a in-built Custom Chart Type called the 2-Axis Chart. This was a pretty useful chart type, which I used to display very small numbers and very large numbers – all on the same chart. But it magically disappeared from Excel 2007, Excel 2010 & even Excel 2013.

2 Axis Chart in Excel - Final

2 Axis Chart in Microsoft Excel

What is a 2 Axis Chart?

The default Bar Chart of Excel has One X Axis (The Horizontal side), and One Y Axis (represented Vertically). This is useful most of the time.

However, once in a while, when you want to display 2 different sets of numbers in the same chart, and one set of numbers (Quantity) comprises of very small numbers, and the other set of numbers (Sales figures) are very large –  in Thousands.

If you plot a normal bar chart, the Sales figures are so high that the bars are quite long, and the scale is set in Thousands. So  the Quantity figures are too small to be even seen in the Bar Chart.

A 2 axis chart is slightly different from a normal, standard chart. It uses 2 Y Axis in the same chart. The one on the Left of the chart is used to display one scale (Small Numbers), and another Y Axis is added on the Right Side, which represents the (Large Numbers) in Thousands.

In this way, both small numbers and very large numbers are seen in the same chart – using both the Primary Axis and the Secondary Axis.

However, do note that the Bars of the Chart will overlap each other, and it will be difficult to see one set of values. To overcome this situation, we convert one of the bars into a Line Chart. Since the scale is different, both can be seen, and in this way, a useful 2 Axis chart is created easily in Microsoft Excel.

If you wish to make a 2 Axis Chart in Microsoft Excel 2007 or Excel 2010 or Excel 2013,  just follow this  fairly simple process:

  1. Select the Data to be plotted.You can use this Example Worksheet to practice creating the 2 Axis Chart.
  2. Click Insert > Column > 2-D Column Chart. You will get a normal Bar Chart in Excel.
  3. Click on one of the bar charts. The entire series gets selected.
  4. Right Click, and select Format Data Series.
  5. From the Format Data Series Popup Menu, Choose Secondary Axis.
  6. You will see that the selected data series has moved to the Secondary Axis.
  7. Format the chart to your liking.

You can have a beautiful looking chart, displaying data on 2 axis – the Y axis is listed on both sides. And it hardly takes a minute to build… Enjoy!

Additional Useful Tips

Anything else you’d like covered here? Do post a comment and let me know.

Hope you enjoyed this Excel Tip. If you would like some more tips on Charts, Pivot Tables techniques, simply Subscribe to the ExcelChamp Weekly Excel Tips Newsletter.

Cheers,
Vinai Prakash
Founder ExcelChamp.Net

3 thoughts on “Creating a Two Axis Chart in Excel 2007 / Excel 2010 / Excel 2013

  1. Linda J Burton

    I was perfectly happy creating 2 axis charts in E 2003 – why the heck did you have to change it? Ever since our IT department installed 07/10 on our computers, it’s been nothing short of a pitched battle to get what I want to illustrate on the screen. This is truly NOT an improvement.

    Reply
    1. Vinai Prakash Post author

      Sorry Linda. We use this great tool from Microsoft to solve our daily problems… and have to live with some quirks in the software, and the changes that Microsoft makes in each version, many are good, but some are not so good too :(

      Reply
  2. K R Muraly

    I tried for 2 axis chart, but I am not able to get the Format Data Series Popup Menu, which is not showing while right click. Please advise.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>