30. Statistics for Analysts

30.4 CORREL and a Correlation Matrix: Which Numbers Move Together

By Ravindra Bagale · Last updated: 7 October 2026

Example first. Amir runs a Nashik dark store. For 20 days in September he has ad spend, discount, rain, orders and average delivery minutes. He asks: "Does ad spend go with more orders?" =CORREL(B2:B21,E2:E21) returns 0.891, a very strong positive link. Then he notices that delivery minutes and orders have a correlation of -0.60: do slow deliveries scare customers away? Not necessarily. Rain makes deliveries slow and keeps orders down. This lesson builds the full correlation matrix and teaches how to read it without fooling yourself.

First look at the data (before → after):

Expected result — Correlation with Orders

=CORREL(B2:B21,E2:E21)
=RSQ(E2:E21,B2:B21)
Data › Data Analysis › Correlation › Input Range B1:F21, Labels in first row

The data: 20 days at a Nashik dark store (fictional)

The correlation matrix (ToolPak output, rounded to 2 decimals)

The ToolPak fills only the lower triangle, because CORREL(A,B) equals CORREL(B,A), and the diagonal is always 1. Every cell equals a CORREL formula, for example Rain and Mins: =CORREL(D2:D21,F2:F21) = 0.928.

A matrix with formulas that update. The ToolPak table is static. For a live matrix, put the five names in H12:L12 and H13:H17 and type this in I13, then fill it across and down to M17:

=CORREL(INDEX($B$2:$F$21,0,ROWS($1:1)),INDEX($B$2:$F$21,0,COLUMNS($A:A)))

INDEX(range,0,n) returns the whole n-th column, and ROWS / COLUMNS count 1, 2, 3… as you fill. In Microsoft 365 you can also spill the whole matrix from one cell: =MAKEARRAY(5,5,LAMBDA(r,c,CORREL(CHOOSECOLS(B2:F21,r),CHOOSECOLS(B2:F21,c)))) (lesson 9.18 explains LAMBDA).

How to read r

These labels are a rule of thumb, not a law. With only 20 rows, an r of 0.3 can easily appear by chance; with 2,000 rows it is a real (but small) link.

Correlation is not causation: the rain trap. Mins and Orders have r = -0.60. It is tempting to write "slow delivery reduces orders". But Rain is strongly linked to both: rain slows riders (r = 0.93) and keeps orders down (r = -0.76). Rain is a confounder. Lesson 30.6 shows how a regression with Rain in it separates the effects. Also notice that AdSpend and Discount are themselves strongly linked (r = 0.81): on campaign days Amir raised both, so this data alone cannot fully separate their effects.

Three more checks before trusting r

1. Draw it. Select B1:B21 and E1:E21 (hold Ctrl), choose Insert › Insert Scatter (X, Y) or Bubble Chart › Scatter, then Chart Design › Add Chart Element › Trendline › Linear. In Format Trendline tick Display R-squared value on chart: it shows 0.7931, the same as RSQ.
2. r only measures straight lines. A U-shaped link (orders fall with very low and very high discounts) can have r near 0.
3. One outlier can create or hide a correlation. Recalculate r without the extreme row and see if it holds.

Every number on this page was computed with Excel's documented formulas and re-checked twice: in LibreOffice Calc 25.2 with the same formulas, and in Python with scipy and statsmodels.

Microsoft sources (checked 7 October 2026): Use the Analysis ToolPak to perform complex data analysis; CORREL function.

Practice task (try it before you open the answer):

1. What is the correlation between Discount and Orders, and how would you describe it?
2. Which pair in the matrix has the strongest negative correlation?
3. Write one honest sentence about Mins and Orders for a report.