To insert an X-Y chart, follow these steps.
A scatter plot can be used to show the correlation between two different variables X and Y. By using the points in the plot, the recipient can easily infer how these variables relate to each other.
For example, a scatter plot can show the relationship between a company’s revenue and net profit.
There are five different types of scatter charts in Excel:
- Scatter with Smooth Lines and Markers
- Scatter with Smooth Lines
- Scatter with Straight Lines and Markers
- Scatter with Straight Lines
Another kind of a scatter chart is a bubble chart.
You’ll need data that looks something like this:
In this simple case, you can see the collected data on employee rates and performance. We would like to see if there is a correlation between them. The scatter plot should enable us to do this.
Inserting a scatter chart
Click Insert after selecting the entire dataset (A2 through C9 in this case).
Select the XY chart.
Choose the desired design for your chart.
Newly created XY chart:
Scatter Chart Formatting
Adding axis titles
Let’s start formatting our scatter plot.
The first thing I miss are axis titles. In our case, the horizontal axis is rate, and the vertical axis is performance. We are aware of this, but the person who does not have access to the data does not know it. In this case, we need to add axis titles to avoid the recipient misunderstanding the chart.
To add an axis title, click on the chart, then on the little green plus that appears on the right and select the axis titles option.
As you can see, text boxes next to the axis have been added. Click on them and enter the axis titles. Unfortunately, Excel did not do this on its own.
There is also a possibility to link axis titles with cells. In such situation title of your axis will be taken from the cell.
This is how the Scatter Diagram looks like with titles of axes.
Adding chart title
A chart needs a title. Let’s change it to name your diagram. What would be a graph without the name?
The rule is the same. Double-click the text field above your chart and enter the chart title.
The title should briefly describe the content of your chart. For scatter charts it is a good idea to name it “relationship between X and Y”. It is also possible to enter multiline chart title but it will descrease the size of what you want to show itself.
In case you don’t need the chart title you can remove it. To do that click yout chart and click the small green plus sign on the right hand side from your diagram. Simply, untick the Chart Title option to remove it.
To learn how to get chart title from cell please visit linked dedicated lesson.
Optionally, you can decide to add data labels to your scatter graph.
To do this, click on the chart, select the little green plus sign, and select Data Labels. The data labels appear in the chart. Right-click one of them and select Format Data Labels. A dialog box will appear on the right side of the screen.
In my humble opinion, there are only three options here that are worth considering:
- Series Name
- X Value
- Y Value
This is how the scatter graph looks like with all of them ticked.
As for me, there is too much information in the chart with them. I will remove the data labels from my chart, but you can keep them.
I recommend that you only add data labels when there aren’t too many of them. I prefer things to be short and simple so I don’t like showing too much information on the chart.
Suffice it to mention that such an option exists.
Error bars do not look good on scatter charts. It looks silly to me.
If you need them, you can consider changing the chart type.
Error bars might be useful when the precision is the case in your example. The rate may be equal to 7 but in reality it may differ from 6.3 to 7.6, so it’s worth showing it on the screen.
It is extremely important to choose the grid format that best suits your chart.
Just take a look.
You must know your data. It’s all about precision again.
In my simply example minor gridlines are absolutely obsolete. In your case, they can be crucial.
It all depends on the type of chart and the goal. In scientific cases, minor gridlines are necessary, but in simple cases, you can even remove gridlines completely.
To remove gridlines from the diagram, simply uncheck all options. I will keep the main grid lines both vertical and horizontal.
IMHO’s trendlines are only an essential part of your scatter charts if you care about the future. For charts that only show dependencies, you don’t need to worry about trendlines. If you want to predict a future relationship, you must add at least one of them.
Adding a trendline is the same as with the previous formatting options, so just select the appropriate option to add a trendline to your chart. Additionally, Excel will ask which data set (X or Y) is needed. I chose a rate trend line.
A trend line has appeared on the chart. Right-click on it and select Format Trendline. Here you may notice a few options you may want to set.
I figured my trend was linear, so I chose Linear Trendline. I also don’t think the distant future is easy to predict, so I decided to only predict 2 more periods. In most cases, you’ll enter a larger number here.
Displaying the equation on the graph is a must for me. The equation appears on the graph for me.
You may prefer Display R-squared value on the chart. It’s up to you.
To set intercept tick the dedicated option. You will see additional line in the chart which will try to reach 0 point (or any other chosen) backwards in my case.
You may also notice that linear trendline information has been added to the chart legend at the bottom of the chart.
You can add more than one trendline. You can even add a trendline for each variable in your dataset.
- A scatter plot is used to show the relationship between two variables. If your chart goal is different, choose a different chart type.
- A scatter chart may not be applicable to a large data resource because it will be unreadable.
- First, check if there is any relationship between the two variable datasets; otherwise there is no point in creating a scatter plot. Well, unless you don’t want to show any relationship on the chart.
- It can be extremely important to select the exact grid size in your chart. If your data is very precise, then without a precise grid, you might not be able to show what you want with the visibility you need.