Working with Excel charts lets you present data in many different ways, but sometimes some data may looked squashed if the chart is also showing other data with much higher values. For example if you build a line chart showing Sales, Costs and Profits lines, the Sales and Costs data will show clearly but if the profit values are relatively small, the profit line may be difficult to see, particularly if you need to see variations in the line. Find out in this article how to add a second vertical axis in a line chart to sort out this kind of situation.

To show how to do this we'll first create a line chart in Excel based on a table with several sets of figures. Ensure one set of figures is much smaller than the other sets. Then create a regular line chart based on the table. You may need to change the data to be row or column based depending on the lines you want to display. The aim is to show the data lines with one line much smaller than the others. The next job is to turn on a secondary vertical axis.

The secret here is in the line selecting. To do this carefully hover over the chosen line in the chart and click it once only. The line should now show as selected, with sizing dots at each end of the line, and only on this line. If this does not look right, click off the chart and start again, until only the chosen line is selected. Then carefully right lick on the selected line. For Excel 2003 click the Axis tab, choose secondary axis and click Close. For Excel 2007/2010 choose Secondary Axis, and click Close. A new vertical axis appears at the right hand side of the chart with a scale automatically based on the values in the selected line, so now the line shows on the chart against this secondary axis, so it looks much larger than before.

You can also change the values in this secondary axis by formatting it. To do this we first click once on the numbers in the secondary axis to the right of the chart. In Excel 2003 you just click once anywhere in the numbers.

In Excel 2007/2010 you have to be more careful and click on an actual number in the axis. Then right click over the selection and choose Format Axis. Be particularly careful in Excel 2007/2010 that you do not right click slightly away from the number. If this happens you will not see Format Axis when you right click, so if this happens click off and start again. Once you choose Format Axis the Format Axis panel will appear. In Excel 2003 choose the Scale tab. In Excel 2007/2010 you'll see the scale details on the right in the panel. Make any changes you wish and close the panel. Now you'll see the revised scale on the secondary axis with the revised positioning of the data line.

You can also format the main vertical axis in your line chart in much the same way. So if you select a number in the main left hand vertical axis, right click and choose Format Axis, you can choose your own numbers for the scale. For example if the original scale was from 0 to 100 you could change this to 50 to 100 to visually emphasise how the lines vary on the displayed chart.

You cannot add a third vertical axis, should you wonder about this. If you select a different line on the original line chart and again choose Format Axis and select Secondary Axis, the secondary axis now shows values to suit the latest selected line, so the chart can only ever show two vertical axis. If you then want to return to the original secondary axis, again select this different line, choose Format Axis and select primary axis and complete, the revised secondary axis is removed and the original restored.

Interested in learning more about Excel charts or other Excel features? You might consider attending a training course and take your skills to the next level.