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
Enter your email address below to download the sample workbook.
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:
- Click on the top horizontal axis and delete it.
- 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.
- 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:
- Let’s delete the legend - click on it and press the delete key.
- 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.
- 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 to Juan for asking this question, and to Jon Peltier who wrote about using this solution with a column chart way back in 2010.
I just wrote a long response on MrExcel.com asking for text labels on the Y-axis of a chart. I totally forgot that I had written my 2010 post which you’ve cited here.
Next year they’ll let me hide my own Easter eggs!
too funny! I can relate to that too, Jon.
Thanks, Mynda! Much appreciated!
Hi there, can we do this with a scatter plot? I need the y axis to show up as text in my scatter plot
Yes, Jon Peltier describes it here.
When i try to convert the data to a bar chart, my whole chart goes to a Bar chart format.
I have tried replicating the dataset but Excel just wont let me do it. It continues to switch over the whole set (im using excel 2016)
Any idea what could have gone wrong?
I suspect you’re not selecting the dummy series on its own. You probably have more than one series selected.
To select just one series you can select the chart and then go to the Chart Tools: Format tab of the ribbon, and in the top left there is a drop down list where you can select the different chart elements. Select the specific series from this list and then right-click the chart > Change Series chart type. You’ll know if you got it right because you’ll see ‘Change Series chart type’ as opposed to ‘Change chart type’.
Thank you very much ! Awesome !
I thought I was mastering graphics for Excel, and I know realize how much I was wrong !!!
Cheers, Eric. One of the great things about Excel is you never stop learning 🙂
Thank you very much Mynda for your time in creating this awesome tutorial, this will be of great value to have a high-reliable technique to achieve this crart in Excel. I really appreciate your attention and great effort in develop such great tutorials!!
Thanks, Juan. I’m glad you liked it 🙂