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 Chart Utility

Comments

  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….

    Cheers,

    Bob

  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.

Subscribe without commenting

Trackbacks

  1. [...] This post was mentioned on Twitter by Teach Business. Teach Business said: RT @ThatBlokeSean: Text Labels on a Vertical Column Chart in Excel – http://peltiertech.com/WordPress/text-labels-on-vertical-axis-in-ex … [...]

Write a Comment

I welcome comments from my readers. If you have an opinion on this post, if you have a question, or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion. If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.

Subscribe without commenting

Peltier Tech Chart Utility

 

Create Excel dashboards quickly with Plug-N-Play reports.

 

Site by: Dawud Miracle, Business Coach & WordPress Websites