How to Create a Scatter Chart in Excel

Scatter charts are powerful tools for visualizing the relationship between two variables, making them ideal for data analysis and decision-making. In this article, we will walk you through the process how to create a scatter chart in Excel, step-by-step.

We will cover the basics of creating a scatter chart, including organizing your data, inserting a scatter chart in Excel, customizing the chart, and assigning data to the x-axis and y-axis. Additionally, we will explore how to create a scatter chart with multiple groups and even three variables in Excel.

By following this article, you will be able to visually analyze relationships between variables, identify patterns or trends in your data, and make data-driven decisions with confidence. So, let’s dive in and learn how to create a scatter chart in Excel!

Below is the overview image of a scatter chart in Excel.

Download Practice Workbook

You can download the workbook used for the demonstration from the download link below.

Types of Scatter Graph Data and Their Correlations in Excel

If you want to use the scatter plot properly, you must know how these variables are connected with one another. There are three types of correlation in scatter plotting:

• Positive Correlation: if with the increase in the x variable, the y variable also increases then it is a positive correlation. The best example of a positive correlation is- the total time a student spent studying vs his scores.
• Negative Correlation: if with the increase in the x variable, the y variable also decreases then it is a negative correlation. The relation between class bunks and obtaining scores is one of the best examples of negative correlation because the amount of class bunks increases and the obtaining scores decrease.
• No Correlation: if there is no connection between the variables then it is called no correlation. For example, students’ height has no correlation with their grades.

Advantages of Scatter Graphs

• Visualize relationships: Scatter charts allow you to visually represent the relationship between two variables, making it easy to identify patterns, trends, or correlations in your data.
• Highlight data points: Scatter charts provide a clear way to highlight individual data points on the chart, making it useful for identifying specific data points or outliers in your data.
• Flexibility: Scatter charts can be customized with different symbols, colors, and sizes for data points, allowing you to convey additional information or add visual emphasis to certain data points.
• Ability to handle large data sets: Scatter charts can handle large data sets with numerous data points, making them suitable for analyzing and visualizing complex data.
• Comparison of data points: Scatter charts allow you to compare data points along the x-axis and y-axis, helping to identify trends or patterns that may not be immediately apparent in tabular data.

Disadvantages of Scatter Graphs

• Scatter charts offer fundamental features for data analysis, such as trendlines, but they might not have the advanced features that come with specialized data analysis tools.
• A scatter plot chart is not a quantitative measure that can be used to calculate the outcome numerically.
• It only provides an approximation of the relationship.
• When data is large, it can cause a mess on the scatter chart.

Step-by-Step Procedures to Create a 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. XY Scatter Plot is one of the best solutions.

A scatter chart can be created easily by using the Insert tab. You need to follow the steps below to create a Scatter Chart in a simple and easy manner. For the purpose of demonstration, we have used the following sample data.

Step 1: Arrange Dataset for Scatter Chart

For example, say you run a little store in your town. Many of your customers come from farther distances, and some of them live near your store. You want to run a survey on your customers to find out whether those who come to your store from a farther distance shop more than those who come from nearby places. Say you have found the following data from your survey. In the following steps, we will arrange the following data set with two variables in order to create a Scatter Chart in Excel.

• On the left, you get the Distance column, and on the right, you get the Amount spent.
• Distances are measured in miles per unit, and amounts spent are in dollars. To get relationships between two variables, we will use the XY data series.

Read More: How to Format Data Series in Excel (with Easy Steps)

Step 2: Utilizing Charts Group to Create a Scatter Chart for 2 Variables

Now, we will draw a scatter chart for two variables. Follow the below steps to create a Scatter Chart smoothly in Excel.

• Firstly, select any cell within the data.
• Then, click the Insert tab.
• Next, from the Charts group of commands, click on the Scatter (X, Y) command.
• A drop-down menu will appear; select the first one, simply the Scatter chart.

• Then, select the chart and click on the (+) symbol.
• After that, click on the Chart Elements below.

• Finally, you will get a Scatter chart like the following image. The X-axis represents the distance in miles, and the Y-axis represents the amount spent.

Read More: How to Create Excel Scatter Plot Color by Group (3 Suitable Ways)

Similar Readings

• How to Select Data for a Chart in Excel (2 Ways)
• Excel VBA: Get Source Data Range from a Chart (with Quick Steps)
• How to Add Multiple Series Labels in Scatter Plot in Excel
• Combine Two Scatter Plots in Excel (Step by Step Analysis)
• How to Make a Correlation Scatter Plot in Excel (2 Quick Methods)

Customizing Scatter Chart in Excel

• Adding / Removing Chart Elements

You can manually edit the graph to add or remove any elements of the chart by using the Add Chart Element option.

• After clicking on the Add Chart Element, you will see a list of elements.
• Next, you have to click on them one by one to add, remove or edit.
• Alternatively, you can find the list of chart elements by clicking the Plus (+) icon from the right corner of the chart.
• Here, you have to mark the elements to add or unmark the elements to remove.
• You will find an arrow on the element, where you will find other options to edit the elements.

• Axes

Axes are the values you can see directly next to the chart, both vertically and horizontally. Axes’ maximum and minimum values can be changed, which is an important customization you can make. To change it, you need to click on Axes and then More Options.

• Therefore, the Format Axis window will appear.
• The major and minor units, as well as the minimum and maximum bounds, can all be configured in the axis option.

• Axis Title

You can describe what the X and Y-axis in an Excel scatter chart represent by using the Axis title. To change more, you need to click on Axis Titles and then More Options.

• Therefore, the Format Axis Title window will appear.
• The title’s fill and border, as well as the text’s color, alignment, and rotation, can all be changed using these options.

• Chart Title

You can format the Chart title in an Excel scatter plot in the same way that you can with the axis titles. Typically, a chart’s title provides information about the chart’s subject. To change more, you need to click on Chart Title and then More Options.

• Therefore, the Format Chart Title window will appear.
• The title’s fill and border, as well as the text’s color, alignment, and rotation, can all be changed using these options.

• Data Labels

When you make a scatter plot in Excel, the default setting prevents the display of data labels. However, adding and formatting these is simple. To change more, you need to click on Data Labels and then More Options.

• Therefore, the Format Data Labels window will appear.
• Along with the standard formatting options like fill, border, text color, and alignment, you also have access to a few extra label options.

• Error Bars

Although error bars haven’t been utilized in scatter charts, Excel does offer a feature that lets you add them for every point of data in the graph. To change more, you need to click on Error Bars and then More Options.

• As a result, a window called Format Error Bars will show up where you can change the error bars’ color, direction, and style.

• Gridlines

When your chart contains a lot of data points, gridlines are helpful because they help the reader understand where each data point is located. Gridlines are automatically turned on when you draw a scatterplot. To change more, you need to click on Gridlines and then More Options.

• Legend

You can add a legend to an Excel scatter chart to show which data point belongs to which series when using multiple series. When you make a scatter chart, it does not come with a legend by default.

Visualizing Scatter Chart with Multiple Designs

The Scatter Chart command of Excel contains multiple designs. It includes Scatter, Scatter with Smooth Lines and Markers, Scatter with Smooth Lines, Scatter with Straight Lines and Markers and Scatter with Straight Lines. All of them are Scatter Charts showing various types of visualization. Now, I will demonstrate how to create multiple design Scatter Charts in Excel.

1. Scatter with Smooth Lines and Markers

• Again, click on the Scatter command from the Charts group.
• Now, choose Scatter with Smooth Lines and Markers from the Scatter Chart command.

• Finally, a Scatter Chart will be created automatically.

2. Scatter with Smooth Lines

• From the Insert tab, click on the Scatter (X, Y) It is in the Charts group of commands.
• Now, click Scatter with Smooth Lines.

• A Scatter chart showing smooth fine lines will be generated.

Read More: How to Add Average Line to Scatter Plot in Excel (3 Ways)

3. Scatter with Straight Lines and Markers

• From the Charts collection of commands, select the Scatter command.
• Select Scatter with Straight Lines and Markers.

• This will provide a Scatter Chart with straight lines and markers.

Read More: How to Connect Dots in Scatter Plot in Excel (with Easy Steps)

4. Scatter with Straight Lines

• Firstly, select Scatter (X, Y)  from the Charts group of commands.
• Then, click Scatter with Straight Lines from the Scatter Chart command.

• Now, you will be able to create a Scatter chart in like the following image.

Read More: How to Add Line to Scatter Plot in Excel (3 Practical Examples)

Creating Scatter Plot with 3 Variables in Excel

Additionally, Scatter Plots can be drawn easily for three variables in Excel. It establishes a relationship between three variables. The process is almost the same as the process we followed earlier to create a scatter chart in Excel for three variables.

Steps:

• Click on any cell within the sample data.
• Now, click the Insert tab.
• Afterward, from the Charts group of commands, select the Scatter command.
• Finally, select the first one from the drop-down menu, which is the Scatter chart.

• After that, select the chart and click on the (+) symbol.
• Then, click on the desired Chart Elements.
• Finally, you will be able to create a desired Scatter chart in Excel with 3 variables.

Read More: How to Create a Scatter Plot with 4 variables in Excel (with Quick Steps)

Advanced Features Scatter Chart in Excel

• Adding Trendline and Equation

To understand the connection between the two variables, you can use the trendline in your chart.

• At the starting point, press right-click on any data point and select Add Trendline.

• Then, look over the Display Equation on the Chart box on the Format Trendline which will come to the right side of your window just after you’ve added a trendline. The result will look similar to this:

• Adjusting Axis Scale (Reducing White Space)

If the points are assembled at the top, bottom, right, left, or any side of the graph, then you may want to clear the space. To reduce the white space follow these steps:

• Initially, right-click on the x-axis, use the Format Axis, and set the Minimum and Maximum options as needed.
• Additionally, you can make changes in the spacing between the grid lines using the Major and Minor.

• Switching X and Y Axes in Scatter Chart

A scatter plot normally uses the horizontal axis to represent the independent variable and the vertical axis for the dependent variable. If you have plotted the graph other than that, then you have to fix it and the easiest way to do that is by swapping the columns.

• Right-click on either the x or y-axis and click the Select Data tab.

• In the Select Data Source dialog box, click Edit.

• Copy ‘Series X value’s to the ‘Series Y values’ box and vice versa.

• After clicking OK, your scatter plot will show this transformation:

• Add an Average Point to a Scatter Plot

To add an average point to a scatter plot, you need to follow the below steps.

• First, right-click on the chart and click on the Select Data option.

• Therefore, the Select Data Source window will appear.
• Click on the Add button below the Legend Entries (Series).

• Then, the Edit Series window will appear where you can rename the series and set the range of X and Y series values.

• Therefore, this will indicate our X and Y averages with only one point on the graph, as seen in the revised graph below.

Common Issues with Scatter Chart in Excel

• Incorrect data plotting: For scatter charts in Excel, the data must be properly arranged in columns or rows. The scatter chart may not plot the data correctly or show inaccurate results if the data is not organized properly, such as if there are missing or misplaced data points.
• Incorrect chart type: Scatter charts are designed to display relationships between two sets of data. The data may not be accurately visualized if you inadvertently select a different chart type, such as a line chart or bar chart, leading to incorrect data interpretation.
• Data labels overlapping: Scatter charts often display data labels to provide additional information about each data point. It can be difficult to understand a chart if there are too many data points or if the data labels are too long because they may overlap and become unreadable.
• Inadequate axis scaling: Scatter charts rely heavily on proper axis scaling to accurately represent the data. The chart may be misinterpreted if the axis scales are not properly set, causing the data points to appear compressed or distorted.

Frequently Asked Questions

1. How to make a scatter plot in Excel with two sets of data?

You may plot two separate sets of data on one scatter plot in Excel by selecting select Data option under the Chart Design tab. Then, add each data range from your two data sets one at a time using the Add option.

2. How to make a scatter plot in Excel with multiple data sets?

You are able to incorporate as many data sets as you would like in an Excel scatter plot. Click Data to select it, then use the Add option to add as many data sets in order as you need. Keep in mind that, if required, you may need to plot some of them on the y-axis at a different scale.

3. How do I make a scatter plot with multiple data in Excel?

Excel’s scatter plotting feature is a great tool for visually comparing and analyzing data from various data sets. Here is a step-by-step tutorial on how to create a scatter plot in Excel using multiple data sets:

Step 1: Open Excel and enter your data

Enter your data into an Excel spreadsheet. For a scatter plot, you will require two or more sets of data. Enter the x-values in one column and the corresponding y-values in another column. Ensure that the data sets are arranged in columns or rows, preferably with labels.

Step 2: Select the data

Click on a blank cell in your Excel spreadsheet, then press Ctrl+A on your keyboard to select all the data in the sheet. As an alternative, you can choose the precise data sets you want to plot by hand by clicking and dragging to highlight the relevant cells.

Step 3: Insert a scatter plot

Go to the “Insert” tab in the Excel ribbon at the top of the screen. In the “Charts” group, click on the “Scatter” chart type button. Choose the scatter plot chart type that you want to use. Excel offers various scatter plot options, such as scatter with straight lines, scatter with smooth lines, or scatter with markers only.

Step 4: Customize the scatter plot

Once you’ve inserted the scatter plot, you can customize it to your liking. Right-click on the chart to open the “Format Chart Area” pane, or use the “Chart Elements” and “Chart Styles” buttons on the Excel ribbon to make changes to the chart title, axis labels, legend, data markers, and more. You can also right-click on the data markers to format them individually, such as changing their shape, size, or color.

Step 5: Add additional data sets

If you have more than two data sets that you want to plot on the same scatter plot, you can repeat the process by selecting the additional data and adding it to the chart. You can either select the data and drag it onto the chart, or right-click on the chart, choose “Select Data,” and then click “Add” to add additional data series.

Step 6: Update and save your scatter plot

If you need to update your scatter plot with new data or make further changes, simply edit the data in the Excel spreadsheet, and the scatter plot will update automatically.

💬 Things to Remember

✎ When two or more data points are very close to each other, their labels can overlap. In this case, to fix this, click on the labels, and then select only the overlapping data. After that, point your mouse cursor to the selected label and wait until the cursor changes to the four-sided arrow, and then drag the label to the desired position. As a result, you will have a perfect Excel scatter plot with better labels.

✎ If you want to switch rows and columns remember that before switching rows and columns, make sure they are all selected.

Discussion

On the chart, it is clear that there is a relationship between distance and the amount spent. You can see from the chart that people who come to the store from farther distances shop more. Moreover, a person who comes from a distance of 0 to 20 miles shops for between 0 and 50 dollars. When customers’ distances are between 40 and 60 miles, they shop for 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 department stores are not available around your residence. So when you finally go there, you will spend more money because you cannot make frequent visits to a long department 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 data series.

Conclusion

These are all the steps you can follow in Excel to create a Scatter Chart in Excel. Hopefully, you can now easily create the needed adjustments. I sincerely hope you learned something and enjoyed this guide. Please let us know in the comments section below if you have any queries or recommendations.

For more information like this, visit ExcelDemy.com.

Related Articles

• How to Change Data Source in Excel Chart (3 Useful Examples)
• Excel Chart Not Updating with New Data (2 Suitable Solutions)
• How to Edit Chart Data in Excel (5 Suitable Examples)
• Add Data Table in an Excel Chart (4 Quick Methods)
• Make a Scatter Plot in Excel with Multiple Data Sets
• How to Add Text to Scatter Plot in Excel (2 Easy Ways)