In this tutorial, you'll learn how to make a scatter plot in Excel to create a graphical representation of two sets of correlated data.
Looking at two columns of quantitative data in your Excel spreadsheet, what do you see? Just two sets of numbers. Do you want to see how the two sets are related to each other? The scatter chart is the ideal graphics choice for this.
- Scatter Chart in Excel
- How to arrange data for a scatterplot
- How to Make a Scatter Chart in Excel
- Scatter chart types
- 3D Scatter Chart in Excel
- Scatter chart and correlation
- Customizing the Scatter Chart
- Adjust axis scale to reduce white space
- Add Excel Scatter Chart Labels
- Add a trendline
- Swap X and Y data series
Scatter Chart in Excel
ONEscatter chart(also calledxy chart, oScatter diagram) 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 plot numerical data. Typically, the independent variable is on the x-axis and the dependent variable is on the y-axis. The chart displays values at the intersection of an x and y axis, combined into single data points.
The main purpose of a scatterplot is to show how strong the relationship is, orcorrelation, between the two variables is. The closer the data points fit along a straight line, the greater the correlation.
How to arrange data for a scatterplot
With a variety of built-in chart templates provided by Excel, creating a scatter plot becomes a job in a few clicks. But first, you need to properly organize your source data.
As already mentioned, a scatterplot shows two interrelated quantitative variables. Therefore, you enter two sets of numerical data in two separate columns.
For ease of use, theIndependentThe variable must be inletcolumn, as this column will be plotted on the x-axis. Thedependentvariable (the one affected by the independent variable) must be in theCorrectcolumn and will be plotted on the y-axis.
Addendum.If your dependent column comes before the independent column and there's no way to change that in a worksheet, you canswap the x and y axesdirectly on a chart.
In our example, let's visualize the relationship between the advertising budget for a given month (independent variable) and the number of items sold (dependent variable), then we order the data accordingly:
How to Create a Scatter Chart in Excel
With the source data properly organized, making a scatter plot in Excel requires these two quick steps:
- Select two columns with numeric data, including column headers. In our case, it is the range C1:D13. Do not select any other columns to avoid confusing Excel.
- go to theCashierguide >charlasgroup, click onDispersalchart icon and select the desired template. To insert a classic scatter plot, click on the first thumbnail:
The scatter plot will be immediately inserted into your worksheet:
Basically, you can consider the job done. or you canpersonalizesome elements of your graph to make it look nicer and more clearly convey the correlation between the two variables.
Scatter chart types
In addition to the classic scatter plot shown in the example above, there are a few more templates available:
- Scatter with smooth lines and markers
- Scatter with smooth lines
- Scatter with straight lines and markers
- Scatter with straight lines
spread with linesit is better to use it when you have few data points. For example, this is how you can plot the data for the first four months using the scatterplot with smooth lines and markers:
Excel XY chart templates can also draweach variable separately, presenting the same relations in a different way. For 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 cost and the orange dots represent items sold:
To see all available scatter types in one place, select your data, click theDispersion(X,Y)on the ribbon and clickMore scatter plots…This will open thebuilt-in graphicdialogue withXY (dispersion)selected type and switch between the different templates at the top to see which one provides the best graphical representation of your data:
3D Scatter Chart
Unlike a classic XY scatter plot, a3D Scatter Chartdisplays data points on three axes (x, y, and z) to show the relationship between three variables. Therefore, it is often calledXYZ chart.
Unfortunately, there is no way to create a 3D scatter plot in Excel, even in the new version of Excel 2019. If you need this type of plot for your data analysis, consider using a third-party tool such astrama.ly🇧🇷 The following screenshot shows what kind of 3D scatter plot this tool can draw:
Scatter chart and correlation
To interpret the scatterplot correctly, you must understand how variables can be related to each other. In general, there are three types of correlation:
positive correlation- As the variable x increases, so does the variable y. An example of a strong positive correlation is the amount of time students spend studying and their grades.
negative correlation- As the x variable increases, the y variable decreases. Dropping out of school and grades are negatively correlated: as the number of absences increases, test scores decrease.
no correlation- there is no obvious relationship between the two variables; the dots are spread across the entire graph area. For example, the student's height and grades appear to be uncorrelated, as the former does not affect the latter in any way.
Customizing the XY Scatter Chart in Excel
As with other chart types, almost every element of a scatter chart in Excel can be customized. You can easily change thechart title, addaxis titles, hide thegridlines, choose yourschart colors, and more.
Next, let's focus on some specific customizations of a scatter plot.
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 clear the extra white space.
To reduce the space between the first data point and the vertical axis and/or between the last data point and the right edge of the chart, follow these steps:
- Right-click the x-axis and clickShape Axis...
- About itformat axispanel, set the desiredMinimumyMaximumlimits as appropriate.
- Additionally, you can change theImportantunits that control the spacing between grid lines.
The following screenshot shows my setup:
To remove space between the data points and the top/bottom borders of the plot area, format the vertical y-axis similarly.
Add labels to scatterplot data points
When creating a scatterplot with a relatively small number of data points, you may want to label the points by name to make your image more understandable. This is how you can do it:
- Select the pattern and click the buttonchart elementsbutton.
- Check adata labelsbox, click the small black arrow next to it, and clickMore options…
- About itformat data labelspanel, switch to thetag optionstab (the last one) and set your data labels like this:
- Select thecell valueand then select the range you want to extract the data labels from (B2:B6 in our case).
- If you only want to display the names, uncheck theValor XEuY valueto remove numeric values from labels.
- Specify the position of the labels,Abovedata points in our example.
That is all! All data points in our Excel scatter plot are now labeled by name:
Tip: How to Fix Overlapping Labels
When two or more data points are very close to each other, their labels can overlap, as is the case withEneyMarchlabels on our scatterplot. To fix this, click on the labels and then click on the overlay so that only that label is selected. Point the mouse cursor at the selected label until the cursor changes to a four-headed arrow, then drag the label to the desired position.
As a result, you will have a nice Excel scatter plot with perfectly readable labels:
Add a trendline and equation
To better visualize the relationship between the two variables, you can draw a trendline on your Excel scatter plot, also called a trendline.best fit line.
To do this, right-click on any data point and chooseAdd trendline...no context menu.
Excel will draw a line as close as possible to all the data points so that there are as many points above as below it.
Additionally, you can display thetrend line equationthat mathematically describes the relationship between the two variables. To do this, see theShow equation on chartbox intrend line formatpanel that should appear on the right side of the Excel window immediately after you add a trendline. The result of these manipulations will look like this:
What you see in the screenshot above is usually calledlinear regression chart, and you can find the detailed guidelines on how to create it here:How to Make a Linear Regression Chart in Excel.
How to Change the X and Y Axes on a Scatter Chart
As already mentioned, a scatter plot usually shows the independent variable on the horizontal axis and the dependent variable on the vertical axis. If your chart plots differently, the easiest solution is to swap the source columns in your worksheet and then draw the chart again.
If for some reason it is not possible to rearrange the columns, you can change the X and Y data series directly in a chart. That's how:
- Right-click on any axis and clickSelect Data...no context menu.
- NoSelect data sourcedialog window, click the buttonTo editbutton.
- CopySeries X ValuestowardY series valuesbox and vice versa.
Addendum.To securely edit the contents of theseriescheckboxes, place the mouse pointer on the checkbox and pressF2.
- give a clickOKtwice to close both windows.
As a result, your Excel scatter chart will undergo this transformation:
Addendum.If you need to find a specific data point on a graph, this tutorial will show you how.find, highlight, and label a data point in a scatterplot.
This is how you create a scatter plot in Excel. In our next tutorial, we'll continue this topic and show you how to quickly find and highlight a given data point in a scatterplot. Please stay tuned!
You might also be interested in
- How to Make a Chart in Excel
- How to Customize an Excel Chart
- Make an Excel chart from different worksheets
- How to Make a Heatmap 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 do you make a scatter plot in Excel with two rows of data? ›
Highlight the two columns you want to include in your scatter plot. Then, go to the “Insert” tab of your Excel menu bar and click on the scatter plot icon in the “Recommended Charts” area of your ribbon. Select “Scatter” from the options in the “Recommended Charts” section of your ribbon.
How do I make a scatter graph? ›- Collect pairs of data where a relationship is suspected.
- Draw a graph with the independent variable on the horizontal axis and the dependent variable on the vertical axis. ...
- Look at the pattern of points to see if a relationship is obvious. ...
- Divide points on the graph into four quadrants.
Along the top ribbon, click the Insert tab and then click Insert Scatter (X, Y) within the Charts group to produce the following scatterplot: What is this? The (X, Y) coordinates for each group are shown, with each group possessing a unique color.
How do you make a scatter plot with multiple variables? ›If we want to make a scatter plot or a line chart with multiple data shown in the same plot, we first use the plot() function to show one data set along the x-axis and another data set on the y-axis. Then we add more data sets to the plot with the points() or lines() functions.
How do you make an XY scatter plot in sheets? ›Open the dropdown menu for Chart type, and scroll down to find the Scatter chart option. Select it, and your data will convert to an x-y graph.
How do you plot a scatter graph with 3 variables in Excel? ›- STEP 2: Go to Insert > Insert Scatter Chart or Bubble Chart > Bubble.
- STEP 5: Under Axis Options > Minimum >Set the value to 0.
- STEP 2: Go to Insert > Insert Scatter Chart or Bubble Chart > 3D Bubble.
On the All Charts tab, choose Combo, and then pick the Clustered Column - Line on Secondary Axis chart. Under Choose the chart type and axis for your data series , check the Secondary Axis box for each data series you want to plot on the secondary axis, and then change their chart type to Line.
Where can I create a scatter plot? ›- Highlight data you want to plot.
- Go to the “Insert” tab.
- Find the “Charts” section.
- Click on the scatter plot icon.
- Edit ranges, axis titles, chart title, and anything else you want to adjust.
There are three types of scatter plots or charts: U-shaped, linear and exponential. These are the three most important ones: positive, negative, or no correlation.
How do I make a scatter plot with different colors in Excel? ›In a chart, click to select the data series for which you want to change the colors. On the Format tab, in the Current Selection group, click Format Selection. tab, expand Fill, and then do one of the following: To vary the colors of data markers in a single-series chart, select the Vary colors by point check box.
Can a scatter plot have two sets of data? ›
As already mentioned, a scatter graph displays two interrelated quantitative variables. So, you enter two sets of numeric data into two separate columns. For ease of use, the independent variable should be in the left column as this column is going to be plotted on the x axis.
How do I link all dots in Excel scatter plot? ›Click on the “select data” option. On the Select Data Source window click on the “hidden or empty cells” button located on the bottom left of the window. Click the “connect data points with a line” radar button. The press OK.
How do you make a scatter plot with 4 quadrants in Excel? ›- First, delete the trend line from your scatter diagram.
- Second, reformat your X-Axis. ...
- Third, reformat your Y-Axis. ...
- Once you have updated both the X and Y Axis Values, you should have a XY Scatter Diagram with Four Quadrants!
A Scatter (XY) Plot has points that show the relationship between two sets of data.
How do I create a chart in Excel with 4 sets of data? ›- Hi Niranjan, Create a column chart of all four data series.
- Right click on one in the chart; select Chart Type and convert to line.
- Repeat with second data series.
- Right click one of the data series; Format Data Series and specify Secondary.
- y-axis.
- Select the data for which you want to create a chart.
- Click INSERT > Recommended Charts.
- On the Recommended Charts tab, scroll through the list of charts that Excel recommends for your data, and click any chart to see how your data will look. ...
- When you find the chart you like, click it > OK.
- Select a chart to open Chart Tools.
- Select Design > Change Chart Type.
- Select Combo > Cluster Column - Line on Secondary Axis.
- Select Secondary Axis for the data series you want to show.
- Select the drop-down arrow and choose Line.
- Select OK.
- On the View menu, click Print Layout.
- Click the Insert tab, and then click the arrow next to Chart.
- Click a chart type, and then double-click the chart you want to add. ...
- In Excel, replace the sample data with the data that you want to plot in the chart.
Click the "Insert" tab, then "Column" from the Charts group and "Cluster Column" from the drop-down menu. The Cluster Column option is the left-most option of each of the column types, such as 2-D, 3-D or Cylinder. The cluster column chart is automatically created by Excel on the same page as your data.
How do you graph the relationship between three variables? ›There are a number of ways to show the relationship between three variables. One of the most common ways this is done is to add a third variable to a scatter plot of and two continuous variables. The third variable would be mapped to either the color, shape, or size of the observation point.
How do I create a combo chart in Excel with multiple series? ›
To create a combo chart, select the data you want displayed, then click the dialog launcher in the corner of the Charts group on the Insert tab to open the Insert Chart dialog box. Select combo from the All Charts tab. Select the chart type you want for each data series from the dropdown options.
What is the best graph for two sets of data? ›A Dual Axis Line Chart is one of the best graph to compare two sets of data. The chart has a secondary y-axis to help you display insights into two varying data points. More so, it uses two axes to easily illustrate the relationships between two variables with different magnitudes and scales of measurement.
What tool is used for scatter plots? ›Microsoft Excel: This is the most common tool used to create a Scatter Diagram.
What is a scatter plot example? ›A scatter plot can suggest various kinds of correlations between variables with a certain confidence interval. For example, weight and height would be on the y-axis, and height would be on the x-axis. Correlations may be positive (rising), negative (falling), or null (uncorrelated).
What is the difference between a scatter plot and a scatter diagram? ›A scatter plot is also called a scatter chart, scattergram, or scatter plot, XY graph. The scatter diagram graphs numerical data pairs, with one variable on each axis, show their relationship.
Is scatter plot and scatter chart the same? ›A scatter chart, also called a scatter plot, is a chart that shows the relationship between two variables.
What is the best graph to show relationship between two variables? ›The most useful graph for displaying the relationship between two quantitative variables is a scatterplot.
How do you interpret a scatter plot with two variables? ›Interpreting scatter plots
Check for negative relationships between the two variables in the plot. If you see low values for the first variable and high values of the second variable, there is a negative correlation. In this case, a line drawn through the data points will slope downwards i.e. a negative slope .
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 label each data point on a scatter graph in sheets? ›- On your computer, open a spreadsheet in Google Sheets.
- Double-click the chart you want to change.
- At the right, click Customize. Series.
- Check the box next to “Data labels.”
How do you plot a line graph with two sets of data in Excel? ›
- Step 1: Insert Two Data Sets Into the Spreadsheet. ...
- Step 2: Select Both Data Sets. ...
- Step 3: Insert the Line Graph. ...
- Step 4: Choose the “Clustered Column – Line” Subtype. ...
- Step 5: Check Secondary Axis Box.
A scatterplot shows the relationship between two quantitative variables measured for the same individuals. The values of one variable appear on the horizontal axis, and the values of the other variable appear on the vertical axis. Each individual in the data appears as a point on the graph.
How do you make a graph with two lines on Excel? ›You can easily plot multiple lines on the same graph in Excel by simply highlighting several rows (or columns) and creating a line plot.
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.
How do you plot a graph with two variables? ›- Find the x– and y-intercepts of the line. Let. and solve for x. Let. and solve for y.
- Find a third solution to the equation.
- Plot the three points and check that they line up.
- Draw the line.
- On your Excel ribbon, go to the Ablebits tab > Merge group, and click the Combine Sheets button:
- Select all the worksheets you want to merge into one. ...
- Choose the columns you want to combine, Order ID and Seller in this example:
- Select additional options, if needed.
Click the "Insert" tab, then "Column" from the Charts group and "Cluster Column" from the drop-down menu. The Cluster Column option is the left-most option of each of the column types, such as 2-D, 3-D or Cylinder. The cluster column chart is automatically created by Excel on the same page as your data.