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.

Scatter Chart in Excel Img1

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

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.

Scatter Chart in Excel Img2

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.

Scatter Chart in Excel Img4

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.

Would you like to add some to this article? Feel free to put your additional thoughts in the comment box.

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!

Read more:

Data Analysis in Excel – Learn 10 Common Statistical Terms

How to Calculate/Find Mean and Standard Deviation in Excel

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share here. Not only how to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned!

We will be happy to hear your thoughts

      Leave a reply