Statistics mat 240

MAT 240 Scatterplots in Excel Tutorial This tutorial will guide you though the steps necessary to create scatterplots using your data. It will also walk you through inserting a linear trend line and inserting the regression equation and the R-squared value on the chart.

1. Open your data set in Excel. 2. Select all the data for the two variables you are targeting. (example: median listing price & Median

square feet) a. Tip: holding down the CTRL button while selecting your data will allow you to select two

columns of data that are not next to each other 3. On the Insert tab select Recommended Charts button

4. This will bring up the insert chart dialog box prompting you to select from the list of auto-generated

charts. If Scatter is not one of the options, click the “All charts” tab and select X Y (Scatter) and select

the chart on the right side. Then click “ok”

5. With the new chart selected, in the Chart Design tab, select the Add chart Element button to open

the menu and hover over “trendline” and select “More trendline options”

6. In the Format Trendline sidebar, select “Linear” and at the bottom, check off “Display Equation on

chart” as well as Display R-Squared value on chart”

7. Close the Format Trendline sidebar, and move the equation and R-squared value to the side so that

it is visible. Tip: You can use an empty cell to have Excel calculate the square-root of your R-squared value bu selecting an empty cell in your sheet and using the =sqrt() function.

a. Type your R-squared value into the parenthesis and hit enter.