The first thing most people do when they need to measure relationship strength between sales and ad spend is type =CORREL(A2:A50,B2:B50) and paste the result into a report. That’s usually the wrong move — because they’ve ignored outliers, mismatched date ranges, and non-linear patterns that make CORREL return garbage.
Quick Answer
Use CORREL(array1, array2) only when both arrays are numeric, same-length, aligned by observation (e.g., row 2 = Week 1 for both), and free of extreme outliers. Never feed it text, blanks, or mismatched time periods — Excel won’t warn you, but the number will lie.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| Basic CORREL formula | Type =CORREL(A2:A21,B2:B21) in any blank cell |
Quick check on clean, aligned, linear data | Fails silently with text, blanks, or unequal lengths |
| CORREL + FILTER | Apply AutoFilter (Ctrl+Shift+L), filter out outliers manually, then run CORREL on visible cells only | Spot-checking correlation after removing obvious anomalies | SUBTOTAL doesn’t work inside CORREL — you must copy visible cells first |
| Array formula with IF | Enter =CORREL(IF(C2:C21>0,A2:A21),IF(C2:C21>0,B2:B21)) with Ctrl+Shift+Enter (Legacy) or Enter (365) |
Conditional correlation (e.g., only Q1 data or positive revenue months) | Returns #N/A if any condition yields zero matches |
| CORREL + Data Analysis ToolPak | Enable ToolPak → Data tab → Data Analysis → Correlation → select input range (B1:C21) | Comparing >2 variables at once (e.g., ad spend, email opens, conversions) | Outputs matrix, not single value; assumes all columns are same length and aligned |
Method 1 Deep Dive
Let’s say you’re analyzing monthly performance for Acme Corp’s regional offices. Column A holds Monthly Revenue (A2:A21), Column B holds Digital Ad Spend (B2:B21). You want to know if more spend drives more revenue.
Here’s what NOT to do: =CORREL(A2:A21,B2:B21) without checking first.
Do this instead:
- Select A1:B21 — including headers
- Press Alt+A+V to open Data Validation dialog
- Type
=ISNUMBER(A2)in Allow box → click OK. Repeat for B2. - Scan for #N/A or “N/A” in either column — delete those rows entirely (don’t just blank them)
- Now enter
=CORREL(A2:A21,B2:B21)in D1
You’ll get 0.67. But here’s the counterintuitive part: that looks strong — until you plot it. In E2:E21, enter =A2/B2 to calculate ROI per month. Sort by ROI descending. Notice March (A5/B5) shows $142k revenue on $8k spend — an outlier. Remove row 5. Recalculate: CORREL drops to 0.32. That’s the real story.
Sample data snippet (A1:C11):
| Revenue ($) | Ad Spend ($) | ROI |
|---|---|---|
| $89,400 | $4,200 | 21.3 |
| $112,600 | $5,800 | 19.4 |
| $142,200 | $8,000 | 17.8 |
| $65,100 | $3,900 | 16.7 |
| $78,300 | $4,600 | 17.0 |
| $92,700 | $5,100 | 18.2 |
| $54,900 | $3,200 | 17.2 |
| $101,500 | $5,400 | 18.8 |
| $87,600 | $4,900 | 17.9 |
| $61,200 | $3,700 | 16.5 |
Method 2 Deep Dive
Say your dataset spans Jan–Dec 2024 but includes 3 months where ad spend was $0 (e.g., holidays). CORREL treats $0 as valid data — even though $0 spend tells you nothing about the relationship.
Do this:
- In F2, enter
=FILTER(A2:A21,(B2:B21>0)*(A2:A21>0)) - In G2, enter
=FILTER(B2:B21,(B2:B21>0)*(A2:A21>0)) - Now run
=CORREL(F2#,G2#)— the#means spilled range
This excludes zero-spend and zero-revenue months automatically. Result: 0.81 — much stronger, and honest.
If you’re on Excel 2019 or earlier (no FILTER), use this array formula instead in H1:=CORREL(IF(B2:B21>0,IF(A2:A21>0,A2:A21)),IF(B2:B21>0,IF(A2:A21>0,B2:B21)))
Then press Ctrl+Shift+Enter. You’ll see curly braces { } appear — that’s confirmation.
Pro tip: CORREL ignores text and logicals — but it does NOT ignore zeros. So =CORREL({1,2,3},{0,0,0}) returns 0, not #N/A. That’s why filtering >0 is safer than just deleting blanks.
Cheat Sheet
| Task | Formula / Shortcut | Notes |
|---|---|---|
| Basic correlation | =CORREL(A2:A21,B2:B21) |
Both ranges must be same length, numeric, no blanks |
| Check for text/blanks | Select range → Alt+H+F+D → ‘Go To Special’ → ‘Blanks’ or ‘Constants’ | Fast way to spot non-numeric entries |
| Correlate filtered data | =CORREL(SUBTOTAL(109,A2:A21),SUBTOTAL(109,B2:B21)) ❌✅ Copy visible cells to new range first, then apply CORREL |
SUBTOTAL doesn’t work inside CORREL — common trap |
| Conditional correlation (2019+) | =CORREL(FILTER(A2:A21,B2:B21>5000),FILTER(B2:B21,B2:B21>5000)) |
Only includes months where spend > $5,000 |
| Multi-variable matrix | Data tab → Alt+A+Y+C → Correlation → Input B1:D21 | Requires headers; outputs full correlation matrix |
