How to Make Correlation Graph in Excel

by Best Excel Tutorial Charts

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).

correlation dataset

Step 2: Select the column for X-axis and Y-axis.

axes

Step 3: Insert -> Charts -> Scatter

insert scatter

Step 4: Name X-axis and Y-axis. Name the chart.

correlation chart

Step 5: Right-click on any datapoint. Add a trendline.

See also  How to Make a Chart for Large Data Set

Add trendline

Step 6: Select the most suitable trendline. Check the following boxes as shown below.

select trendline

Step 7. Format your trendline.

format 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:

correl

correlation direction

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:

pivot chart sales data setHow to Create Pivot Chart in Excel Thermometer Chart TableHow to Create a Thermometer Chart in Excel Interactive Map Dashboard power queryHow to Create an Interactive Map Dashboard in Excel