# Use Scatter Chart in Excel to Find Relationships between Two Data Series

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.

The data that you got 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

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.

Insert -> Charts -> Insert Scatter (X, Y) or Bubble Chart -> Scatter

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 get this scatter chart. I have added a trend line to the chart.

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.

This is a sample lecture of my course: Data Analysis in Excel with Statistics: Get Meanings of DataÂ where you will learn Data Analysis with 52 case studies, problems, and their solutions!