What is a single data point or element in a spreadsheet?

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:

The source data

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.

Extract x and y values for the 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:

1. Enter the point's text label in a separate cell. In our case, let it be the month of May in cell E2. It is important that you enter the label exactly as it appears in your source table.
2. In F2, insert the following VLOOKUP formula to extract the number of the sold items for the target month:

   =VLOOKUP[$E$2,$A$2:$C$13,2,FALSE]

3. In G2, pull the advertising cost for the target month by using this formula:

   =VLOOKUP[$E$2,$A$2:$C$13,3,FALSE]

   At this point, your data should look similar to this:
   

Add a new data series for the data point

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:

1. Right-click any axis in your chart and click Select Data….
   
2. In the Select Data Source dialogue box, click the Add button.
   
3. In the Edit Series window, do the following:
   • Enter a meaningful name in the Series name box, e.g. Target Month.
   • As the Series X value, select the independent variable for your data point. In this example, it's F2 [Advertising].
   • As the Series Y value, select the dependent In our case, it's G2 [Items Sold].
4. When finished, click OK.

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].

Customize the target data point

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.

Change the appearance of the data point

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:

Add the data point label

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:

1. Click on the highlighted data point to select it.
2. Click the Chart Elements button.
3. Select the Data Labels box and choose where to position the label.
   
4. By default, Excel shows one numeric value for the label, y value in our case. To display both x and y values, right-click the label, click Format Data Labels…, select the X Value and Y value boxes, and set the Separator of your choosing:
   

Label the data point by name

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:

Define the position of the data point on x and y axes

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:

1. Select the target data point in a chart.
2. Click the Chart Elements button > Error Bars > Percentage.
   
3. Right-click on the horizontal error bar and choose Format Error Bars… from the pop-up menu.
   
4. On the Format Error Bars pane, go to the Error Bar Options tab, and change Direction to Minus and Percentage to 100:
   
5. Click the vertical error bar and do the same customization.

   As the result, the horizontal and vertical lines will extend from the highlighted point to the y and x axes, respectively:
   

6. Finally, you can change the color and style of the error bars so that they better fit the colors of your chart. For this, switch to the Fill & Line tab of the Format Error Bars pane and choose the desired Color and Dash type for the currently selected error bar [vertical or horizontal]. Then do the same for the other error bar:
   

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]:

Show a position of average or benchmark point

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.

Practice workbook

Excel Scatter Plot - examples [.xlsx file]

You may also be interested in

What is a single value from within a data series?

What is a spreadsheet what are its various elements?

How are data organized in a spreadsheet?

How many types of data cell contain?