# Correlation chart

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

• Positive:

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.

• Negative:

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 8: The final Correlation Graph is created.

### Generate Correlation Direction to Find Positive/Negative Relation

Syntax: = Correl(array1, array2)

Where:

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.