How To Find A Point On A Graph In Excel

The tutorial shows how to identify, highlight and label a specific data point in a scatter chart as well equally how to define its position on the 10 and y axes.

Terminal calendar week we looked at how to make a scatter plot in Excel. Today, we will be working with individual data points. In situations when at that place are many points in a besprinkle graph, it could be a real challenge to spot a detail i. Professional information analysts often use third-political party add-ins for this, but at that place is a quick and easy technique to identify the position of any data point by ways of Excel. There are a few parts to it:

• The source data
• Extract ten and y values for the data betoken
• Add together a new data series for the information betoken
• Customize the target data point
  • Change the appearance (color, fill, border, etc.)
  • Add the data point label
  • Characterization the data point by proper name
  • Show the position of the information point on x and y axes
• Show a position of average or benchmark point

The source data

Supposing, y'all have two columns of related numeric data, say monthly advertising costs and sales, and you accept already created a besprinkle plot that shows the correlation betwixt these data:

Now, you want to exist able to speedily observe the data point for a particular month. If we had fewer points, we could but label each point by name. Merely our scatter graph has quite a lot of points and the labels would but clutter it. So, we demand to effigy out a way to notice, highlight and, optionally, label only a specific data point.

Excerpt x and y values for the data bespeak

Every bit you know, in a scatter plot, the correlated variables are combined into a unmarried data signal. That means we need to get the ten (Advertising) and y (Items sold) values for the data point of interest. And here's how you lot can extract them:

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

   =VLOOKUP($Eastward$two,$A$2:$C$thirteen,two,FALSE)

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

   =VLOOKUP($Due east$2,$A$2:$C$thirteen,three,Imitation)

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

Add a new data serial for the data signal

With the source data gear up, let's create a data signal spotter. For this, nosotros will have to add a new information series to our Excel scatter chart:

1. Right-click whatever axis in your nautical chart and click Select Data….
   
2. In the Select Information Source dialogue box, click the Add button.
   
3. In the Edit Series window, do the post-obit:
   • Enter a meaningful name in the Series proper noun box, e.chiliad. Target Month.
   • Equally the Series Ten value, select the contained variable for your data signal. In this example, it's F2 (Advertising).
   • Every bit the Series Y value, select the dependent In our instance, it's G2 (Items Sold).
4. When finished, click OK.

As the result, a data bespeak in a different color (orangish in our instance) volition announced amidst the existing data points, and that is the point yous are looking for:

Of course, since the chart serial update automatically, the highlighted bespeak will change once yous blazon a different name in the Target Month jail cell (E2).

Customize the target information point

There are a whole lot of customizations that you can make to the highlighted data point. I volition share but a couple of my favorite tips and let you play with other formatting options on your own.

Modify the appearance of the information signal

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 and so, please make sure that only a single information point is selected:

On the Format Data Series pane, go to Fill up & Line > Marker and choose any color you lot want for the marker Fill and Edge. For example:

In some situations, using a dissimilar color for the target data indicate may not be appropriate, so y'all tin can shade it with the aforementioned color as the residual of the points, and then make information technology stand out by applying some other maker options. For example, these ones:

Add together the data point label

To let your users know which exactly data betoken is highlighted in your besprinkle chart, you lot tin add together a characterization to it. Hither'south how:

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

Characterization the information point by name

In addition to or instead of the 10 and y values, you lot can show the calendar 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 cull the appropriate cell in your worksheet, E2 in our case:

If you want to show simply the name of the month on the label, clear the X Value and Y Value boxes.

As the upshot, yous will become the post-obit scatter plot with the data bespeak highlighted and labeled by proper noun:

Ascertain the position of the data signal on x and y axes

For better readability, you lot can marking the position of the data bespeak important to you on the x and y axes. This is what y'all need to exercise:

1. Select the target data bespeak in a chart.
2. Click the Chart Elements push > Fault Bars > Percentage.
   
3. Right-click on the horizontal error bar and choose Format Error Confined… from the pop-up bill of fare.
   
4. On the Format Error Bars pane, go to the Fault Bar Options tab, and alter Direction to Minus and Percent to 100:
   
5. Click the vertical error bar and do the same customization.

   Every bit the upshot, 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 mistake bars so that they better fit the colors of your chart. For this, switch to the Fill & Line tab of the Format Mistake Bars pane and choose the desired Color and Nuance type for the currently selected error bar (vertical or horizontal). Then exercise the same for the other error bar:
   

And here comes the final version of our scatter graph with the target data signal highlighted, labeled and positioned on the axes:

The best thing about it that yous accept to perform these customizations only one. Due to the dynamic nature of Excel charts, the highlighted point will change automatically as soon every bit yous input another value in the target cell (E2 in our example):

Show a position of average or benchmark signal

The same technique can besides be used to highlight the average, benchmark, smallest (minimum) or highest (maximum) betoken on a scatter diagram.

For instance, to highlight the boilerplate bespeak, you lot calculate the average of x and y values past using the Average role, and and then add these values as a new data serial, exactly as we did for the target month. As the result, you will take a besprinkle plot with the average indicate labeled and highlighted:

That'southward how you can spot and highlight a sure data point on a scatter diagram. To take a closer wait at our examples, you are welcome to download our sample Excel Scatter Plot workbook. I cheers for reading and promise to see y'all on our blog next week.

You may too be interested in

Source: https://www.ablebits.com/office-addins-blog/2018/10/10/find-data-point-excel-scatter-graph/

Posted by: smithpriverime1944.blogspot.com

Share this post