Join Curt Frye for an in-depth discussion in this video Creating XY (scatter) charts, part of Excel for Mac 2011 Essential Training. Note: the Analysis TookPak is no longer included in Excel for the Mac. “Data” menu tab and see the “Data Analysis” option as below, then the add-in is already installed. Create your regression curve by making a scatter plot. ![]() 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. 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 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. 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: • 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. • In F2, insert the following formula to extract the number of the sold items for the target month: =VLOOKUP($E$2,$A$2:$C$13,2,FALSE) • 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: • Right-click any axis in your chart and click Select Data. • In the Select Data Source dialogue box, click the Add button. • 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 G2 (advertising cost). • As the Series Y value, select the dependent In our case, it's F2 (items sold). • 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.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
March 2019
Categories |