Each trial has two measurements. Keep them on the same row before sorting or removing incomplete records so the formula compares the intended pairs.

Start with four pairs whose result you can predict

Enter 1, 2, 3, 4 in B2:B5 and 2, 4, 6, 8 in C2:C5. Use =CORREL(B2:B5,C2:C5). Every Y is double its X, so the expected correlation is 1.

The X and Y values form four points on a line.

Illustrated correlation check. Keep each X next to its own Y.

Test a constant column in a copy

Set all four Y values to 4. The calculation is undefined because Y has no variation. If a real dataset errors, check both ranges for spread, length, and missing observations. Use a scatter plot to spot curves and unusual points, and include the number of pairs when reporting correlation.

A flat row of points at Y = 4 illustrates why the statistic is undefined.

Illustrated constant-column check. Both variables need spread.

Microsoft’s CORREL function reference