How to Read a Data Fro a Highlighted Graph on Excel

The tutorial shows how to identify, highlight and characterization a specific data signal in a scatter chart too as how to ascertain its position on the 10 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 exist a real challenge to spot a particular one. Professional data analysts often apply third-party add-ins for this, merely there is a quick and piece of cake technique to identify the position of any data bespeak by means of Excel. At that place are a few parts to information technology:

• The source information
• Extract x and y values for the information point
• Add a new information serial for the information point
• Customize the target data point
  • Change the appearance (color, fill, border, etc.)
  • Add together the data indicate label
  • Label the data point past name
  • Show the position of the information signal on x and y axes
• Show a position of average or benchmark bespeak

The source information

Supposing, you have two columns of related numeric data, say monthly advertising costs and sales, and you have already created a besprinkle plot that shows the correlation between these information:

Now, y'all want to be able to speedily find the data bespeak for a particular month. If we had fewer points, we could simply label each indicate by proper noun. But our scatter graph has quite a lot of points and the labels would simply clutter it. And then, we need to figure out a way to find, highlight and, optionally, label simply a specific data point.

Extract x and y values for the data point

As y'all know, in a scatter plot, the correlated variables are combined into a single data betoken. That means we demand to get the x (Advertising) and y (Items sold) values for the information point of involvement. And here'southward how you can excerpt them:

1. Enter the point's text label in a divide cell. In our example, let it exist the calendar month of May in cell E2. It is important that you lot 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$thirteen,two,Simulated)

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

   =VLOOKUP($Due east$2,$A$two:$C$13,3,FALSE)

   At this bespeak, your information should expect similar to this:
   

Add a new data series for the information point

With the source data ready, let's create a data betoken lookout man. For this, we will have to add together a new data series to our Excel besprinkle chart:

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

As the event, a information point in a different color (orange in our example) 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 betoken volition change in one case you type a different proper name in the Target Calendar month prison cell (E2).

Customize the target data signal

There are a whole lot of customizations that you can make to the highlighted data indicate. I will share just a couple of my favorite tips and permit yous play with other formatting options on your ain.

Alter the appearance of the information point

For starters, let'due south experiment with colors. Select that highlighted data betoken, right click information technology and select Format Data Series… in the context menu. When doing then, please brand sure that simply a single data point is selected:

On the Format Data Series pane, go to Make full & Line > Marker and cull any color you lot desire for the marker Make full and Border. For instance:

In some situations, using a different color for the target information betoken may non be appropriate, and so you can shade it with the aforementioned color every bit the rest of the points, and and then go far stand up out by applying some other maker options. For example, these ones:

Add the information signal label

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

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

Characterization the information point by name

In addition to or instead of the x and y values, y'all can bear witness the month name on the label. To do this, select the Value From Prison cell bank check box on the Format Information Labels pane, click the Select Range… button, and choose the appropriate prison cell in your worksheet, E2 in our instance:

If you desire to show simply the proper noun of the calendar month on the label, articulate the X Value and Y Value boxes.

Equally the issue, you will go the post-obit scatter plot with the information betoken highlighted and labeled by name:

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

For meliorate readability, yous can marker the position of the information point important to yous on the x and y axes. This is what you need to do:

1. Select the target information signal in a chart.
2. Click the Chart Elements button > Mistake Confined > Percentage.
   
3. Right-click on the horizontal error bar and choose Format Error Confined… from the pop-up card.
   
4. On the Format Error Confined pane, go to the Fault Bar Options tab, and modify Management to Minus and Per centum to 100:
   
5. Click the vertical fault bar and practise 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 modify the color and style of the fault 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 Colour and Nuance type for the currently selected error bar (vertical or horizontal). And then practise the same for the other error bar:
   

And hither comes the final version of our scatter graph with the target information signal 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 prison cell (E2 in our example):

Prove a position of average or benchmark point

The same technique tin can also exist used to highlight the average, benchmark, smallest (minimum) or highest (maximum) point on a besprinkle diagram.

For instance, to highlight the boilerplate point, you calculate the boilerplate of ten and y values by using the Boilerplate part, and so add these values as a new information serial, exactly as nosotros did for the target month. As the result, you lot will accept a scatter plot with the average indicate labeled and highlighted:

That'southward how you can spot and highlight a sure data betoken on a scatter diagram. To accept a closer look at our examples, you are welcome to download our sample Excel Scatter Plot workbook. I cheers for reading and promise to come across you on our web log next week.

You may also be interested in

getherwrearpon.blogspot.com

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

Share this post