55

I am trying to figure out how to get labels to show on either Google sheets, Excel, or Numbers.

I have information that looks like this

name|x_val|y_val
----------------
a   |    1|    1
b   |    2|    4
c   |    1|    2

Then I would want my final graph to look like this.

4|      .(c)
3|
2|  .(b) 
1|  .(a)
 |__ __ __ __
0   1  2  3  4

Like why can't I label each of these points with its name? I can only seem to label the value, e.g, (c) would show 4

Is the only solution D3?

zx8754
  • 52,746
  • 12
  • 114
  • 209
loonyuni
  • 1,373
  • 3
  • 16
  • 24

5 Answers5

70

Well I did not think this was possible until I went and checked. In some previous version of Excel I could not do this. I am currently using Excel 2013.

This is what you want to do in a scatter plot:

  1. right click on your data point

  2. select "Format Data Labels" (note you may have to add data labels first)

  3. put a check mark in "Values from Cells"
  4. click on "select range" and select your range of labels you want on the points

Example Graph

UPDATE: Colouring Individual Labels

In order to colour the labels individually use the following steps:

  1. select a label. When you first select, all labels for the series should get a box around them like the graph above.
  2. Select the individual label you are interested in editing. Only the label you have selected should have a box around it like the graph below.
  3. On the right hand side, as shown below, Select "TEXT OPTIONS".
  4. Expand the "TEXT FILL" category if required.
  5. Second from the bottom of the category list is "COLOR", select the colour you want from the pallet.

If you have the entire series selected instead of the individual label, text formatting changes should apply to all labels instead of just one.

Colouring

Forward Ed
  • 9,484
  • 3
  • 22
  • 52
  • 9
    I just got IT to give me Microsoft Office 2016 for Mac, but unfortunately Excel for Mac does not have this feature :(. Thanks though – loonyuni Apr 15 '16 at 00:13
  • 1
    This is excellent. All official documentation I can find from Microsoft explains how to do this with clumsy macros. This is the perfect solution. – Tom Bowen Apr 12 '18 at 10:40
  • I use excel 365 (build 1805) on windows and I don't have the option "value from cells" :( any other way? – Si Mon Jun 20 '18 at 09:24
  • @SiMon Not that I am aware of. If you have a small data set you may have the option in the other answer available to you. – Forward Ed Jun 20 '18 at 12:59
  • How can you make each of label a different color here ? – nyan314sn Nov 05 '18 at 15:30
  • How can you make each of label be a legend item instead? – Pedro77 Oct 15 '19 at 18:27
  • @Pedro77 only series name and data point style for the series show up in the legend. In order to have each data point show up in the legend you would need to create a series for each point you want in the legend and only include the data for those points in the series. When plotting each of those series format the series to only include the data points and not show the line connecting. You can then make a series of all the data points which would superimpose on top of the other series. Configure it to only show the line. That is the only way I can think of doing it, unless I misunderstood – Forward Ed Oct 15 '19 at 20:24
6

None of these worked for me. I'm on a mac using Microsoft 360. I found this which DID work: This workaround is for Excel 2010 and 2007, it is best for a small number of chart data points.

Click twice on a label to select it. Click in formula bar. Type = Use your mouse to click on a cell that contains the value you want to use. The formula bar changes to perhaps =Sheet1!$D$3

Repeat step 1 to 5 with remaining data labels.

Simple

jacqui
  • 61
  • 1
  • 1
  • Thanks. I'm using Excel 2016 on a PC but this helped me figure out how to label one point (by double clicking to select it, then right clicking) rather than all points as in the top answer. – Chris Jenks May 13 '19 at 17:43
3

For all those who don't have the option in Excel (like me), there is a macro which works and is explained here: https://www.get-digital-help.com/2015/08/03/custom-data-labels-in-x-y-scatter-chart/ Very useful

Si Mon
  • 397
  • 3
  • 16
2

Another convoluted answer which should technically work and is ok for a small number of data points is to plot all your data points as 1 series in order to get your connecting line. Then plot each point as its own series. Then format data labels to display series name for each of the individual data points.

In short it works ok for a small data set or just key points from a data set.

Forward Ed
  • 9,484
  • 3
  • 22
  • 52
0

If using VBA is an option and assuming that you have a table named 'Table1' of the form:

Label|x_val|y_val
----------------
a   |    1|    1
b   |    2|    4
c   |    1|    2

this routine should work:

Sub labelDatapoints()
    Dim r As Integer
    With ActiveSheet.ChartObjects(1).Chart                                                                  'The scatter plot
        .SeriesCollection(1).ApplyDataLabels
        For r = 1 To Range("Table1[Label]").Rows.Count                                                      'iterate through all data points
                .SeriesCollection(1).Points(r).DataLabel.Text = Range("Table1[Label]").Cells(r).Value       'add the custom label to the current datapoint
        Next r
    End With
End Sub

Modified from https://www.get-digital-help.com/dynamic-data-labels-in-a-chart/

JAUGRY
  • 25
  • 6