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.

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.

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:

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:

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.

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:

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