How to Make a Scatter Plot in Excel and Google Sheets

A scatter plot in Excel or Google Sheets takes about a minute once your data is in the right shape. This guide walks through both programs, step by step, using the menu names from Microsoft’s and Google’s own help pages: how to make a scatter plot in Excel, how to add a trendline with its equation and R², how to plot multiple data sets, how to show a third variable, and how to do the same in Google Sheets. At the end you will find a way to check your numbers and a faster option if you just need the chart.

Before You Start: Arrange the Data

Both programs expect the same layout, and most problems come from getting it wrong.

• One row per case. Each row is one day, one student or one product, and becomes one point.
• X in the first column. Microsoft’s chart guide says to place the x values in one row or column and the corresponding y values in the adjacent rows or columns. Google’s help for scatter charts says the first column holds the values for the X axis.
• Y in the column to the right. In Google Sheets, each further column of Y values becomes another series of points.
• A header row. The names in the first row become the series names in the legend.
• Numbers only. Units belong in the header, not in the cells.

Which variable goes on X? The one you think explains or drives the other. Temperature drives iced drink sales, not the reverse, so temperature goes in the first column.

Here is the sample data used throughout this guide. Copy it into a blank sheet to follow along.

How to Make a Scatter Plot in Excel

These steps follow Microsoft’s support article for Excel for Microsoft 365, Excel 2024 and Excel 2021 on Windows. Other versions may name some tabs and buttons slightly differently.

Step 1: Select the Data

Select both columns, including the header row.

Step 2: Insert the Scatter Chart

Select the Insert tab, then the Insert Scatter (X, Y) or Bubble Chart button, and pick a type under Scatter. Hover over each icon to see its name. The first one, Scatter, shows markers only, which is what you want for most data.

Microsoft’s list of chart types describes the other options: Scatter with smooth lines, and Scatter with straight lines, each with or without markers. Only use the line versions when the order of the points means something.

Step 3: Add a Chart Title

Select the title at the top of the chart and type your own, such as “Noon temperature and iced drinks sold”.

Step 4: Add Axis Titles

Select the chart. On the Chart Design tab, select Add Chart Element, then Axis Titles. Choose Primary Horizontal for the X axis title and Primary Vertical for the Y axis title, then select each title and type the text. Always include units: “Temperature at noon (°F)”, not just “Temperature”.

Step 5: Style It if You Want

The Chart Design tab has chart styles and Quick Layout presets, and the Format tab changes fills and outlines. The data view can be changed with Switch Row/Column or Select Data, both on the Chart Design tab.

On a Mac, Microsoft’s article has its own tab of steps. The chart types and the Chart Design tab are the same, but some buttons sit in different places, so check that tab if a menu is missing.

Add a Trendline, Equation and R² in Excel

Add the Trendline

On Windows, Microsoft’s instructions are: select the chart, select the button at the top right corner of the chart (Chart Elements), and select Trendline. If the chart has more than one series, Excel opens an Add Trendline dialog so you can choose which series gets the line. On a Mac, use Chart Design, Add Chart Element, Trendline, then More Trendline Options.

Excel’s trendline types are Exponential, Linear, Logarithmic, Polynomial, Power and Moving average. For a straight pattern, choose Linear. Microsoft’s trendline documentation says the linear trendline is the least squares best-fit straight line, with m as the slope and b as the intercept.

Show the Equation and R² on the Chart

To format the trendline, select the chart, go to the Format tab, pick the trendline in the Current Selection drop-down, and select Format Selection. The Format Trendline pane opens. It includes options to display the equation and the R-squared value on the chart; Microsoft’s Excel reference documents both settings and notes that they share one data label.

For these ten days the line is y = 1.674x − 63.94. The slope says each extra degree at noon goes with about 1.7 more iced drinks sold, on average. R² of 0.985 means about 98 percent of the day-to-day differences in sales follow the line. Our line of best fit guide explains how to read both numbers and when not to trust them.

The Forward and Backward fields in the same pane extend the line beyond your data. Use them with care: a prediction outside the range you measured is a guess.

Check the Numbers With Formulas

Excel can compute the same values in cells, which is useful for a report or to double-check the chart:

• =SLOPE(B2:B11, A2:A11) for the slope
• =INTERCEPT(B2:B11, A2:A11) for the intercept
• =RSQ(B2:B11, A2:A11) for R²
• =CORREL(A2:A11, B2:B11) for the correlation r

Note the order in the first three: Microsoft’s syntax is SLOPE(known_y's, known_x's), Y first. Swapping the ranges gives a different, wrong answer for the slope and intercept.

Scatter Plot in Excel With Multiple Data Sets

Say you have the same measurements for two cafes. Plot each cafe as its own series so each gets a color, a legend entry and, if you want, its own trendline.

Two Ways to Add the Second Series

• Side by side columns. If both data sets share the same X values, put them next to each other (X, Y for cafe A, Y for cafe B) and select all three columns before inserting the chart. Each Y column becomes a series.
• Different X values. If the data sets have different X values, as in the chart above, start with one data set, then open Select Data from the Chart Design tab and add a second series, pointing it to its own X range and its own Y range.

With two or more series, adding a trendline from the Chart Elements button asks which series to use. Repeat for each series you want a line on.

Scatter Plot in Excel With 3 Variables

A flat scatter plot shows two numbers per point. For a third, you have three options.

Use a Bubble Chart for a Third Number

Microsoft describes the bubble chart as a type of scatter chart where bubble size adds a third data dimension. The columns must be in the order X, then Y, then size. Insert it from the same Insert Scatter (X, Y) or Bubble Chart button. Microsoft’s chart list notes that bubbles can be shown in 2-D or 3-D format, but without a depth axis.

Use Colors for a Third Category

If the third variable is a category, such as cafe, region or product line, make one series per category as described above. This is usually easier to read than bubble sizes.

Use Several Small Plots

For many variables, statisticians use a scatterplot matrix: one small scatter plot for every pair of variables. The NIST/SEMATECH e-Handbook of Statistical Methods describes it, along with the conditioning plot, which draws Y against X separately for each value of a third variable. In Excel that means several small charts side by side.

Scatter Chart or Line Chart in Excel?

This is the most common Excel trap. The two look alike, but Microsoft’s article explains that a scatter chart has two value axes, while a line chart spaces its points evenly along a category axis, whatever the X values are. If your X values are unevenly spaced numbers, such as 62, 65, 68 and 70 degrees, a line chart places them at equal distances and distorts the picture.

Microsoft’s general rule: use a line chart when your x values are not numbers or are evenly spaced labels such as months, and a scatter chart when the x values are numeric.

How to Make a Scatter Plot in Google Sheets

These steps follow the Google Docs Editors Help pages for Google Sheets on a computer.

Step 1: Arrange and Select the Data

Put the X values in the first column and the Y values in the next. Google’s help says each row is a point on the chart and the optional first row supplies the legend names. Select the cells.

Step 2: Insert the Chart

Click Insert, then Chart. The chart editor opens on the right. If the chart Sheets draws is not a scatter chart, change it in the next step.

Step 3: Switch to a Scatter Chart

In the chart editor on the right, click Setup. Under Chart type, open the list and choose Scatter chart. If the editor is closed, double-click the chart to open it again.

Step 4: Add Titles

Click Customize, then Chart & axis title, and set the chart title and each axis title.

Step 5: Add a Trendline

Google’s trendline help gives the steps: double-click the chart, click Customize, then Series, optionally choose a series next to “Apply to”, and check Trendline. Google supports trendlines on bar, line, column and scatter charts. Under Trendline you can set the type, the label and R squared; Google notes that R squared appears only if the chart has a legend. The linear trendline equation is y = mx + b, and the other types are exponential, polynomial, logarithmic, power series and moving average.

Multiple Data Sets in Google Sheets

Add more Y columns next to the first and each becomes a series. For a second range elsewhere in the sheet, go to Setup, then Data range, and click Add another range.

A Third Variable in Google Sheets

Google Sheets also has a bubble chart. Its help page lists the columns: a label, the X values, the Y values, a series name that sets the color, and a number for the bubble size.

Formulas in Google Sheets

Google Sheets has the same regression functions: SLOPE(data_y, data_x), INTERCEPT, RSQ, CORREL and FORECAST, which returns the expected y for a given x. As in Excel, the Y range comes first.

Check Your Chart Against Ours

Least squares gives one answer for one data set, so every program should agree. Paste the ten rows above into Excel, Google Sheets and the scatter plot maker and compare: the slope should round to 1.674, the intercept to −63.94 and R² to 0.985. If your spreadsheet shows something else, the usual causes are the X and Y ranges swapped, a header row included in a formula range, or a line chart used instead of a scatter chart.

Open the sample data in the scatter plot maker

Skip the Spreadsheet: Paste and Export

If you only need the chart, for a report, a lab write-up or slides, the scatter plot maker is quicker:

1. Copy the two columns in Excel or Google Sheets.
2. Paste them into the maker. The header row becomes the axis titles.
3. The line of best fit, its equation and R² appear at once, with the slope, intercept and r listed below.
4. Turn on groups to plot several data sets, each with its own color and line.
5. Download a PNG or an SVG. Nothing is uploaded.

To learn what the finished plot tells you, read what a scatter plot is and how to read one, and for practice, try the scatter plot worksheets.