Excel for Statistics > Find Correlation in Excel 2013
Correlation in Excel 2013: Overview
Finding Pearson’s correlation coefficients by hand is ugly and involves a lot of lengthy math. However, Excel can make those calculations for you in a fraction of a second. You have two options for finding correlation in Excel 2013: The CORREL function or the Data Analysis Toolpak. If you’re familiar with entering functions in Excel you could enter the CORREL command:
=CORREL(array 1, array 2)
For example, =CORREL(A2:A6,B2:B6)
However, the Data Analysis Toolpak is much easier overall, because you don’t have to remember (or hunt for) an array of functions; They are all just listed in the Data Analysis list. If Data Analysis isn’t showing to the far right of the data tab, make sure you have loaded the Data Analysis Toolpak. The Data Analysis Toolpak is an optional add-in to Excel which gives you access to many functions, including Correlation, Linear Regression, Histograms, t tests, z-tests and ANOVA one way and two way tests.
Step 1: Type your data into a worksheet in Excel. The best format is two columns. Place your x-values in column A and your y-values in column B.
Step 2: Click the “Data” tab and then click “Data Analysis.”
Step 3: Click “Correlation” and then click “OK.”
Step 4: Type the location for your x-y variables in the Input
Range box. Or, use your cursor to highlight the area where your variables are located.
Step 5: Click either the “columns” or “rows” option to let Excel know how your data is laid out. In most cases, you’ll click “columns” as that’s the standard way to lay out data in Excel.
Step 6: Check the “Labels in first row” if you have column headers.
Step 7: Click the “Output Range” text box and then select an area on the worksheet where you want your output to go.
Check out our YouTube channel for more Excel tips and help!
If you prefer an online interactive environment to learn R and statistics, this free R Tutorial by Datacamp is a great way to get started. If you're are somewhat comfortable with R and are interested in going deeper into Statistics, try this Statistics with R track.Comments are now closed for this post. Need help or want to post a correction? Please post a comment on our Facebook page and I'll do my best to help!