Text Labels on a Vertical Column Chart in Excel

When analyzing survey results, for example, there may be a numerical scale that has associated text labels. This may be a scale of 1 to 5 where 1 means “Completely Dissatisfied” and 5 means “Completely Satisfied”, with other labels in between. The data can be plotted by value, but it’s not obvious how to place the text labels on the chart in place of the numerical labels on the vertical axis.

Text Labels on a Vertical Column Chart

There are several ways to accomplish this task. In this tutorial I’ll show how to use a combination bar-column chart, in which the columns show the survey results and the bars provide the text labels for the horizontal axis. The steps are essentially the same in Excel 2007 and in Excel 2003. I’ll show the charts from Excel 2007, and the different dialogs for both where applicable.

Let’s assume the following dummy survey results.

Data for column chart

Plot the responses for each question (the first two columns of the data) in a clustered column chart, and use the Error column as custom error bar values.

Col Chart (Step 1)

So far so good. Note that I’ve violated the first rule of column chart value axis scales, which is that The Axis Scale Must Include Zero. However, the minimum possible score here is 1, and we’ll be using text labels. In our chart, fixing the scale at 1 to 5 makes sense.

Here is the data for the text labels. Rating 1 may stand for “Totally Lame” and Rating 5 for “Totally Awesome”. I chose the Dummy values of 2 just so the data would show up in the chart.

Data for text labels

Copy this table above, select the chart, and use Paste Special to add the data to the chart using the settings below (the Excel 2007 dialog is very much like this Excel 2003 dialog).

Chart Paste Special Dialog

We now have two sets of columns in the chart.

Col Chart (Step 2)

Right click on the new series, choose “Change Chart Type” (“Chart Type” in 2003), and select the clustered bar style.

Bar Chart (Step 3)

There are no Rating labels because there  is no secondary vertical axis, so we have to add this axis by hand. On the Excel 2007 Chart Tools > Layout tab, click Axes, then Secondary Horizontal Axis, then Show Left to Right Axis.

Chart Tools - Layout Tab - Axes - Excel 2007

In Excel 2003 go to the Chart menu, choose Chart Options, and check the Category (X) Axis checkmark.

Chart Options - Axes - Excel 2003

Now the chart has four axes.

Col Chart (Step 4)

We want the Rating labels at the left side of the chart, and we’ll place the numerical axis at the right before we hide it. In turn, select the bottom and top vertical axes.

In the Excel 2007 Format Axis dialog, the bottom axis will be set so the vertical axis crosses at the automatic setting, and the top axis so the vertical axis crosses at the maximum category. Switch the settings of the top and bottom axes.

Format Axis Dialog - Axis Crosses At - Excel 2007

In the Excel 2003 Format Axis dialog, the maximum category checkbox checked for the top axis and unchecked for the bottom axis. Change the setting for the top and bottom axes.

Format Axis Dialog - Axis Crosses At - Excel 2003

Now we have the axes where we want them.

Col Chart (Step 5)

Hide the dummy series by setting its fill color to no fill.

Col Chart (Step 6)

Hide the top and right axes by selecting “None” for axis tick marks and tick labels, and “No Line” for the axis line itself.

Col Chart (Step 7)

In Excel 2007 (not in Excel 2003) the Rating labels may have been squished. Select the plot area, and drag its left edge inwards to make room for the labels.

Col Chart (Step 7a)

The Rating labels are still not properly aligned, but this is easy to fix.

Format the vertical axis, and change the Position Axis setting of the horizontal axis from “Between Tick Marks” to “On Tick Marks”.

Format Axis Dialog - Position Axis - Excel 2007

In the Excel 2003 Format Axis dialog, uncheck the “Value Axis Crosses Between Categories” checkbox.

Format Axis Dialog - Position Axis - Excel 2003

Finally we have our chart with text labels along the survey response (vertical) axis.

Column Chart with Value Axis Text Labels

See Text Labels on a Horizontal Bar Chart in Excel to see how to get the text labels onto the horizontal axis of a bar chart.

Text Labels on a Horizontal Bar Chart


Peltier Tech Charts for Excel


  1. Hi Jon,

    Once again, glad to see you are posting. At this time of year, I just wanted to thank you for your contribution to the Excel community, the wit and opinion you put into your posts here and in the newsgroups.

    All the Best of the Season to you & your family.

    Strum, strum, strum….



  2. yet another excellent post!

  3. Wow, great tip! Thanks!

  4. This tutorial has been very helpful and informative. Thank you very much for sharing the technique.

  5. Hi
    thank you very much for very useful posts
    that was awesome!

  6. The category labels on the x-axis of my graph are now too long to fit on the graph without overlapping and they won’t wrap into two lines. For instance, if you had typed out “Question 1” instead of “Ques 1” on the graph above, it would have overlapped “n 1” with “Que” from the label “Question 2.” Do you know of a way to wrap the text for the labels along the x-axis so it will fit when the graph shrunk down? (I tried putting lots of spaces between the words -which usually works to make it wrap- but it just made the label longer on one line.) I’m using Excel 2010.
    Thank you for your help and expertise!

  7. It’s also worth noting that in Excel 2010 I was unable to find the “Value (Y) crosses at maximum category” button. What I ended up doing was going to Format Axis, Axis Options, and under ‘Axis labels’ choosing “Low.” After that, I had to put an outline around the graph because there was no other way to get the left verticle axis line back in.

  8. Rachel –

    1. About all you can do is insert a line feed into the cell. Place the cursor where you want the text to wrap, hold Alt, and press Enter.

    2. Format the horizontal axis. In the Axis Options pane, near the bottom, the header says “Vertical Axis Crosses”, and you have the choices “Automatic”, “At Category Number”, and “At Maximum Category”. Choose the last option.

  9. I have an xy scatter chart. I want to place a name by the axes to identify what it is. A simple operation that should be 5 minutes, has now taken me an hour. You guys screwed this up bad.

  10. “Us guys?”
    Did you want to add Axis Titles?
    Excel 2007/2010: Chart Tools/Layout tab > Axis Titles.
    Excel 2013: Plus icon > Axis Titles or Chart Tools/Design tab > Add Chart Element > Axis Titles.
    Google: ‘Excel Axis Titles’ or ‘Excel Axis Labels’.

  11. Great instructions – was looking exactly for this – clear, simple, very effective. Thank you :) .

  12. Excellent!
    I do not understand why this cannot be found on official Office site…

  13. thanks Jon! Excellent post that just works :-)

  14. I am just trying to figure out how to add the “Ques1” “Ques 2” etc. to a graph. Why is this information not easily available anywhere?

  15. Melissa –
    In the first table of this post (just below the first chart) you see three data columns: questions, ratings, and errors. Select the first two columns (including the top row with the blank cell and the “Response” label) and make your chart, and it will automatically use those labels for the category axis labels. If you didn’t start out with this kind of data, you can right click on the chart, select Select Data, then click the Edit button above the category labels section in the right half of the dialog, and select the range with these labels.

  16. Jon, thanks for your postings. I was able to use this post and another one to change the labels on both my x-axis and y-axis. I don’t know why the software has to make it so cumbersome! Thanks.

  17. Great articles except for the explaining what your article actually claims to be intending to explain part. How do I make my x axis label be something other than number? You claim to explain that in this article. Yet you simply show that you have already done it and then go on to talk about god only knows what.

  18. Well, the article was about putting text labels on the Y axis, which it does in fact explain. But besides that, what part of the article are you having trouble with?

  19. Thanks a lot!!!

  20. Hello,

    How do I do this if I can’t add a secondary axis? The chart I am working with is built piece by piece using VBA. I cannot recreate the chart manually to even play with it. I’m ready to bang my head against a brick wall. I wish I could show you what I’m working on, but confidentiality prevents me. Any ideas on building a y-axis using VBA that will simply replace the existing one? The y-axis I want to use is a table on one of my worksheets. Any help, ideas, thoughts, you may have will be greatly appreciated!

  21. Update: I’ve played with the chart and figured out how to do it. My question is this….what if the y-axis isn’t spread out evenly? For example, what if the y-axis is % and the % varies. I would like my labels to match the %. In your example above, Rating 1 would be 0 percent, Rating 2 would be 16%, Rating 3 would be 33%, Rating 4 would be 75% and, Rating 5 would be 95%. How can that be accomplished?

  22. Hi Jon,

    I’m using Excel 2013. When I Paste Special the “Rating” Data, Stacked Columns are only added next to the first 4 Points of Series1. I can see that I definitely selected all 5 of the Rating labels before the Copy Operation.

    Any thoughts?

  23. Winston –

    Did you select the header as well (“Dummy”)? If not, then don’t check Series Names in First Row.

  24. Hi Jon,
    I’m working with Excel 2010.. I can’t find the Paste Special option. I basically want my y-axis scale to be –,-,0,+,++. Can you give me some tips on how to do this? Thanks in advance!

  25. Home tab > Paste button dropdown arrow > Paste Special

  26. Ah I see, thanks for the quick reply!

  27. This is great and really helped me do what I need.

    However, I have one more part that I don’t know is possible: My chart has data on it that will range from 1-3. However, the scale is 1-1.6, 1.7-2.3 and 2.4-3.

    What I’m looking for is a way to display the tick marks at 1.6 and 2.3 but also have the labels of “Rating 1” at 1.6, “Rating 2” at 2.3, and “Rating 3” at the 3 mark. Is there a way to do this?

    Thanks in advance!

  28. vvanasperen says:

    Great stuff! Thanks a lot for sharing.

  29. Thank you somuch for this tutorial…it helped alot…what took me the whole night to figure out was resolved in a few minutes.

  30. Robin Carseldine says:

    Are you able to provide a similar tutorial for 2016 as I can’t get the above to work.

  31. Robin –

    The interface for Excel 213 and 2016 is different than that shown for Excel 2007 (and 2010) above. You can add an axis using the [+] “skittle” next to the chart. Formatting the axes is much the same, but you have a task pane along the right edge of the Excel window, not a big ugly dialog floating in the middle. The wording might be slightly different, and things may have been rearranged, but the things you need to select or check are still there.

  32. Hi Jon, very useful your post and very good explanations.
    I would like to do it with a stacked column graph and I have not been able, it only serves in clustered column graph?

  33. Carmenpat –

    Nope, works just the same. Here are my data ranges:

    Text Labels on Vertical Axis of Stacked Column Chart - Data

    Here are the steps I took to construct the chart. Same as above.

    Text Labels on Vertical Axis of Stacked Column Chart - Chart Construction


  1. […] to Juan for asking this question, and to Jon Peltier who wrote about using this solution with a column chart way back in […]

Speak Your Mind


Peltier Tech Charts for Excel 3.0