Despite the impossibly long list of features and settings in Microsoft’s Excel, the most popular spreadsheet suite still lacks some features that seem trivial, or they require complicated workarounds and possibly some knowledge of VBA. One omission that annoyed me several times is the fact that in XY scatter (and bubble) charts you can’t automatically label data points with text from a third column; it’s only possible to show the value of the X or Y point. Until recently, custom labels required either a lot of manual work to change the default text, which was not sustainable for large amounts of data, or the use of macros or third-party add-ons. But, while working on the chart for my previous article, I discovered that, at least in Office 2013, this option has finally been added to the user interface!
It’s still a little buried under the wealth of chart options, so here’s how you can find it: first set up the chart and make sure you have turned on data labels. Then select the data labels for the series where you want to change to custom labels and open the formatting pane (right click and choose ‘Format Data Labels…’). Another way to find this panel is to select the chart, then go to the tab ‘Design’ in the Office ribbon, click the menu ‘Add Chart Element’ ► ‘Data Labels’ ► ‘More Data Label Options…’. Once here, select ‘Label Options’, check the first box, ‘Value From Cells’ – and optionally uncheck the rest to keep labels from becoming too large and hard to read – and finally use the button ‘Select Range…’ to add the range of custom labels to the chart.
That’s it, no more manual workarounds, just a couple of clicks and charts can become much clearer for readers. I’m assuming the range for labels should have the same number of cells as the ranges of the X and Y data. If you don’t need to label the entire series, you can leave some blank cells in the label range, these are ignored when Excel draws data labels. You can see an example chart below, a slightly modified version of an illustration for an older article.