Add Labels to Outliers in Excel Scatter Charts

Scatter charts are a great way to present the relationship between two data points

In my example, I’m using a scatter charts to monitor student test results – I want to compare their latest test score against how that score has changed from their last test.

We can quickly see that whilst most students hit the pass mark or 84.5%, the bulk of students slipped back from their previous score – must have been a tough exam!

But, what if you want to know which students are really struggling, and perhaps who really sits at the top of the pack?

Until Excel 2013, adding a label to a value on a scatter chart was a pain, and involved creating a VBA scripts to add these. The only label you could add was one to show the actual numeric values (83% / -1.5%) – which doesn’t help identify the ‘owner’ of those values.

Thankfully, in Excel 2013, we can finally add proper labels to scatter charts.

To do so is very simple. With your chart selected;

From the Tab Tools tab group, select the DESIGN tab

Select ‘Add Chart Element à Data Labels à More Data Label Options…

You will now see the Format Data Labels screen

Here, you can opt to show the X & Y values we’ve previously been able to show. Not always very helpful…

But… now we can tell it where to get the label from – Hurrah!

Remove the X & Y values (unless you want them, of course), tick the ‘Value From Cells’ option, and select ‘Select Range’

Select the column with your data labels in them. Be sure that you select the same range of rows as your base data

You will now see your names on your Scatter Chart

But, whilst this is great, it’s not always going to be very helpful….

However, now we know we can add labels based on a cell range, we can of course setup that cell range however we want.

In my case, I’ve created a new field, and created a formula which checks if their test score, and their score change, and for those students falling within those bands, bring back the student ID & Name…

The beauty of it, is that you can set that formula however you want – you might want to vlookup to a list of pre-defined student you want to call out, you might want a drop down box to control the parameters, or create a formula to flag out the bottom & top 5% of values.

Once you’re done, go back to your chart, and change the Data Label Range to your new field

And there you have it! A scatter chart with custom outlier labels!

Fancy a challenge? How about making the outliers stand out more by having their markers formatted differently?

Let me know how you get on, and I’ll do a write-up at some point in the future!

1 comment