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 chart and a trend line.

What is Correlation Chart?

Correlation Chart is a type of graph or mathematical scheme that uses Cartesian coordinates for display values typically of two variables for a data set. The data is displayed as a collection of points, each with the value of one position variable on the horizontal axis and the value of a second position variable on the vertical axis.

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 Chart 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 a 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)

Where:

Array1 is the first data range of values.

Array2 is the second data range of values.

Value in Example Above:

Errors:

• #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.

Related posts:

How to Create Pivot Chart in Excel How to Create a Thermometer Chart in Excel How to Create an Interactive Map Dashboard in Excel