Correlation helps to identify the pattern of change between two variables. It has two properties – strength and direction. It is a common statistics concept which can be easily represented in excel using a scatter plot and trend line.
Direction of Correlation
If the correlation coefficient is positive, it shows a direct proportional relation between the variables and an upward slope of the correlation graph.
If variable 1 increases, variable 2 will also increase – and vice versa.
If the correlation coefficient is negative, it shows an inverse proportional relation between the variables and a downward slope of the correlation graph.
If variable 1 increases, variable 2 will also decrease – and vice versa.
Create Correlation Graph in Excel
Step 1: Create the correlation dataset. The independent variable should be in the left-hand side (X-axis) and the dependent variable should be in right-hand side (Y-axis).
Step 2: Select the column for X-axis and Y-axis.
Step 3: Insert -> Charts -> Scatter
Step 4: Name X-axis and Y-axis. Name the chart.
Step 5: Right Click on any datapoint. Add trendline
Step 6: Select the most suitable trendline. Check the following boxes as shown below.
Step 7. Format your trendline.
Step 8: The final Correlation Graph is created.
Generate Correlation Direction to Find Positive/Negative Relation
Syntax: = Correl(array1, array2)
Array1 is the first data range of values.
Array2 is the second data range of values.
Value in Example Above:
- #N/A: If the two arrays are not same in length
- #DIV/0!: If either of the array is blank or standard deviation of the numbers is 0.