Skip to content
Related Articles

Related Articles

How to Find, Highlight, and Label a Data Point in Excel Scatter Plot?

View Discussion
Improve Article
Save Article
Like Article
  • Last Updated : 23 Mar, 2022

Scatter plots are one of the most frequently used charts for data analysis. There can be situations when you want to highlight a particular data point from the scatter chart that contains hundreds of data points. This seems to be a tedious task but it could be achieved very easily in excel. We will learn about how to find, highlight and label a data point in an excel Scatter plot. 

Use Hover for Small Data 

For table size less than equal to 10, this technique is quite efficient. For example, you are given a Pressure Vs Temperature plot. The number of rows in the table is 6. 

Scatter-Chart

 

Simply hover on the data points in the scatter chart. Here we can see that the point hovered has a pressure of 5 and a temperature of 12

Hover-on-it

 

This method is not efficient when we have more than 2 columns in our table or the number of rows greater than 10. 

Using Data Labels 

To highlight the data points for more information we can use data labels. These help us to make data more understandable. This technique is efficient if the number of rows in the given data source is less than 20. It’s been observed that if the number of rows is high then the data labels make complete chaos. The data labels start overlapping and the observation starts fading. For example, given a data source of students, the number of hours they study, and the marks obtained. Make data labels as students’ names on the given scattered graph for better observations. 

Example

 

Following are the steps: 

Step 1: Select the chart and click on the plus button. Check the box data labels

Check-the-box-data-labels

 

Step 2: The data labels appear. By default, the data labels are the y-coordinates. 

Data-labels

 

Step 3: Right-click on any of the data labels. A drop-down appears. Click on the Format Data Labels… option. 

Click-on-the-Format-Data-Labels

 

Step 4: Format Data Labels dialogue box appears. Under the Label Options, check the box Value from Cells

Check-the-box-Value-from-Cells

 

Step 5: Data Label Range dialogue-box appears. 

Step 6: Select the range for which you want to add your custom data labels. For example, B4:B9. Click Ok

Select-the-range

 

Step 7: Uncheck the rest of the boxes under Label Options

Uncheck-the-rest-of-the-boxes

 

Step 8: Data labels as student name appears. 

Data-labels-as-student-name-appears

 

We can observe that the highest and second-highest marks obtained by students are Arushi and Gautam respectively.  

Using Color Separation Technique

This is one of the most used techniques to highlight a data point in Excel. When we are having hundreds or thousands of data points in excel, the use of data labels is inefficient as it creates chaos and neatness starts fading from the scatter chart. To solve this problem, you can highlight a data point that you want to access. The chart we will create now will be a dynamic chart that finds and highlights a data point and changes according to the custom input. For example, you are given the same data source as above. Student name, study hours, and marks obtained. Highlight the cell you want as a custom input. 

 

Following are the steps: 

Step 1: Add a new table with three new columns in it. This table helps you input the cell you want to highlight. 

Adding-a-new-table

Step 2: Enter the Student name you want to highlight in your scatter chart. For example, Arushi. Now, our task is to find the Hours studied and Marks Obtained from the student name entered. You can use the VLOOKUP function for this. The formula written for cell G4 is =VLOOKUP(F4, B4:D9, 2, FALSE). You will obtain the Hours of study for the corresponding student’s name. 

Formula-written-in-cell-G4

 

Step 3: Also use the VLOOKUP function for adding the Marks obtained. The formula written for cell H4 is =VLOOKUP(F4, B4:D9, 3, FALSE). You will obtain the Marks obtained corresponding to the student’s name. 

Formula-written-in-cell-H4

 

Step 4: Enter the Student’s name to be highlighted. For example, Arushi

Highlighted-data

 

Step 5: Select the Chart. Go to the Chart Design tab. Click on the Select Data

Click-on-the-Select-Data

 

Step 6: Select the Data Source dialogue box that appears. Click on Add

Select-the-Data-Source-dialogue-box

 

Step 7: Edit Series dialogue box appears. In the Series name, select cell F3. In the Series X value, select the cell G4 and in the Series Y value, select the cell H4. Click Ok

Click-Ok

 

Step 8: A series name Student is added in the Legend Entries. Click Ok.

Student-is-added-in-the-Legend-Entries

 

Step 9: You can see that the data point is highlighted. To add the data label, repeat the same process done in method 2. Select the highlighted cell, click on the plus button. Check the box Data Labels.

Data-point-is-highlighted

 

Step 10: Now, right-click inside the highlighted cell. Click on Format Data Series…

Click-on-Format-Data-Series

 

Step 11: Format Data Series dialogue box appears. Under Series Options, click on the Marker. Here, you can customize the data point. You can change the color of the data point. You can also add a border color of the data point. 

Add-color-of-the-data-point

 

Step 12: Click on the Marker Options. Here you can customize, the type of data point and its size. 

Click-on-the-Marker-Options

 

Step 13:To add the Student name as the data label. Right-click inside the data label, of the highlighted cell. Click on Format Data Labels. 

Click-on-Format-Data-Labels

 

Step 14: Format Data Labels dialogue-box appears. Under Label Options, check the box Value from Cells

Check-the-box-Value -from-Cells

 

Step 15: Data Label Range dialogue-box appears. Select cell F4. Click Ok

Data-Label-Range-dialogue-box-appears

 

Step 16: Uncheck the rest of the boxes. 

Uncheck-the-rest-of-the-boxes

 

Step 17: Here the highlighted data point is Arushi

Highlighted-data-point-is-Arushi

 

Step 18: The chart you have created is dynamic. Changing the name to Gautam. Changes the highlighted cell. 

Change-the-highlighted-cell

 

Add position Lines in the highlighted data point

You may require adding the position of the highlighted point on the x-axis and the y-axis. Consider the above data set, with the highlighted student name as Arushi

Add-position-Lines-in-the-highlighted-data-point

 

Following are the steps:

Step 1: Select the highlighted point in your scattered chart. Click on the plus icon. Under the Charts Elements, click on the Error Bars. Then, select the percentage

Click-on-the-Error-Bars

 

Step 2: Select the highlighted data point. 

Select-the-highlighted-data-point

 

Step 3: Right-click on it. A drop-down appears. You can see that the series name of the highlighted data point is Student. Click on it. A drop-down appears. 

Series-name-of-the-highlighted-data-point-is-Student

 

Step 4: Click on Series “Student” X Error Bars. This helps us add the X position line of the highlighted data point. Series “Student” Y Error Bars, adds the Y position line of the highlighted data point. 

Click-on-Series-Student-X-Error-Bars

 

Step 5: This is the most important step. The X error line is selected. This line is so small that it’s not visible with your naked eyes. So, right-click on the light blue dot of the cell. Note, do not click on the highlighted data point.

Small-x-error-line-is-selected

 

Step 6: A drop-down appears. Click on the Format Error Bars… 

Click-on-the-Format-Error-Bars

 

Step 7: Format Error Bars dialogue box appears. In the Horizontal Error Bar, under Direction click on Minus. In the Error Amount, set the Percentage to 100%

Format-Error-Bars-dialogue-box-appears

 

Step 8: Horizontal Position line appears. 

Horizontal-Position-line-appears

 

Step 9: Repeat the same steps to add a Vertical Position line. 

Add-a-Vertical-Position-line

 


My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!