If you want to perform a regression analysis with Excel, first you need to investigate the relationship between two sets of numbers. But to do that, you need to know how to create a scatter plot in Excel. Many researchers and scientists use scatter plots for data analysis and research.
Therefore, it is important to know how to create scatter plots and the steps you need to follow to get the proper results. If you are looking to learn how to create a scatter plot in Excel, you have come to the right place. Here is a guide with all the steps to create a scatter plot in Excel.
What Is a Scatter Plot in Excel?
A scatter plot is a mathematical diagram that displays values for two variables for a collection of data using Cartesian coordinates on the x and y-axis. In this case, it’s a mathematical diagram that you can create in Microsoft Excel. A scatter plot displays the two sets of values and the pattern of the obtained points, which reveal any existing correlation.
These plots are used in statistical, scientific, and engineering research to illustrate and compare numeric values. Other names for scatter plots include scatter diagrams, scatter charts, scatter graphs, or scattergrams. Some people might mix scatter charts with line graphs.
Scatter charts and line charts have a lot in common, especially when the scatter chart has connecting lines. The way each of these graphic styles displays data along the horizontal axis and the vertical axis is significantly different. Scatter tables show the relationship between two values, and line graphs show only one value.
Uses for a Scatter Plot in Excel
- Statistics and research. In statistical analysis, a scatter plot is used when comparing large amounts of data over time to show whether two variables are related. A scatter diagram is used to determine the correlation between two quantitative variables on the XY chart. With a correlation coefficient, you can measure the strength of the relationship between the variables.
- Data science. Because data science works with analyzing data, scatter plots are one of the most commonly used data visualization tools in this field. For example, many data scientists learn to use Python to create accurate scatter plots when using lots of data. Knowing how to use scatter plot techniques as a data scientist is really important.
- Machine learning and AI. These fields use regression analysis and scatter plots to visualize and analyze data. Machine learning engineers, for example, use Python libraries such as Seaborn or learn Matplotlib to do it. With them, you can draw a scatter plot with the possibility of several semantic groupings. Some of the most used types of plots they use are replot, displot, and catplot.
Creating a Scatter Plot In Excel: Step-by-Step
- Input data and organize variables.
- Display the scatter chart.
- Add new data sets.
- Add titles or change axis labels.
- Add a trendline.
Steps to Create Scatter Plot In Excel: In-Depth
1. Input Data and Organize Variables
The first thing you need to do is input the numerical values you will use in Excel and name your variables. Your first column would be your X-axis, and the second column would be the Y-axis value. If you have a controlled (independent) variable, you need to put that one in the first column.
An important note is that if you have multiple columns and select only two columns, the one on the left column will always appear on the horizontal axis heading, while the right column will appear on the vertical axis heading.
2. Display the Scatter Chart
Once you have inputted the data, select the desired columns, go to the Insert tab in Excel, select the XY Scatter Chart and choose the first scatter plot option. Now you should have a scatter graph shown in your Excel file. With this done, you need to add a chart title to the scatter plot. Click on the Chart Title text on the scatter chart and change the default name.
3. Add New Data Sets
Yes, you can add new data sets in scatter plots. You can do this in two different ways. The decision will depend on whether the new data values have the same X value or a different one. For the same X values, you need to add a new column to the right of the existing data.
Then you need to select the scatter graph and expand the highlighted data tables to the new data column. Click on the plus sign of the scatter graph and add a Legend to differentiate the data sets. The new data will be in a different color.
4. Add Titles or Change Axis Labels
The next step would be to add your title and add labels for your X and Y-axis. To do that, you go to the plus sign on the top right corner of the scattergram and click the checkbox on Axis Titles. If you want to use the same wording of the variables in the axis titles, you need to link the text.
You need to type = in the formula bar and select the cell with your data label. If you like to change the wording, you can select the Axis Title in the scattergram and just type in the different description.
5. Add a Trendline
Excel scatter charts have the option to toggle on trendlines to use to illustrate types of correlation. Use the drop-down menus from the scatter plot diagram, and you will see several trendline options. Change the trendline options if the default lines of your data do not match. Some options to choose from are moving average, linear trendline, etc.
How to Learn More About Excel
- Online courses. Online Excel courses are also an excellent choice if you prefer structured material you can follow from the comfort of your own home. There are many Excel courses that you can find online. Here are a few of the best online courses that you can find.
- Bootcamps. You can enroll in an Excel bootcamp and learn more about Microsoft Excel. Bootcamps are usually intensive in-person programs and offer many levels of Excel training. An Excel bootcamp is one of the best ways to upskill, regardless of whether you are a beginner or an expert in Excel.
- Free Excel learning resources. You can find a lot of free Excel resources online and learn at your own pace. You can find some free courses at Coursera, Udemy, Coursera, LinkedIn, and ExcelCentral. You can also find free tutorial videos on Microsoft Excel Help Center or Youtube.
How to Create a Scatter Plot In Excel FAQ
Yes, you can change chart styles on scatter plots in Excel. You can stylize the chart element with different chart colors or designs by selecting the plot chart and going to the Design tab at the top. There you can pick from a selection of preset styles.
Yes. Based on the correlation, there can be three types of scatter plot chart types. These plot types are positive correlation, negative correlation, and no correlation. When it comes to showing scatter graph trendlines on Excel, there can be exponential, logarithmic, moving average, polynomial, power, and linear trendline.
You can use the scatter diagram for several things. You can use it to identify potential root causes of problems with scatter plot analysis. You can also use it when you want to determine the relationship between two variables.
No, unfortunately, Microsoft Excel doesn’t offer dot plot charts. There is a way to create dot plots if you combine a scatter chart and a simple bar chart. Even though it contains dots, a scatter plot is not the same as a dot plot.
About us: Career Karma is a platform designed to help job seekers find, research, and connect with job training programs to advance their careers. Learn about the CK publication.
