Options for labelling an axis when graphing with Excel

April 27, 2012 12:25 PM

Excel graphing question: Is there a way to change the axis labels to something you are not graphing?

I have some results of a survey which are numerical - but they are the numerical responses to a question like "How much do you like this?" where 1= Not at all, 2=Somewhat, 3=I love it!

So I'm graphing the average of the responses which are obviously numerical: the mean of Group A's average is 1.35, while the mean of Group B's is 2.46. And currently, the Y-Axis of my graph is labelled numerically (1, 2, 3, etc), as that is the range of values from which I have generated the graph.

However, I am graphing this for a research participant newsletter, and I would like to change the Y-axis labels from being the numbers (which have no meaning) to saying "Not at all", "Somewhat" and "I love it!"

Is this possible within Excel? (My other option is just to suppress the labels and add a textbox after I import the graph into Word, but that's bothersome).

(Yes, I know that there are better programs for graphing - our data analyst uses one. But I have to publish the newsletter using word, and it's easier to lay out colours, fonts, etc with Excel than to be using an image generated from another program).

posted by jb to Computers & Internet (10 answers total)

VLOOKUP is your friend here. Here is a youtube video that demonstrates the process of using VLOOKUP to generate chart labels.
posted by crazycanuck at 12:31 PM on April 27, 2012

Response by poster: That seems interesting, but it's not exactly what I was looking for.

Ideally, I'd like my Y-Axis (currently labelled 1.00, 2.00, 3.00) to be labelled (Not at all, Somewhat, I love it) at the tick marks. I don't want to label the specific values (which are all between the tick marks).

I have been playing with the Y-axis title function (have text left to right, lots of hard returns to place the text at the tick mark), but it's a crude solution.
posted by jb at 12:57 PM on April 27, 2012

You need to add a second series with the axis labels you want, then hide the first one.

Something like http://peltiertech.com/Excel/Charts/ArbitraryAxis.html this.

I do this all the time, it feels a bit cumbersome at first, but it soon clicks and makes sense :)

Hope that helps!
posted by chrispy108 at 1:06 PM on April 27, 2012

while chrispy108's suggestion probably works, there's another way.

I'm assuming you're doing a bar plot? Because it wouldn't really make sense to do this with a scatter or line plot

Anyhow, plot the data you'd like.

For example, 3 categories with a mean for each:

meanA
meanB
meanC

highlight and plot the values as a Column Plot.

Then enter the category labels into a different column of cells.

Select the plot, go to the "Select Data" dialog, and under "Horizontal (Category) Axis Labels" You'll see the 1 2 3 that were defaulted. Click 'Edit' and then highlight the column of labels. Hit OK

Done!
posted by imagineerit at 1:54 PM on April 27, 2012

Response by poster: I can change the Category Labels, but what I want to change are the value labels.
posted by jb at 2:53 PM on April 27, 2012

It's not totally clear but by the sounds of it you're using the wrong type of plot for your data.

example data set?
posted by imagineerit at 3:50 PM on April 27, 2012

Response by poster: It's not so much that I'm using the wrong type of plot, but that I purposely wish to obscure some of the details of the data. I'm making graphs for a newsletter for people who participated in a scientific study where we turned their responses into numerical values, BUT we don't really want to confuse them with that.

Here is some example data:

People who are blue: average response = 1.35

People who are green: average response = 2.78

from a scale where 1= "I don't like this", 2="I kind of like this" and 3="I love this".

What I want is a bar graph where I have two bars showing the differences in the mean answers, but where the value labels say "Don't Like", "Kind of Like" and "Love This" INSTEAD of 1.00, 2.00, and 3.00.

NB: we didn't do our research on people who were blue or green, and we didn't actually as them how much they liked something. But it was a similarly non-numerical thing that was assigned a numerical value so that it could be analyzed statistically).
posted by jb at 5:23 PM on April 27, 2012

Response by poster: And I don't know why I'm being all secretive about what we did ask them, since that's confidential or anything. Basically, we asked people who have a chronic health condition about physical limitations and their level of satisfaction in the participation in different activities (physical leisure, traveling, family activities, etc) and we just want to report the mean responses back to them. Only I want to translate the responses back from the random numbers they were assigned to the words the people actually chose from the scale (No difficulty, some difficulty, a lot of difficulty).

In the mean time, I've misused the axis title and suppressed the value labels. I did this before I read chrispy108's suggestion, and I will try that when I'm next at work.
posted by jb at 5:27 PM on April 27, 2012

So you want the x-axis labels to be 'People who are blue,' 'People who are green,' and on the y-axis instead of 1.0, 2.0, 3.0 you want "don't like," "kind of like,"and "I love this?"

I don't think Excel can do that elegantly. The easiest way is probably to just hide the y-axis entirely and manually add in Text Box's over each bar.
posted by imagineerit at 11:31 AM on April 28, 2012

You can do this either by adding dummy values or else by installing an add in that will let you do this. Both methods are described neatly here.
posted by kev23f at 9:13 AM on May 2, 2012