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.

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!


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

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!

Not Ascending… Not Descending… Just Plain Custom Sorting in Excel

It is pretty easy to sort data in Excel.

The sorting option is available on the Home Tab of Excel 2007 or Excel 2010. See image below from Excel 2010.

Home Tab on Excel 2010

Just select the data, and click on the Sort & Filter Button on the Home Tab.

Sorting Menu in Excel 2010

You can sort A to Z (Ascending order) of the selected column, which is the default Sorting option in Excel 2003, 2007 & Excel 2010 too.

But you could also sort in the Descending Order first (Sort Z to A), just by selecting this option from the Sort button on the Home Tab.

Between these two Ascending & Descending options, you can get almost all kinds of data sorted.

However, you will come across situations, when neither of these options will give you the desired result. This happens when you want to sort in a particular fashion – which is neither ascending nor descending.

What do you do then?

Here’s where Custom Sort comes into play. This hidden gem has been available in Excel since ages (a.k.a. since Excel 95, 98… days). However, most people are stumped when it comes to using it.

Say for example, that Department wise Spending are provided in an Excel file below.

Sorting Unsorted Data in Excel

You want to sort on departments, and you want them in this order listed below.

  1. Sales
  2. Marketing
  3. Service
  4. Human Resources
  5. Accounts
  6. Information Technology

Clearly, this can’t be done using Ascending Sort or Descending Sort.

Custom Sort comes to the rescue.

Go to Sort & Filter button on the Home Tab of Excel 2010. Click on Custom Sort from the drop down menu. A new popup window will open.

Custom Sort Popup in Excel 2010

If your data has headings, you can select the check box “My data has headers“. If you do not have column heading, then do not choose the “My Data has headers” option.

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!


In the Sort by Column dropdown, choose Department as the option to sort on.

In the Order dropdown, choose “Custom List…“.

Custom List Popup in Excel 2010

Click on NEW LIST, and press Add.

The cursor will move to the Empty Box in the popup. Key in the list of departments here – one per line, in the SORT order you need. Then Click OK.

List of Sort Order Entries in Custom Sort in Excel 2010

Click OK on the Custom List Popup, and close all pop ups.

You will now notice that the list of Departments has been sorted, as per your specified order – which was neither ascending nor descending.

List of Departments Sorted in Custom order in Excel 2010

As you can see, using the Custom Sort is pretty easy. In fact, Microsoft Excel has been using this trick to sort Months, Weekdays, Quarters etc. since ages too. It’s time you learnt about this nifty technique too.

Do try it out, and let me know about your experience with Custom Sort. I’ll be glad to receive your comments and suggestions.

Additional Resources:

Cheers,
Vinai Prakash
Founder & Editor, ExcelChamp.Net

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!

Extracting Digits from Serial Numbers in Excel

What do you make of 12CNGY561RR9806?

Chances are that you’d be thinking this some kind of a joke… Well, it is a code, a serial number. If you open your Toaster, Phone, TV, Laptop or Tablet, you’d find a similar looking, equally difficult to read serial number.

You may be wondering – Why do they make it so difficult? And what is the purpose?

Well, these are serial numbers. Not for humans, more for computers and geeks. If you happen to take your product  to the service centre, the bar code scanner can make sense of this gobbledegook right away!

To make sense, you need to be able to extract certain bits and pieces from this serial number, and analyze them separately.

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!


For example, the first  two characters (12) may mean the year of manufacture – 2012.

The next 2 characters (CN) may mean the country of manufacture- China.

The next 4 characters (CNGY) may mean the product code. Similarly, the last 4 digits (9806) may be the runing serial number of the product.

From such numbers, it is easy to find out the country, batch, make, product, and date of manufacture quite quickly.

But imagine staring a such numbers in an Excel file, and be able to quickly filter, find and select numbers belonging to a specific year, country or Product family.

Well, it can easily be done, using special in built functions in Excel – be it Microsoft Excel 2003, 2007 or Excel 2010 or even Microsoft Excel 2013.

See this Example Excel file for trying it yourself.

To solve this problem, we will use Excel’s inbuilt Text Functions – Left, Right and Mid.

The Left function will extract any characters from the left of a string.  LEFT(‘ABCDEF’, 2) will extract AB.

The Right function will extract any number of characters you need, from the right of a string. RIGHT(‘ABCDEF’, 3) will extract DEF.

And the Mid function will extract any characters from the middle of a string. You just have to specify the starting number, and the number of digits required.

So, MID(‘ABCDEFGH’, 3, 2) begins to extract from the Third character, and extracts 2 characters. Therefore, the characters, “CD” get extracted easily.

Using these 3 simple functions, we can extract any digit, or character from any string in Excel. And this feature has been in Excel since ages…

Enjoy, and all the best!

Do post some comments if this article helped you!

Additional Resources:

Cheers,
Vinai Prakash
Founder & Editor, ExcelChamp.Net

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!