The tutorial shows how to identify, highlight and label a specific data point in a scatter chart as well as how to define its position on the x and y axes.
Last week we looked at how to make a scatter plot in Excel. Today, we will be working with individual data points. In situations when there are many points in a scatter graph, it could be a real challenge to spot a particular one. Professional data analysts often use third-party add-ins for this, but there is a quick and easy technique to identify the position of any data point by means of Excel. There are a few parts to it:
Supposing, you have two columns of related numeric data, say monthly advertising costs and sales, and you have already created a scatter plot that shows the correlation between these data:
Now, you want to be able to quickly find the data point for a particular month. If we had fewer points, we could simply label each point by name. But our scatter graph has quite a lot of points and the labels would only clutter it. So, we need to figure out a way to find, highlight and, optionally, label only a specific data point.
As you know, in a scatter plot, the correlated variables are combined into a single data point. That means we need to get the x (Advertising) and y (Items sold) values for the data point of interest. And here's how you can extract them:
At this point, your data should look similar to this:
With the source data ready, let's create a data point spotter. For this, we will have to add a new data series to our Excel scatter chart:
As the result, a data point in a different color (orange in our case) will appear among the existing data points, and that is the point you are looking for:
Of course, since the chart series update automatically, the highlighted point will change once you type a different name in the Target Month cell (E2).
There are a whole lot of customizations that you can make to the highlighted data point. I will share just a couple of my favorite tips and let you play with other formatting options on your own.
For starters, let's experiment with colors. Select that highlighted data point, right click it and select Format Data Series… in the context menu. When doing so, please make sure that only a single data point is selected:
On the Format Data Series pane, go to Fill & Line > Marker and choose any color you want for the marker Fill and Border. For example:
In some situations, using a different color for the target data point may not be appropriate, so you can shade it with the same color as the rest of the points, and then make it stand out by applying some other maker options. For example, these ones:
To let your users know which exactly data point is highlighted in your scatter chart, you can add a label to it. Here's how:
In addition to or instead of the x and y values, you can show the month name on the label. To do this, select the Value From Cell check box on the Format Data Labels pane, click the Select Range… button, and choose the appropriate cell in your worksheet, E2 in our case:
If you want to show only the name of the month on the label, clear the X Value and Y Value boxes.
As the result, you will get the following scatter plot with the data point highlighted and labeled by name:
For better readability, you can mark the position of the data point important to you on the x and y axes. This is what you need to do:
As the result, the horizontal and vertical lines will extend from the highlighted point to the y and x axes, respectively:
And here comes the final version of our scatter graph with the target data point highlighted, labeled and positioned on the axes:
The best thing about it that you have to perform these customizations only one. Due to the dynamic nature of Excel charts, the highlighted point will change automatically as soon as you input another value in the target cell (E2 in our example):
The same technique can also be used to highlight the average, benchmark, smallest (minimum) or highest (maximum) point on a scatter diagram.
For example, to highlight the average point, you calculate the average of x and y values by using the AVERAGE function, and then add these values as a new data series, exactly as we did for the target month. As the result, you will have a scatter plot with the average point labeled and highlighted:
That's how you can spot and highlight a certain data point on a scatter diagram. To have a closer look at our examples, you are welcome to download our sample workbook below. I thank you for reading and hope to see you on our blog next week.
Excel Scatter Plot - examples (.xlsx file)
Table of contents