How to create a scatterplot in Excel (2023)

In this tutorial, you'll learn how to create a scatterplot in Excel to create a graphical representation of two correlated data sets.

What do you see when you look at two columns of quantitative data in your Excel spreadsheet? Just two sets of numbers. Want to see how the two sets are related? The scatter chart is the ideal chart option for this.

• Scatterplot in Excel
• How to Organize Data for a Scatter Plot
• How to Create a Scatter Chart in Excel
• Types of Scatter Plots
• 3D Scatter Plot Without Excel
• Scatter and Correlation Plot
• Fit Scatter Plot
• Adjust axis scale to reduce white space
• Add Labels to Excel Scatter Charts
• Add a trend line
• Swap X and Y data series

Scatterplot in Excel

Ascatter plot(also calledXY diagram, oscatter plot) is a two-dimensional graph that shows the relationship between two variables.

In a scatter chart, the horizontal and vertical axes are value axes that represent numeric data. Usually, the independent variable is on the x-axis and the dependent variable is on the y-axis. The chart shows values ​​at the intersection of the x and y axes, combined into single data points.

The main purpose of a scatterplot is to show how strong the relationship is, orcorrelation, between the two variables. The closer the data points are along a straight line, the greater the correlation.

How to Organize Data for a Scatter Plot

With a variety of built-in chart templates provided by Excel, creating a scatterplot becomes a one-click job. But first, you need to properly organize your source data.

As mentioned above, a scatterplot shows two related quantitative variables. Then you enter two sets of numerical data in two separate columns.

To facilitate handling, theno matterthe variable must be inlinksColumn because this column plots on the x-axis. EastdependentVariable (the one affected by the independent variable) must be inTo the rightcolumn y is plotted on the y-axis.

AdviceIf your dependent column comes before the independent column and there's no way to change that in a worksheet, you can do thisSwap the X and Y axesdirectly on a chart.

(Video) How to Make a Scatter Plot in Excel

In our example, we visualize the relationship between the advertising budget for a given month (independent variable) and the number of items sold (dependent variable) and organize the data accordingly:

How to Create a Scatter Chart in Excel

With the source data properly organized, creating a scatter chart in Excel requires two quick steps:

1. Select two columns of numeric data, including the column headers. In our case it is the interval C1:D13. Do not select other columns to avoid confusing Excel.
2. Go tomissionguide >Chatgroup, clicksprinkleChart icon and select the desired template. To insert a classic scatterplot, click the first thumbnail:

The scatterplot is immediately inserted into your worksheet:

Basically, you can look at the work done. or you canadjustsome elements of your chart to make it look better and to convey the correlation between the two variables more clearly.

Types of Scatter Plots

In addition to the classic scatterplot shown in the example above, there are a few more templates available:

• Scatter with smooth lines and markers
• Dispersion with smooth lines
• Scatter with straight lines and markers
• Dispersion with straight lines

scatter with linesit is best used when you have few data points. For example, to plot the data for the first four months using the scatterplot with smooth lines and ticks:

Excel's XY plot templates can also ploteach variable separately, representing the same relationships in different ways. To do this, you need to select 3 columns with data: the leftmost column with text values ​​(labels) and the two columns with numbers.

In our example, the blue dots represent advertising costs and the orange dots represent items sold:

To see all available scatter types in one place, select your data, click the buttonDispersion (X,Y)on the ribbon, and then clickMore scatter diagrams...This opens theside chartdialog withXY (dispersion)Type selected and cycle through the different templates above to see which one offers the best graphical representation of your data:

Estrediagrama 3D

Unlike a classic XY scatter diagram, aEstrediagrama 3Ddisplays data points on three axes (x, y, and z) to show the relationship between three variables. Therefore, it is often referred to as aXYZ chart.

(Video) Creating a Scatter Plot in Excel 2016

Unfortunately, there is no way to create a 3D scatter chart in Excel, even in the new version of Excel 2019. If you urgently need this type of chart for your data analysis, consider using a third-party tool liketrama.ly🇧🇷 The following screenshot shows what kind of 3D scatterplot this tool can draw:

Scatter and Correlation Plot

To correctly interpret the scatterplot, you must understand how the variables can be related to each other. There are three types of correlations:

positive correlation- If the variable x increases, the variable y also increases. An example of a strong positive association is the time students spend studying and their grades.

negative correlation- As the variable x increases, the variable y decreases. Dropout and grades are negatively correlated: test scores fall as the number of absences increases.

no correlation- there is no obvious relationship between the two variables; The points are distributed over the entire area of ​​the graph. For example, height and student grades do not seem to have any correlation, since the former does not affect the latter in any way.

Customize XY Scatter Chart in Excel

As with other types of charts, almost every element of a scatter chart in Excel can be customized. You can easily change them.diagram title, Addaxis title, hide thisgrid lines, choose yourschart colors, and more.

Next, we'll focus on some specific settings for a scatterplot.

Adjust axis scale (reduce white space)

If your data points are clustered at the top, bottom, right, or left of the chart, you may want to delete the extra white space.

To decrease the distance between the first data point and the vertical axis and/or between the last data point and the right edge of the graph, do the following:

1. Right click on the x axis and clickFormat axis...
2. Noformat axisrange, set the desiredMinimummimaximumlimits as appropriate.
3. You can also change themHauptUnits that control the spacing between grid lines.

The following screenshot shows my setup:

(Video) Making Scatter Plots/Trendlines in Excel

Similarly, to remove the space between the data points and the top/bottom borders of the chart area, format the vertical y-axis.

Add Labels to Scatterplot Data Points

If you are creating a scatterplot with a relatively small number of data points, you may want to label the points with names to make the image easier to understand. See how you can do this:

1. Select the chart and clickchart elementsGusto.
2. check thosedata labelClick on the little black arrow next to it and click onMore options…
3. Noformat data labelsarea, go tolabeling optionstab (the last one) and set your data labels like this:
• To choosecell valueand then select the range you want to get data labels from (B2:B6 in our case).
• If you want to display only names, turn this option off.valor xmeY valueto remove numeric values ​​from labels.
• Specify the position of the label,Abovedata points in our example.

Is that! All the data points in our Excel scatterplot are now labeled with names:

Tip: How to fix overlapping subtitles

When two or more data points are close together, their labels may overlap, as is the case withJanuarymiDamageLabels on our scatterplot. To fix this, click the labels and then click the overlay label so that only that label is selected. Hover the mouse pointer over the selected label until the cursor changes to a four-headed arrow, then drag the label to the desired location.

The result is a beautiful Excel scatterplot with perfectly legible labels:

Add a trend line and an equation

To better visualize the relationship between the two variables, you can draw a trend line, also known as an a, on your Excel scatter chart.line of best fit.

To do this, right-click on any data point and selectAdd trend line...no context menu.

Excel draws a line as close to each data point as possible, so that there are as many points above the line as there are below it.

(Video) Creating an XY Scatter Plot in Excel

Also, you can view theEquation for the trend linethat mathematically describes the relationship between the two variables. To do this, check theShow equation on graphbox intrend line formatArea that should appear on the right side of the Excel window immediately after adding a trend line. The result of these manipulations looks like this:

What you see in the screenshot above is often calledLinear regression plots, and you can find the detailed build guidelines here:How to Create a Linear Regression Chart in Excel.

How to Swap the X and Y Axes in a Scatter Plot

As mentioned above, a scatterplot usually shows the independent variable on the horizontal axis and the dependent variable on the vertical axis. If your chart draws differently, the simplest solution is to swap the source columns in your worksheet and redraw the chart.

If for some reason it is not possible to rearrange the columns, you can swap the X and Y data series directly on a chart. To see how:

1. Right click on any axis and clickSelect dates...no context menu.
2. In themSelect data sourcedialog box clickto editGusto.
3. copy ofX Series Valuesfor himValues ​​of series Ybox and vice versa.

Adviceto the content ofseriebox, move the cursor inside the box and pressF2.

4. cliqueOKtwice to close both windows.

As a result, your Excel scatter chart will undergo this transformation:

AdviceIf you need to find a specific data point on a chart, this tutorial will show you how.Find, highlight, and label a data point on a scatterplot.

How to create a scatter chart in Excel. In our next tutorial, we'll continue this topic and show you how to quickly find and highlight a specific data point on a scatterplot. Please hold the line!

You may also be interested in

• How to create a chart in Excel
• How to customize an Excel chart
• Create an Excel chart from different worksheets
• How to create a heat map in Excel
• How to create a Pareto chart in Excel
• Highlight active row and column in Excel
• How to draw a line chart in Excel

FAQs

How many columns of data are required to create a scatterplot? ›

In order to create a scatter plot, we need to select two columns from a data table, one for each dimension of the plot. Each row of the table will become a single dot in the plot with position according to the column values.

Why is my scatter plot not showing all data Excel? ›

This is caused by the large data, so you need to control the data in visual. You could try to use filter or slicer to control it. In addition, the "data volumn" is available in new version of powerbi desktop, you could try to update desktop to see whether it work or not.

How do you ensure data accuracy in Excel? ›

When data entered does not match your specifications, Excel will display an Error Alert that will prompt the user to try again with the correct format. Select the cell or cells that you wish to check during entry. On the Data tab, in the Data Tools group, click Data Validation to open the Data Validation dialog box.

How do you increase the accuracy of a trendline in Excel? ›

To do this, follow these steps:
1. In the chart, select the trendline equation.
2. On the Format menu, click Selected Data Labels.
3. Select the Number tab, and then select Number in the Category list.
4. In the Decimal places box, increase the number of decimal places to 30 so that you can see all the decimal places.
5. Select OK.
Mar 31, 2022

How do you avoid Overplotting scatter plots? ›

Fixes for overplotting include reducing the size of points, changing the shape of points, jittering, tiling, making points transparent, only showing a subset of points, and using algorithms to prevent labels from overlapping.

How do you make a scatter plot look good? ›

Making an excellent scatter plot

Strive for clarity: Each point on a scatter plot represents one data point. You'll want to make sure that these points are big enough to see, but not so big they obscure each other. Unless you have a very good reason to add color, it is probably best to keep a scatter plot grayscale.

How do I smooth scatter data? ›

Scatterplots may be smoothed by fitting a line to the data points in a diagram. This line attempts to display the non-random component of the association between the variables in a 2D scatter plot.

How do I make a scatter plot with two sets of data in Excel? ›

How to create a scatter plot in Excel
1. Select two columns with numeric data, including the column headers. In our case, it is the range C1:D13. ...
2. Go to the Inset tab > Chats group, click the Scatter chart icon, and select the desired template. To insert a classic scatter graph, click the first thumbnail:
Oct 20, 2022

What is the main limitations of scatter diagram method? ›

It does not give us the degree of correlation between two variables.

How many sets of data do you need for a scatter plot? ›

A Scatter (XY) Plot has points that show the relationship between two sets of data.

What type of data is best for scatter plots? ›

This is where Scatter Plot comes in. Scatter Plots are best suited to visualize data for causal relationship insights.

How do I get Excel to show all numbers in a graph? ›

On the Layout tab, in the Labels group, click Data Labels, and then click the display option that you want. Depending on the chart type that you used, different data label options will be available.

How do I show all data in an Excel chart? ›

Show or hide a data table
1. Select a chart and then select the plus sign to the top right.
2. To show a data table, point to Data Table and select the arrow next to it, and then select a display option.
3. To hide the data table, uncheck the Data Table option.

Can a scatter plot have more than 2 variables? ›

You can have more than one dependent variable.

Your data set might include more than one dependent variable, and you can still track this on a scatter plot. The only thing you'll want to change is the color of each dependent variable so that you can measure them against each other on the scatter plot.

Are scatter plots good for large data sets? ›

The Box and Scatter Plot Charts are arguably among the tested and proven charts you can use to visualize large data.

How do you make a scatter graph with 3 variables in Excel? ›

STEP 1: Select the table on where we want to create the chart.
1. STEP 2: Go to Insert > Insert Scatter Chart or Bubble Chart > Bubble.
2. STEP 5: Under Axis Options > Minimum >Set the value to 0.
3. STEP 2: Go to Insert > Insert Scatter Chart or Bubble Chart > 3D Bubble.
Jun 24, 2020

How do you improve data accuracy? ›

10 Tips for Maintaining Data Accuracy
1. Tip 1: Create a centralized database. ...
2. Tip 2: Capture and store all data results. ...
3. Tip 3: Don't put pen to paper. ...
4. Tip 4: Assign permissions to change data. ...
5. Tip 5: Keep data sources in sync. ...
6. Tip 6: Standardize the data entry process. ...
7. Tip 7: Simplify the data entry process.

How can you improve data quality and accuracy? ›

How to Improve Data Quality in Your Organization
1. Assess Your Data. ...
2. Define Acceptable Data Quality. ...
3. Correct Data Errors Up Front. ...
4. Eliminate Data Silos. ...
5. Make Data Accessible to All Users. ...
6. Use the Correct Data. ...
7. Impose a Defined Set of Values for Common Data. ...
8. Secure Your Data.

How can you prove data is accurate? ›

How Do You Know If Your Data is Accurate? A case study using search volume, CTR, and rankings
1. Separate data from analysis, and make analysis repeatable. ...
2. If possible, check your data against another source. ...
3. Get down and dirty with the data. ...
4. Unit test your code (where it makes sense) ...
5. Document your process.
Apr 9, 2013

How do you make a trendline like a pro? ›

to draw a trend line in an uptrend, two lows must be connected by a straight line. to draw a trend line in a downtrend line, two highs must be connected by a straight line. a trend line should be connected by at least three highs or lows to make it valid.

What is the most accurate trendline? ›

Trendline reliability A trendline is most reliable when its R-squared value is at or near 1. When you fit a trendline to your data, Graph automatically calculates its R-squared value. If you want, you can display this value on your chart.

Which trendline is more accurate? ›

A trendline is most accurate when its R-squared value is equal or close to 1. (R-squared value is a a number from 0 to 1 that reveals how closely the estimated values for the trendline correspond to your actual data. This is also known as the coefficient of determination.)

What makes a scatter plot weak? ›

The strength of a scatter plot is usually described as weak, moderate or strong. The more spread out the points are, the weaker the relationship. If the points are clearly clustered, or closely follow a curve or line, the relationship is described as strong.

What is a weakness of scatter plot? ›

Disadvantages of scatter diagrams include: Reading scatter diagrams incorrectly may lead to false conclusions that one variable caused the other, when both may have been influenced by a third. A relationship in a scatter diagram may not be apparent because the data does not cover a wide enough range.

What are the common issues with scatter plot? ›

Two common issues have been identified with the use of scatter plots – overplotting and the interpretation of causation as correlation. Overplotting occurs when there are too many data points to plot, which results in the overlapping of different data points.

How do you make a graph more readable? ›

Make Your Charts Easier to Read – 4 Tips
1. Avoid Grid Lines. If you are doing a bar chart, there is no need for vertical lines! ...
2. Avoid Shocking Colors. The default colors of charts might not be best colors to use. ...
3. Use appropriate Units to represent the data. ...
4. Avoid Cluttering Charts.

How do you describe a scatter plot with no correlation? ›

If the points on the scatter plot seem to form a line that slants down from left to right, there is a negative relationship or negative correlation between the variables. If the points on the scatter plot seem to be scattered randomly, there is no relationship or no correlation between the variables.

Which 4 features should you always describe about a scatterplot? ›

A quick description of the association in a scatterplot should always include a description of the form, direction, and strength of the association, along with the presence of any outliers.

How can I make Excel data smoother? ›

Another way to smooth out data series in Excel is to use the Lowess Smoothing tool. To do this, select your data series, then click on the "Data" tab. In the "Data Tools" group, click on the "Lowess Smoothing" button. In the dialog box that appears, select the desired smoothing factor and click OK.

What is jitter in scatter plot? ›

The “jitter” option is a useful tool to help better visualize the data. “Jitter” adds random noise to the observations before generating the scatterplot, yielding a better visual sense of how many observations have each pair of X and Y values. “Jitter” does not modify the data permanently.

How do I statistically compare two sets of data in Excel? ›

When comparing two lists of data, select both columns of data, press F5 key on the keyboard, select the “Go to special” dialog box. Then select “Row difference” from the options. Matching cells of data across the rows in the columns are in white color and unmatched cells appear in grey color.

How do you make a scatter plot with 4 quadrants in Excel? ›

You can also manually convert a scatter plot to a four-quadrant graph
1. First, delete the trend line from your scatter diagram.
2. Second, reformat your X-Axis. ...
3. Third, reformat your Y-Axis. ...
4. Once you have updated both the X and Y Axis Values, you should have a XY Scatter Diagram with Four Quadrants!

How do I display a lot of data in Excel? ›

It's easy! Just select a range of cells next to the data you want to chart, then click Insert on the UI ribbon and click Line in the Sparklines group (you can also click Column or Win/Loss). In the Create Sparklines dialog box, click in the Data Range text box and select the rows or columns of data you want to depict.

How do you make a graph on Excel with multiple sets of data? ›

How to show two sets of data on one graph in Excel
1. Enter data in the Excel spreadsheet you want on the graph. ...
2. Select the data you want on the graph. ...
3. Click the "Insert" tab and then look at the "Recommended Charts" in the charts group. ...
4. Choose "All Charts" and click "Combo" as the chart type.
Sep 29, 2021

How do I organize large amounts of data in Excel? ›

8 Ways to Organize Your Spreadsheets in Excel
1. Use Templates. ...
2. Highlight the Important Data. ...
3. Sort Your Data. ...
4. Use Multiple Sheets! ...
5. Hide Unnecessary Data. ...
6. Hyperlinked Cells Can Save You a Lot of Time. ...
7. Use Formulas and Functions Correctly. ...
8. Shortcuts Can Help You Work Faster.
May 27, 2021

How do I fill 1000 rows in Excel without dragging? ›

Quickly Fill Numbers in Cells without Dragging
1. Enter 1 in cell A1.
2. Go to Home –> Editing –> Fill –> Series.
3. In the Series dialogue box, make the following selections: Series in: Columns. Type: Linear. Step Value: 1. Stop Value: 1000.
4. Click OK.

How do I get Excel to show more than 1000 rows? ›

Quite easy! Go to Table menu, Properties and Connection properties as shown in the screenshot below. In the connection properties window, go to Definition tab and delete MAXROWS 1000 in the Command text as shown in the screenshot below. With that small change, all is done.

How do I select a large amount of data in Excel without dragging? ›

Select one or more rows and columns
1. Select the letter at the top to select the entire column. Or click on any cell in the column and then press Ctrl + Space.
2. Select the row number to select the entire row. ...
3. To select non-adjacent rows or columns, hold Ctrl and select the row or column numbers.

How do I plot 4 sets of data on one graph in Excel? ›

How do I create a chart with 4 sets of data?
1. Hi Niranjan, Create a column chart of all four data series.
2. Right click on one in the chart; select Chart Type and convert to line.
3. Repeat with second data series.
4. Right click one of the data series; Format Data Series and specify Secondary.
5. y-axis.
Jan 18, 2010

How do I add a second series to a scatter plot in Excel? ›

Right-click the chart and choose Select Data. Click Add above the bottom-left window to add a new series. In the Edit Series window, click in the first box, then click the header for column D.

What is the fastest way to select large amounts of data in Excel? ›

To select a larger range, it's easier to click the first cell and hold down the Shift key while you click the last cell in the range. You can scroll to make the last cell visible. To select an entire column or row, click the column or row header.

How do you handle millions of data in Excel? ›

You may generate large csv file yourself manually - save about million of rows from excel as csv, open such csv in Notepad, copy and add or add manually another half millions of rows or so, close the file. Now try to open in Excel - it will show only first million. Try to open in Notepad++ , it will show entire file.

Which Excel graph is best for large data? ›

So which graph is best suited for large data sets? The answer is Box and Plot Column Chart.

How do you organize large amounts of data? ›

Here are 11 tips for making the most of your large data sets.
1. Cherish your data. “Keep your raw data raw: don't manipulate it without having a copy,” says Teal. ...
2. Visualize the information.
3. Show your workflow. ...
4. Use version control. ...
5. Record metadata. ...
6. Automate, automate, automate. ...
7. Make computing time count. ...
8. Capture your environment.
Jan 13, 2020

What is the best type of plot to represent large amounts of time series data? ›

Stream Graph

Stream graphs are great to represent and compare time series data for multiple variables.

Videos

1. KNN (K - Nearest Neighbors ) Algorithm
(junaid khateeb)
2. Excel: Two Scatterplots and Two Trendlines
(BurkeyAcademy)
3. How to Make a Scatter Graph/Plot in Microsoft Excel (Scatter Graph Tutorial)
(Tropical Tech)
4. How to create a scatter plot in Excel with 3 variables
(SpreadCheaters)
5. How to Make and Interpret a Scatter Plot in Excel
(Matt Macarty)
6. How To Make a X Y Scatter Chart in Excel With Slope, Y Intercept & R Value
(The Organic Chemistry Tutor)

References

Top Articles
Latest Posts
Article information

Author: Catherine Tremblay

Last Updated: 07/08/2023

Views: 6475

Rating: 4.7 / 5 (47 voted)

Reviews: 94% of readers found this page helpful

Author information

Name: Catherine Tremblay

Birthday: 1999-09-23

Address: Suite 461 73643 Sherril Loaf, Dickinsonland, AZ 47941-2379

Phone: +2678139151039

Job: International Administration Supervisor

Hobby: Dowsing, Snowboarding, Rowing, Beekeeping, Calligraphy, Shooting, Air sports

Introduction: My name is Catherine Tremblay, I am a precious, perfect, tasty, enthusiastic, inexpensive, vast, kind person who loves writing and wants to share my knowledge and understanding with you.