Excel has some useful chart types that can be used to plot data and show analysis.
A common scenario is where you want to plot X and Y values in a chart in Excel and show how the two values are related.
This can be done by using a Scatter chart in Excel.
For example, if you have the Height (X value) and Weight (Y Value) data for 20 students, you can plot this in a scatter chart and it will show you how the data is related.
Below is an example of a Scatter Plot in Excel (also called the XY Chart):
In this tutorial, I will show you how to make a scatter plot in Excel, the different types of scatter plots, and how to customize these charts.
What is a Scatter Chart and When To Use It?
Scatter charts are used to understand the correlation (relatedness) between two data variables.
A scatter plot has dots where each dot represents two values (X-axis value and Y-axis value) and based on these values these dots are positioned in the chart.
A real-life example of this could be the marketing expense and the revenue of a group of companies in a specific industry.
When we plot this data (Marketing Expense vs. Revenue) in a scatter chart, we can analyze how strongly or loosely these two variables are connected.
Also read: Bell Curve in Excel
Creating a Scatter Plot in Excel
Suppose you have a dataset as shown below and you want to create a scatter plot using this data.
The aim of this chart is to see whether there is any correlation between the marketing budget and the revenue or not.
For making a scatter plot, it’s important to have both the values (of the two variables that you want to plot in the scatter chart) in two separate columns.
The column on the left (Marketing Expense column in our example) would be plotted on the X-Axis and the Revenue would be plotted on the Y-Axis.
Below are the steps to insert a scatter plot in Excel:
- Select the columns that have the data (excluding column A)
- Click the Insert option
- In the Chart group, click on the Insert Scatter Chart icon
- Click on the ‘Scatter chart’ option in the charts thats show up
The above steps would insert a scatter plot as shown below in the worksheet.
The column on the left (Marketing Expense column in our example) would be plotted on the X-Axis and the Revenue would be plotted on the Y-Axis. It’s best to have the independent metric in the left column and the one for which you need to find the correlation in the column on the right.
Adding a Trend Line to the Scatter Chart
While I will cover more ways to customize the scatter plot in Excel later in this tutorial, one thing that you can do immediately after building the scatter plot is to add a trend line.
It helps you quickly get a sense of whether the data is positively or negatively correlated, and how tightly/loosely correlated it is.
Below are the steps to add a trendline to a scatter chart in Excel:
- Select the Scatter plot (where you want to add the trendline)
- Click the Chart Design tab. This is a contextual tab which only appears when you select the chart
- In the Chart Layouts group, click on the ‘Add Chart Element’ option
- Go to the ‘Trendline’ option and then click on ‘Linear’
The above steps would add a linear trendline to your scatter chart.
Just by looking at the trendline and the data points plotted in the scatter chart, you can get a sense of whether the data is positively correlated, negatively correlated, or not correlated.
In our example, we see a positive slope in the trendline indicating that the data is positively correlated. This means that when the marketing expenses go up then the revenue goes up and if the marketing expenses go down then the revenue goes down.
In case the data is negatively correlated, then there would be an inverse relation. In that case, if the marketing expenses go up then the revenue would go down and vice versa.
And then there is a case where there is no correlation. In this case, when the marketing expenses increase, their revenue may or may not increase.
Note that the slope only tells us whether the data is positively or negatively correlated. it doesn’t tell us how closely it’s related.
For example, in our example, by looking at the trendline we cannot say how much the revenue will go up when the marketing expense increases by 100%. This is something that can be calculated using the correlation coefficient.
You can find that using the below formula:
=CORREL(B2:B11,C2:C11)
The correlation coefficient varies between -1 and 1, where 1 would indicate a perfectly positive correlation and -1 would indicate a perfectly negative correlation
In our example, it returns 0.945, indicating that these two variables have a high positive correlation.
Identifying Clusters using Scatter Chart (Practical Examples)
One of the ways I used to use scatter charts in my work as a financial analyst was to identify clusters of data points that exhibit a similar kind of behavior.
This usually works well when you have a diverse data set with less overall correlation.
Suppose you have a data set as shown below, where I have 20 companies with their revenue and profit margin numbers.
When I create a scatterplot for this data, I get something as shown below:
In this chart, you can see that the data points are all over the place and there is a very low correlation.
While this chart doesn’t tell us much, one way you can use it to identify clusters in the four quadrants in the chart.
For example, the data point in the bottom left quadrant are those companies where the revenue is low and the net profit margin is low, and the companies in the bottom right quadrant are those where the revenue is high but the net profit margin is low.
This used to be one of the highly discussed charts in the management meeting when we used to identify prospective customers based on their financial data.
Different Types of Scatter Plots in Excel
Apart from the regular scatter chart that I have covered above, you can also create the following scatter plot types in Excel:
- Scatter with Smooth Lines
- Scatter with Smooth Lines and Markers
- Scatter with Straight Lines
- Scatter with Straight Lines and Markers
All these four above scatter plots are suitable when you have fewer data points and when you’re plotting two series in the chart.
For example, suppose you have the Marketing Expense vs Revenue data as shown below and you want to plot a scatter with smooth lines chart.
Below are the steps to do this:
- Select the dataset (excluding the company name column)
- Click the Insert tab
- In the Charts group, click on the Insert Scatter Chart option
- Click on Scatter with Smooth Lines and Markers options
You will see something as shown below.
This chart can quickly become unreadable if you have more data points. This is why it is recommended to use this with fewer data points only.
I have never used this chart in my work as I don’t think it gives any meaningful insight (since we can’t plot more data points to it).
Customizing Scatter Chart in Excel
Just like any other chart in Excel, you can easily customize the scatter plot.
In this section, I will cover some of the customizations you can do with a scatter chart in Excel:
Adding / Removing Chart Elements
When you click on the scatter chart, you will see plus icon at the top right part of the chart.
When you click on this plus icon, it will show you options that you can easily add or remove from your scatter chart.
Here are the options that you get:
- Axes
- Axis Title
- Chart Title
- Data Labels
- Error Bars
- Gridlines
- Legend
- Trendlines
Some of these options are already present in your chart, and you can remove these elements by clicking on the checkbox next to the option (or add these by clicking the checkbox if not checked already).
For example, if I want to remove the Chart Title, I can simply uncheck the option and it would be gone,
In case you need more control, you can click on the little black arrow that appears when you hover the cursor hover over any of the options.
Clicking on it will give you more options for that specific chart element (these open as a pane on the right side).
Note: All the screenshots I have shown you are from a recent version of Excel (Microsoft 365). In case you’re using an older version, you can get the same options when you right-click on any of the chart elements and click on the Format option.
Let’s quickly go through these elements and some of the awesome customizations you can do to scatter charts using it.
Axes
Axes are the vertical and horizontal values that you see right next to the chart.
One of the most useful customizations you can do with axes is to adjust the maximum and minimum value it can show.
To change this, right-click on the axes in the chart and then click on Format axes. This will open the Format Axis pane.
In the Axis option, you can set the minimum and maximum bounds as well as the major and minor units.
In most cases, you can set this to automatic, and Excel will take care of it based on the dataset. But in case you want specific values, you can change them from here.
One example could be when you don’t want the minimum value in the Y-axis to be 0, but something else (say 1000). Changing the lower bound to 1000 will adjust the chart so that the minimum value in the vertical axis would then be 1000.
Axis Title
The Axis title is something you can use to specify what the X and Y-axis represent in the scatter chart in Excel.
In our example, it would be the Net Income for the X-axis and Marketing Expense for the Y-axis.
You can choose to not show any axis title, and you can remove these by selecting the chart, clicking on the plus icon, and then unchecking the box for Axis title.
To change the text in the axis title, simply double-click on it and then type whatever you want as the axis title.
You can also link the Axis title value to a cell.
For example, if you want the value in cell B1 to show up in the vertical axis title, click on the axis title box and then enter =B1 in the formula bar. This will show the value in cell B1 in the axis title.
You can do the same for the horizontal axis title and link it to a specific cell. This makes these titles dynamic and if the cell value changes, the axis titles will also change.
If you need more control on formatting the axis titles, click on any axis, right-click and then click on Format Axis Title.
With these options, you can change the fill and border of the title, change the text color, alignment, and rotation.
Chart Title
Just like Axis titles, you can also format the Chart title in a scatter plot in Excel.
A chart title is usually used to describe what the chart is about. For example, I can use ‘Marketing Expense Vs Revenue’ as the chart title.
If you don’t want the chart title, you can click and delete it. And in case you don’t have it, select the chart, click on the plus icon and then check the Chart Title option.
To edit the text in the Chart title, double-click on the box and manually type the text you want there. And in case you want to make the chart title Dynamic, you can click the title box and then type the cell reference or the formula in the formula bar.
To format the chart title, right-click on the Chart Title and then click on the ‘Format Chart Title’ option. This will show the Format Chart Title pane on the right.
With these options, you can change the fill and border of the title, change the text color, alignment, and rotation.
Data Labels
By default, data labels are not visible when you create a scatter plot in Excel.
But you can easily add and format these.
Do add the data labels to the scatter chart, select the chart, click on the plus icon on the right, and then check the data labels option.
This will add the data labels that will show the Y-axis value for each data point in the scatter graph.
To format the data labels, right-click on any of the data labels and then click on the ‘Format Data Labels’ option.
This will open the former data labels pane on the right, and you can customize these using various options listed in the pane.
Apart from the regular formatting such as fill, border, text color, and alignment, you also get some additional label options that you can use.
In the ‘Label Contains’ options, you can choose to show both the X-axis and the Y-axis value, instead of just the Y-axis.
You can also choose the option ‘Value from Cells’. which will allow you to have data labels that are there in a column in the worksheet (it opens a dialog box when you select this option and you can choose a range of cells whose values would be displayed in the data labels. In our example, I can use this to show company names in the data labels
You can also customize the position of the label and the format in which it’s shown.
Error Bars
While I have not seen error bars being used in scatter charts, Excel does have an option that allows you to add these error bars for each data point in the scatterplot in Excel.
To add the error bars, select the chart, click on the plus icon, and then check the Error Bars option.
And if you want to customize these error bars further, right-click on any of these error bars and then click on the ‘Format Error Bars’ option.
This will open the ‘Format Error Bars’ pane on the right, where you can customize things such as color, direction, and style of the error bars.
Gridlines
Gridlines are useful when you have a lot of data points on your chart as it allows the reader to quickly understand the position of the data point.
When you create a scatterplot in Excel, gridlines are enabled by default.
You can format these gridlines by right-clicking on any of the gridlines and clicking on the Format Gridlines option.
This will open the Format Gridlines pane on the right way you can change the formatting such as the color, thickness, of the gridline.
Apart from the major gridlines that are already visible when you create the scatter diagram, you can also add minor gridlines.
Between two major gridlines, you can have a few minor gridlines that further improve the readability of the chart in case you have a lot of data points.
To add minor horizontal or vertical gridlines, select the chart, click the plus icon, and hover the cursor over the Gridlines option.
Click the thick black arrow there appears and then check the ‘Primary Minor Horizontal’ or ‘Primary Minor Vertical’ option to add the minor gridlines
Legend
If you have multiple series plotted in the scatter chart in Excel, you can use a legend that would denote what data point refers to what series.
By default, there is no legend when you create a scatter chart in Excel.
To add a legend to the scatter chart, select the chart, click the plus icon, and then check the legend option.
To format the legend, right-click on the legend that appears and then click on the ‘Format Legend’ option.
In the Format Legend pane that opens up, you can customize the fill color, border, and position of the legend in the chart.
Trendline
You can also add a trendline in the scatter chart that would show whether there is a positive or negative correlation in the data set.
I’ve already covered how to add a trendline to a scatter chart in Excel in one of the sections above.
3D Scatter Plot in Excel (are best avoided)
Unlike a Line chart, Column chart, or Area chart, there is no inbuilt 3D scatter chart in Excel.
While you can use third-party add-ins and tools to do this, I cannot think of any additional benefit that you will get with a 3D scatter chart as compared to a regular 2D scatter chart.
In fact, I recommend staying away from any kind of 3D chart as it has the potential of misrepresenting the data and portions in the chart.
So this is how you can create a scatter plot in Excel and customize it to make it fit your brand and requirements.
I hope you found this tutorial useful.
Other Excel tutorials you may also like:
- How to Spot Data Point in Excel Scatter Chart
- KPI Dashboard in Excel – Extract List of Companies from the Scatter Chart
- How to Make a PIE Chart in Excel
- How to Create an Area Chart in Excel
- How to Create Combination Charts in Excel
- Creating Actual vs Target Chart in Excel
- Calculate Area Under Curve in an Excel
