{"id":33228,"date":"2023-11-29T19:34:12","date_gmt":"2023-11-29T18:34:12","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=33228"},"modified":"2024-03-22T16:16:01","modified_gmt":"2024-03-22T15:16:01","slug":"correlation-in-excel","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/correlation-in-excel\/","title":{"rendered":"Correlation in Excel – With Examples!"},"content":{"rendered":"

\"Correlation<\/a>
\nCorrelation in Excel is a statistical measure that gives an insight into how strongly two variables are related to one another. The result of calculating correlation is a number between 1 and -1<\/strong>. Numbers closer to either extreme indicate a stronger relationship, whereas a number closer to 0 indicates a weak or nonexistent relationship<\/strong>. A correlation statistic is tedious to calculate manually. Fortunately, Excel has the CORREL function. In this article, we will explore how to use correlation in Excel, and a couple of practical examples.<\/p>\n

Download the Excel Workbook below to follow along and understand how to use the Correlation in Excel \u2013 download excel\u00a0<\/strong>workbook<\/strong><\/span>CorrelationInExcelupdate.xlsx<\/span><\/a><\/h4>\n

Correlation Classification<\/span><\/strong><\/h3>\n

Positive<\/strong>: As one variable increases so does the other and vice-versa. A coefficient of +1 indicates a perfect positive correlation.
\nNegative<\/strong>: As one variable increases the other has a tendency to decrease. A coefficient of -1 indicates a perfect negative correlation.
\nNo Relationship<\/strong>: Movement in one variable cannot be predicted from the other. A coefficient near 0 indicates no correlation.<\/p>\n

The CORREL Function Syntax<\/span><\/strong><\/h3>\n

=CORREL(array1, array2)<\/strong><\/p>\n

Arguments:
\narray1<\/strong> – A range of cell values.
\narray2<\/strong> – A second range of cell values.<\/p>\n

Positive Correlation<\/span><\/strong><\/h3>\n

Let\u2019s say we are examining the sales data<\/strong> of a clothing company (Column A)<\/strong> in relation to their advertising expenses (Column B)<\/strong> during the first 2 quarters.<\/p>\n

In cell D2<\/strong>, enter the formula<\/a> =CORREL(B2:B7, C2:C7)<\/strong><\/p>\n

\"Correlation<\/a><\/p>\n

\"Correlation<\/a><\/p>\n

As you can see, the number 0.9986885 is closer to the number 1<\/strong>. Indicating that there is indeed a positive correlation<\/strong> between the sales data and the advertising expenses. In other words, an increase in the company’s advertising expenditure corresponded to a rise in sales. Conversely, a reduction in advertising spending resulted in a decrease in sales.<\/p>\n

To better illustrate our data, we can opt to add a line chart.<\/p>\n

Highlight A1:C7<\/strong>, then Right-click<\/strong>.<\/p>\n

\"Correlation<\/a><\/p>\n

 <\/p>\n

Click on Quick Analysis<\/strong>, Charts<\/strong>, and Line<\/strong>.<\/p>\n

\"Correlation<\/a><\/p>\n

\"Correlation<\/a><\/p>\n

Negative Correlation<\/span><\/strong><\/h3>\n

Let\u2019s take the same scenario to illustrate a negative correlation.<\/p>\n

In cell D2<\/strong>, enter the formula =CORREL(B2:B7, C2:C7)<\/strong><\/p>\n

\"Correlation<\/a><\/p>\n

\"Correlation<\/a><\/p>\n

As you can see, the number -0.8935515 is closer to the number -1<\/strong>. Indicating that there is indeed a negative correlation<\/strong> between the sales data and the advertising expenses. In other words, an upturn in advertising expenses is correlated with a downturn in sales, and conversely, a decrease in advertising expenses aligns with an increase in sales.<\/p>\n

No Relationship<\/span><\/strong><\/h3>\n

Let\u2019s take the same scenario to illustrate no correlation.<\/p>\n

In cell D2<\/strong>, enter the formula =CORREL(B2:B7, C2:C7)<\/strong><\/p>\n

\"Correlation<\/a><\/p>\n

\"Correlation<\/a><\/p>\n

As you can see, the number -0.0376513 is closer to 0<\/strong>. Indicating that there is little to no correlation<\/strong> between the sales data and the advertising expenses. In other words, changes in advertising spending appear to have little to no impact on sales performance.<\/p>\n

Data Analysis ToolPak<\/span><\/strong><\/h3>\n

What if you need to quickly compare correlation coefficients between multiple variables? What if you don\u2019t want to deal with formulas<\/a>? This is where the Analysis ToolPak<\/strong> comes in handy.<\/p>\n

The Analysis Toolpak add-in is not enabled in Excel by default. To enable it, go to the File<\/strong> tab.<\/p>\n

Select Options<\/strong>.<\/p>\n

\"Correlation<\/a><\/p>\n

Click Add-ins<\/strong>.<\/p>\n

Select Excel Add-ins<\/strong>, and click Go<\/strong>.<\/p>\n

Check the Analysis Toolpak<\/strong> and click OK<\/strong>.<\/p>\n

\"Correlation<\/a><\/p>\n

Once the Analysis Toolpak is enabled, go to Data<\/strong> and click on Data Analysis<\/strong>.<\/p>\n

\"Correlation<\/a><\/p>\n

Select Correlation<\/strong>.<\/p>\n

\"Correlation<\/a><\/p>\n

Input the data range.<\/p>\n

\"Correlation<\/a><\/p>\n

Click OK<\/strong>.<\/p>\n

\"Correlation<\/a><\/p>\n

Summary<\/strong>:
\nColumn 1 and 2 are positively correlated (0.99). Column 1 and 3 are negatively correlated (-0.89). Column 1 and 4 are not correlated (-0.03) . These correlations are apparent when looking at the line graph.<\/p>\n

Notes<\/strong>:
\nIf an array or reference argument comprises text, logical values, or empty cells, these values are disregarded. However, cells with zero values are taken into account.<\/em><\/p>\n

If array1 and array2 have an uneven number of data points, CORREL produces a #N\/A error.<\/em><\/p>\n

Click here<\/a> to learn How to fix the #VALUE error in Excel formulas<\/p>\n

Should either array1 or array2 be empty, or if the standard deviation (s) of their values equals zero, CORREL results in a #DIV\/0! error.<\/em><\/p>\n

Correlation does not always imply causation. A strong correlation indicates a relationship but does not explain why it exists.<\/em><\/p>\n

Click here<\/a> to learn How to Master Excel Formulas – The Ultimate Guide!
\nClick here<\/a> if you are Having Trouble Understanding Complex Formulas in Excel.
\nClick here<\/a> to check out Microsoft\u2019s tutorial on the CORREL Function!<\/p>\n","protected":false},"excerpt":{"rendered":"

Correlation in Excel is a statistical measure that gives an insight into how strongly two variables are related to one another. The result of calculating correlation is a number between 1 and -1. Numbers closer to either extreme indicate a stronger relationship, whereas a number closer to 0 indicates a weak or nonexistent relationship. A […]<\/p>\n","protected":false},"author":1,"featured_media":33248,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"cos_headline_score":0,"cos_seo_score":0,"cos_headline_text":"","cos_headline_has_been_analyzed":false,"cos_last_analyzed_headline":[],"om_disable_all_campaigns":false},"categories":[1475,3,18],"tags":[88,1479,1477,1476,1480,1478],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/33228"}],"collection":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/comments?post=33228"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/33228\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/33248"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=33228"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=33228"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=33228"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}