Labeling data points in an Excel chart
July 8, 2014 11:35 AM   Subscribe

Help me figure out how to get the labels I need on the data points in a fairly simple Y vs. X chart.

I'm using Excel for Mac 2011, version 14.4.3.

I'm having a devil of a time figuring out how to get Excel to automatically label my data points with a value other than the X or Y value. My chart looks something like this:

Name      Category  Y value   X value
Apple     Fruit     1         2
Orange    Fruit     2         4
Okra      Vegetable 3         6
Beans     Vegetable 4         8

I have plotted the X and Y values on a chart. I would like each data point to be labeled "Name, Category", or if only one is possible, just "Name." So, point (1,2) would be labeled "Apple, Fruit" (or just "Apple"), and point (3,6) would be labeled "Okra, Vegetable" (or just "Okra"). Is there some way I can make this happen other than by entering each label manually?
posted by ocherdraco to Computers & Internet (15 answers total) 2 users marked this as a favorite
Oh, and I should mention that I've plotted them on a scatter chart.
posted by ocherdraco at 11:36 AM on July 8, 2014

I am using Excel 2013, but I right click on one of the points and pick Format Data Labels. Then I get Label Options of Series Name, X Value and Y Value.
posted by soelo at 11:45 AM on July 8, 2014

Yes, I am aware of those options, but they don't do what I'm looking for. I'm specifically looking for labels that aren't the series name, the x value, or the y value.
posted by ocherdraco at 11:49 AM on July 8, 2014

The problem is that none of those options correspond to ocherdraco's desired outcome.
Series Name = "X value" (in this example)
X value = some combination of the first 3 columns, depending on how the graph is set up, but definitely including the 1,2,3,4 values.
Y value = 2,4,6,8.

I know you said "automatically", but a rough short-term fix is that the data labels can be manually edited once they're active. Other than that, I'll be watching with interest for the right answer as well.
posted by jeffjon at 11:49 AM on July 8, 2014

This would seem to be the "official" solution, though not a super straightforward one if you're not familiar with VBA:
posted by jeffjon at 11:52 AM on July 8, 2014

Got it. I did it with the given data in Excel for Mac.

Select the whole table (all four columns) and create a scatter plot. It will add two data series, so right-click on the data series and "Select Data", then manually select the X and Y columns for the data series. Delete the other one. Then right click on the data series and add labels. Then right-click on the labels and Format Data Labels. In the dialog box, I had to uncheck "Y value" and check "X value". It still reads the X value from columns A and B.
posted by supercres at 11:55 AM on July 8, 2014

Damn, it didn't work when I tried to repeat it.

Ah, add the data labels before manually selecting the X and Y columns.
posted by supercres at 11:58 AM on July 8, 2014

Unfortunately, that method's not working for me, supercres. When I manually select the data series columns, the labels change to the new selection.
posted by ocherdraco at 12:04 PM on July 8, 2014

And the Visual Basic thing didn't work for me either (which is not surprising, since I have no knowledge of how visual basic works).
posted by ocherdraco at 12:13 PM on July 8, 2014

This is a very common problem and it's not really solvable without VBA. If you're having trouble with the code, here's a free utility that does it for you:

The only non-VBA option is to make each data point its own series, which is probably even more trouble than just entering the labels manually.
posted by neat graffitist at 12:19 PM on July 8, 2014

I think what I'm going to do is make each category its own series and then (sigh) manually label the names. The categories have few enough entries that doing it that way will at least make it faster to identify which datapoint is which.
posted by ocherdraco at 12:23 PM on July 8, 2014

You can do something like this with Google Docs. Here's an example, "AskMe 264815".
posted by grouse at 12:44 PM on July 8, 2014 [1 favorite]

If you are open to it, Google Spreadsheet may help. I moved Category over to be the fourth column and inserted a scatter chart: each point was labelled and the categories color coded.

Edit: grouse beat me to this and provided an example
posted by JackBurden at 12:44 PM on July 8, 2014

Hooray! It worked, it worked, it worked!
posted by ocherdraco at 1:27 PM on July 8, 2014

The (free) Excel add-in neat graffitist linked to is awesome, I use it most days. No idea why this isn't built into Excel to start with though!
posted by chrispy108 at 6:43 PM on July 10, 2014

« Older my life in the mean time   |   Moving with a Soda Stream? Newer »
This thread is closed to new comments.