In this post, you will learn how to find out the relationships between two or more data series using Scatter Chart in Excel.
You know, business data analysis has so many goals. One of the goals is to find out the hidden relationships between values.
For example, say you run a little store in your town. Many of your customers come from farther distance, and some of them live near your store. You want to run a survey on your customers to find out whether who comes to your store from farther distance shop more than who comes from nearby places. Say you have found the following data from your survey.
On the left, you get the Distance column and on the right, you get the Amount spent. Distances are measured in miles unit and amounts spent are in dollar. To get relationships between two data series, you need to use the XY or Scatter chart in Excel.
Making a Scatter Chart in Excel
To follow along with this article, download the workbook that is used to build this post.
Select any cell within the data. In the Insert tab, and in the Charts group of commands, click on the Insert Scatter (X, Y) or Bubble Chart command. A drop down appears, and I select the first one, simply Scatter chart.
You get a Scatter chart like the following image. In the X axis, you get the distance in miles, and in the Y axis, you get the amount spent values. And from the chart, it is clear that there is a clear relationship between distance and amount spent.
You see from the chart that person who comes to your store from farther distance shop more. In the chart, you see the persons who comes from within 0 to 20 miles of distance, shop between 0 and 50 dollar. When customers distance are between 40 and 60 miles they shop between 35 and 125 dollars. The higher the distance, the range is also higher.
The scenario makes sense. If you live close to a store, you are able to make frequent small purchases every time you go to the store. But in most cases, large departmental stores are not available around your living place. So when you will finally go there, you will spend more money because you cannot make frequent visits to a long distance departmental store. This Scatter (X, Y) chart shows you that relationship easily.
So using Scatter Chart in Excel, you can easily show the relationships between two or more data series.
Would you like to add some to this article? Feel free to put your additional thoughts in the comment box.