This tutorial will guide researchers through creating a scatter plot using Microsoft Excel.
We will cover the following material:
Download the sample spreadsheet for this tutorial.
This sample data contains the following columns:
5Year 2016 ACS Data downloaded from Social Explorer.
Step #1: Open provided sample data in Excel 

Step #2: Hold CTRL and click J and K along the top of the spreadsheet to select the BachelorDegree and Income columns. 

Step #3: Select the Insert Ribbon at the top of Excel and in the Charts category, click the Scatter Plot image. 

Step #4: Move to clear portion of spreadsheet or Cut/Paste to new worksheet. 
Step #1: Single click the chart on a whitespace. Then in the Design Ribbon, select from the available chart styles. 

Step #2: Single click the chart on a whitespace. Then in the Design Ribbon, click Quick Layout and select Layout 1. 

Step #3: Adjust the title, xaxis label, and yaxis label by singleclicking and typing. 

Step #4: Adjust the legend label by rightclicking on empty space on the chart and click Select Data.


Step #5: Under Legend Entries (Series) on the left, click Edit. Change the Series Name to ="County" OK OK 

Step #6: Adjust axes units by formatting the cells of the respective columns. Rightclick K (above the Income field) and select Format Cells. Change to Currency with 0 Decimal Places. 
Direct Relationship: As the XAxis increases, the YAxis also increases. This is evidenced by an upward trending positive distribution of points.  
Inverse Relationship: As the XAxis increases, the YAxis decreases. This is evidenced by an downward trending negativer distribution of points.  
Test!! Is the relationship between income and college education direct or inverse? 
What is linear regression? 
From http://www.stat.yale.edu/Courses/199798/101/linreg.htm Linear regression attempts to model the relationship between two variables by fitting a linear equation to observed data. One variable is considered to be an explanatory variable, and the other is considered to be a dependent variable. A linear regression line has an equation of the form Y = a + bX, where X is the explanatory variable and Y is the dependent variable. The slope of the line is b, and a is the intercept (the value of y when x = 0). 
What is RSquared? 
From http://statisticsbyjim.com/regression/interpretrsquaredregression/ Rsquared is a goodnessoffit measure for linear regression models. This statistic indicates the percentage of the variance in the dependent variable that the independent variables explain collectively. Rsquared measures the strength of the relationship between your model and the dependent variable on a convenient 0 – 100% scale. 
Step #1: Singleclick the chart and click the + and check Trendline. Then click the triangle to the right of the checkbox. 

Step #2: Rightclick the Trendline and select Format Trendline.


Step #3: Move the linear equation and RSquared to another section of the chart. 
Step #1: Move the chart so there are at least two empty rows above and two empty columns to the left. 

Step #2: Select a cell above the chart and then in the Data Ribbon, click the Data Validation button. 

Step #3: In the Data Validation menu, change Allow to List. Under Source click the arrowup button and select the headers from White to PropertyValue and hit Enter. OK You now have a dropdown menu! 

Step #4: Copy and Paste the resulting dropdown 2 or 3 cells to the right. Type X to the left of the first dropdown and Y to the left of the second dropdown. 

Step #5: To the right of PropertyValue (should be Mcolumn) type X. To the right of that type Y. 

The =SUMIF() function  https://support.office.com/enus/article/sumiffunction169b8c99c05c4483a7121697a653039b 
Step #6: Select the cell beneath the newly created X column, and then in the formula bar above paste the following but do not hit enter afterwards! =SUMIF($C$1:$L$1,
,$C2:$L2) Now hit Enter Copy the formula down through the entire column ___________ Repeat the above steps for the Y column. The only difference is to click the dropdown representing Y. 

Voila! Step #7: Delete and recreate chart using the new X and Y columns. 
One Bear Place #97148
Waco, TX 767987148
Copyright © Baylor® University. All rights reserved.
Report It  Title IX  Mental Health Resources  Anonymous Reporting  Legal Disclosures