How to Use Excel for Correlation
Correlation measures the strength of association between quantitative variables, usually in the form of a correlation coefficient. The value of a correlation coefficient, symbolized by the Greek letter "rho," ranges from -1 for perfect negative correlation to zero for no correlation at all, to +1 for a perfect positive correlation. Microsoft’s Excel spreadsheet program can analyze the level of correlation between two variables through its data analysis function.
Measuring Correlation With Excel
Enter the data to be analyzed. If you are examining the correlation between two variables, a single variable can represent one column in a spreadsheet. Enter the values of each variable in the cells under the corresponding column.
Unlock and install the Data Analysis tool from the Excel add-ins menu. This tool is included in all options of Excel, but must be installed by the user before it can be used. To do this in Excel 2007, click the Microsoft Office help button in the top left corner of an Excel spreadsheet. Then click the button labeled “Excel Options,” which opens a window with a set of options on the left side. Choose “Add-Ins” and select “Analysis ToolPak.” This makes the data analysis tool available for use. In earlier versions of Excel, click the “Tools” menu and then choose “Add-ins” and select “Analysis Tool Pak,” which places the Data Analysis option in your Tools menu for use.
Choose the two variables for which you wish to examine the correlation. Selecting the values to analyze in Excel requires you to click on a cell or column and drag your mouse to choose or define the range of values for the two variables under consideration. This means the variables must be adjacent to each other in your Excel spreadsheet.
Open the Excel Data Analysis tool (under the Data tab in Excel 2007, in the Tools menu in earlier versions), which opens a window with a set of analysis options. Choose “Correlation” and click "OK." This opens the Excel correlation wizard, which asks you to enter the range of data to be analyzed by clicking a column or cell and dragging. For example, if the two variables you want to analyze are in columns A and B in your spreadsheet, then click and drag across these two columns and down the rows until all values are included. Click "OK" when you are finished.
Examine the value of the correlation coefficient, which Excel will display in a new worksheet. Remember that correlation signifies a relationship between two variables, but does not prove causation.