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:
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?
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?
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
posted by soelo at 11:45 AM on July 8, 2014
Response by poster: 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
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
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:
http://support.microsoft.com/kb/914813
posted by jeffjon at 11:52 AM on July 8, 2014
http://support.microsoft.com/kb/914813
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
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
Ah, add the data labels before manually selecting the X and Y columns.
posted by supercres at 11:58 AM on July 8, 2014
Response by poster: 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
posted by ocherdraco at 12:04 PM on July 8, 2014
Response by poster: 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
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:
http://www.appspro.com/Utilities/ChartLabeler.htm
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
http://www.appspro.com/Utilities/ChartLabeler.htm
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
Response by poster: 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
posted by ocherdraco at 12:23 PM on July 8, 2014
Best answer: 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]
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
Edit: grouse beat me to this and provided an example
posted by JackBurden at 12:44 PM on July 8, 2014
Response by poster: Hooray! It worked, it worked, it worked!
posted by ocherdraco at 1:27 PM on July 8, 2014
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
posted by chrispy108 at 6:43 PM on July 10, 2014
This thread is closed to new comments.
posted by ocherdraco at 11:36 AM on July 8, 2014