Excel Chart Vertical Axis Text Labels

In this tutorial we’re going to look at how we can twist Excel’s arm into putting text labels on the vertical axis of a chart.

This was inspired by a question I received a while back from Juan Gabriel Aguero, who asked if we could re-create this chart in Excel:

While the answer is yes, unfortunately there’s no built in way to achieve this in Excel so we have to trick it. I call it the Sneaky Bar Chart Technique.

No charts were injured in the making of this tutorial.

Download the Workbook

Video Instructions

Written Instructions

Step 1: Line Chart

Create your line chart:

Note how the vertical axis has 0 to 5, this is because I've used these values to map to the text axis labels as you can see in the Excel workbook if you've downloaded it.

Step 2: Sneaky Bar Chart

Now comes the Sneaky Bar Chart; we know that a bar chart has text labels on the vertical axis like this:

So all we need to do is get that bar chart into our line chart, align the labels to the line chart and then hide the bars.

We’ll do this with a dummy series:

Copy cells G4:H10 (note row 5 is intentionally blank) > CTRL+C to copy the cells > select the chart > CTRL+V to paste the dummy data into the chart.

You can see the horizontal axis has gone awry and we have the new (Dummy) series in the chart:

To fix it: select the dummy series line in the chart > Right-click > Change Series Chart Type. Choose a Bar Chart.

This will switch the dummy series to the secondary axis and you should have 3 axes displayed, but wait, you need more! The one axis we really want, the bar chart vertical axis, is missing:

To turn on the secondary vertical axis select the chart:

Excel 2010: Chart Tools: Layout Tab > Axes > Secondary Vertical Axis > Show default axis

Excel 2013: Chart Tools: Design Tab > Add Chart Element > Axes > Secondary Vertical

Now your chart should look something like this with an axis on every side:

Let’s cull some of those axes and format the chart:

1. Click on the top horizontal axis and delete it.
2. Hide the left hand vertical axis: right-click the axis (or double click if you have Excel 2010/13) > Format Axis > Axis Options:
   • Set tick marks and axis labels to None
   • While you’re there set the Minimum to 0, the Maximum to 5, and the Major unit to 1. This is to suit the minimum/maximum values in your line chart.
    

   

3. Now move the secondary vertical axis to the left hand side: right-click the axis (or double click if you have Excel 2010/13) > Format Axis > Axis Options:
   • a. Major tick mark: None
   • b. Axis Labels: Low
   • c. Position on axis: On tick marks
   • d. Then go to the Line Color tab: No Line
    

   

Ok, we’re nearly there. Your chart should look like this:

4. Let’s delete the legend - click on it and press the delete key.
5. Now hide the bars – select them > Format tab > Shape Fill > No Fill. Note how the bars have a value of 1. This is just so I can select them and format them with No Fill so they don’t actually display.

Almost done:

6. Lastly, move your chart plot area over and add a text box with labels for your lines and get rid of the tick marks on the horizontal axis:
 

Note: I didn’t have the original data for Juan's chart so I’ve recreated by eye and as a result the lines in my chart are slightly different to Juan’s, but the intention for this tutorial was to demonstrate how to display text labels in the vertical axis.

Thanks

Thanks to Juan for asking this question, and to Jon Peltier who wrote about using this solution with a column chart way back in 2010.

Please Share

If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.