Catalog

How to Create a Scatter Plot in Excel [Step-by-Step]

August 19, 2023 1.2K views

Scatter plots serve as a visual representation of the relationship between two variables. They are invaluable tools for professionals across diverse fields such as mathematics, medicine, and finance. By analyzing the patterns formed by the plotted points, experts can deduce the nature of the connection between the variables. In this guide, you'll master how to create a Scatter plot in excel with multiple data sets!

What is a Scatter Plot in Excel?

A Scatter plot offers a dynamic means of depicting data with interconnected variables. Within Excel, this tool empowers in-depth data analysis by facilitating the input of variable values, yielding insightful outcomes that unveil the correlation between the paired variables. Beyond its visual representation prowess, Excel's Scatter plot serves as a robust resource, enabling comprehensive calculations that transcend the surface, thereby enhancing your data interpretation and understanding.

In an Excel Scatter plot you chart each data point with one variable along the horizontal x-axis and the corresponding value of the second variable along the vertical y-axis; Scatter plots provide insights into patterns, trends, or possible associations within the data. These plots are particularly valuable when investigating the interdependence of variables, enabling data analysts to discern the existence of connections, dependencies, or potential outliers that might otherwise be concealed within the numerical dataset.

How to Make a Scatter Plot in Excel?

Now that we have established what a Scatter plot is, let’s explore how you can put it to use that will eventually aid you in analyzing your data through the Scatter plot better. In this section, we will show you how to make a scatter plot in Excel using an example.

We have a dataset that contains the stock price and earnings per share (EPS) data for several different companies. You can use this data to create a scatter plot to visualize how the stock price and EPS are related for each company. You can also compare the performance of different companies based on their stock price and EPS.

Step 1: Select the data range that contains the stock price and earnings per share (EPS) data for each company. Make sure to include the column headers if you have them.

Step 2: Go to the Insert tab and click on the Chart icon. This will open a menu where you can choose from different types of charts and graphs.

Step 3: Select Scatter (or X-Y) from the Chart Type list.

This will create a scatter plot that shows the relationship between the stock price and EPS for each company. The scatter plot helps you visualize and analyze the relationship between your variables, and identify any outliers or anomalies in your data.

How to Make a Scatter Plot with Multiple Variables in Excel

You can also create a scatter plot with multiple data sets in Excel. Let’s see how to do this using the example data set below:

In this data set, we have one independent variable (X) and three dependent variables (Y1, Y2, Y3). We can use this data to create a scatter plot to visualize how the independent variable affects the dependent variables.

Step 1: Select all the data for the scatter plot, including the column headers.

Step 2: Go to the Insert tab and locate the Chart section. Choose Scatter (or X-Y) from the Chart Type list.

This will create a scatter plot in Excel with 3/4 variables. By creating a scatter plot with multiple data sets in Excel, you can visualize and analyze multiple relationships and their correlations in a single graph.

How to Design Your Scatter Plot in Excel

You can also customize your scatter plot to improve the visualization and make it more clear and attractive. You can add elements such as trendlines, change themes, or format your data markers. Here are some steps to customize your scatter plot:

Step 1: Chart elements

Add more elements to your scatter plot, click on the “+” icon on the top right corner of the scatter plot to add or remove elements like axis titles, trendlines, or error bars, providing more information and details about your data.

Step 2: Change your chart theme

Change the appearance of your scatter plot, click on the "Brush" icon below the "Chart element" icon. This opens a menu with various Excel themes and templates, allowing you to choose a theme that suits your preferences and purpose.

Step 3: Format your data markers

Format your data markers, such as the color, shape, size, or transparency of the dots, you can double click on any of the data markers on the scatter plot. This will open a formatting option on the right side of the screen, where you can adjust various settings and options for your data markers.

Tips:

  • Clear Data Format: Ensure your data is organized with clear column headers and corresponding values.

  • Label Axes: Clearly label your X and Y axes with descriptive titles to convey the variables being compared.

  • Data Point Markers: Choose distinct markers for data points to differentiate between multiple series on the plot.

How Many Types of Relationships Do Scatter Plot Have in Excel?

1. No Correlation

A Scatter plot that shows no connection between the two variables in a chart would display a chart where no significant relation could be obtained.

An example would be that you are provided two variables; one is the number of chairs needed in each office on a floor of a building and the second variable is the salaries of the employees. No significant connection can be made between the two variables. Such a condition would ultimately represent no correlation.

2. Positive

A positive relation in a Scatter plot would represent a proportional increase in the values of the two variables that are represented over the graph.

An example for this would be that you are presented with two distinct variables; salaries of employees, and their promotions. As the employees get promoted further in their departments, their salaries would increase accordingly, eventually displaying a steady plotting on the graph in an increasing order.

3. Negative

A negative relation in a Scatter plot would ultimately represent an inverse relationship between the two variables represented over the graph. This means as one value increases the other decreases, as opposed to the positive relationship where the other variable would increase accordingly with the first variable.

One such example could be the amount of time studying versus the amount of time playing football. Eventually displaying that when the person plays football longer would study less, displaying unsteady growth between the two variables.

A Free Alternative to Microsoft Office——WPS Office

While the Scatter plot proves to be a valuable asset across various professions, including healthcare, finance, and engineering, its widespread availability owes much to its digitized existence of tools like Excel. While more complex alternatives may have a steeper learning curve, Excel itself comes at a cost when purchasing Office.

Fortunately, WPS Office offers all the features and capabilities of Office 365 but at a more budget-friendly price. It goes beyond being a mere inexpensive alternative; WPS provides additional benefits, such as an all-in-one suite for multitasking with various productivity tools under one window. It also offers the convenience of built-in PDF conversions and editing, surpassing even the capabilities of the expensive Office 365.

FAQs

Q1. How to Change the Values on a Scatter Plot in Excel?

To change the values on a scatter plot in Excel, you can directly edit the data in the spreadsheet. You can change the values in the cells that are linked to the scatter plot, and the scatter plot will update automatically to reflect the changes.

Q2. How to Adjust Points Size in Scatter Plot in Excel?

To adjust the points size in a scatter plot in Excel, right-click on any of the points and select Format Data Series. Then, under Marker Options, choose the size you want. You can also change the shape, color, and border of the points from the same menu.

Simplify Your Data Visualization

In this article, we explored how to create a Scatter plot in excel, which provides a graphical representation of a given dataset. The guide is designed with simplicity to minimize errors and ensure a smooth experience. However, if you prefer a more user-friendly interface, you can achieve the same convenience with WPS Spreadsheet. Download WPS Office now to enhance your data visualization journey.


15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.