how to plot a graph in excel using 2 points

Learning how to make easy-to-read and interpret charts, such as Scatter Plot, is an incredibly powerful addition to your data storytelling weapons. If you want to lose your audience quicker than the rise and fall of a Ponzi scheme, talk about numbers.Ĭonversely, stories have an emotional appeal. You see, data with no narrative to simplify, is boring to any audience, including engineers perceived to be very analytical. Therefore, learning how to create a Scatter Plot in Excel with 2 variables is a huge leap towards crafting compelling data stories. For instance, dots progressing in an upward-right side symbolize a linear (causal-effect) relationship. Our brains can easily identify a trend using dots. So one way that we can make a Dynamic Chart in Excel is with the Offset Function.The chart, as mentioned above, is amazingly easy to read and understand. If we create a chart of all the data points, we will have lots of blanks in our data and the chart would look like this:

But we have a problem because we need to create a chart of only 3 data points (in our example). You can now see that we have the data points that have a “YES” grouped for our chart. Your chart data range should now look like this:ĥ) Define 2 Named Formula Using Offset for Categories and Data Points

Then copy it down from Cell G3 to Cell G13. So put this formula in cell G3 and change the array from A1:A13 to B1:B13 like this: This is the same formula that we did in step 3, but we just need to change the list of data in our Index formula. Now put this formula in cell F2:Ĥ) Use Index to Return the Smallest Row Data Point Now that we have a unique identifier for each selected data point, let’s use that in an index function to return the Horizontal Axis Categories. Your data range will now look like this:ģ) Use Index to Return the Smallest Row Category We will use these row values in our Index formula in the next steps. Here is my solution to the determining a unique identifier as to if a data point should be included in the chart.

Excel won’t move past the first “Yes” and will keep returning the same data point. Now we have identified the data points we want to show in our Excel chart, but we need a unique identifier for each data point. And you would want to mark a few data points with “YES” so that we will know if our test works.Ģ) Determine the Row of a Selected Data Point

How to plot a graph in excel using 2 points