I demonstrated in a post from March 2013 how to create Custom data labels in a chart. Unfortunately that technique worked only for bar and column charts, there was no way you could apply the same technique for a x y scatter chart, as far as I know.
This animated picture shows a column chart with custom data labels.
Luckily the people at Microsoft have heard our prayers. They have implemented a feature into Excel 2013 to label a value from cells, in a x y scatter chart. In fact, I think you can label a value from cells in most chart types.
This example chart shows the distance between the planets in our solar system, in an x y scatter chart.
How to apply custom data labels in excel 2013
The first 3 steps tells you how to build a scatter chart.
- Select cell range B3:C11
- Go to tab "Insert"
- Click the "scatter" button
- Right click on a chart dot and left click on "Add Data Labels"
- Right click on a dot again and left click "Format Data Labels"
- A new window appears to the right, deselect X and Y Value.
- Enable "Value from cells"
- Select cell range D3:D11
- Click OK
This is what the chart shows, as you can see you need to manually rearrange the data labels and add data label shapes.
How to add data label shapes
- Right click on a data label
- Click on "Change data label shapes"
- Select a shape
How to rearrange data labels
You can manually click and drag data labels as needed. You can also let excel change the position of all data labels, choose between center, left, right, above and below.
Download excel 2013 *.xlsx file
Workaround for Excel 2010 and previous versions
This workaround is alright if your data set is small.
- Make sure you select a single data label
- Click on formula bar
- Type =
- Use your mouse to click on a cell that contains the value you want to use.
- Repeat step 1 to 4 with remaining data labels
Change the value in cell D3 and see how the data label on the chart instantly changes.