Correlation in Excel: calculate and interpret it

For correlation Excel provides CORREL, which measures the linear association between two numeric variables observed on the same rows. Match the records, calculate the coefficient, and inspect the paired observations before using the result in a business decision.

Prepare a table of matched observations

Choose the unit first: a branch in a month, a customer in a quarter, or a product in a week. Every row must represent the same unit for both measures. Sorting one column independently destroys that match even when both columns still contain the right totals.

A US field-service company wants to know whether branches completing more service jobs also use more overtime. Finance exports completed jobs and paid overtime hours for the same September period and matches the branch IDs. These are the complete pairs in A1:C7:

The totals are controls, outside the formula ranges. The means are 210 / 6 = 35 jobs and 300 / 6 = 50 overtime hours. Keep the source period and branch definitions beside the table. Resolve duplicate IDs and unexplained missing values using the spreadsheet cleaning checklist.

Calculate Pearson correlation with CORREL

Enter the formula in an empty cell:

=CORREL(B2:B7,C2:C7)

The result is 0.8857142857, or 0.886 rounded to three decimals. Microsoft's CORREL documentation specifies the two input ranges and explains that zero values are included, while text, logical values and empty cells are ignored.

Pearson r = Sum of [(x - mean x) × (y - mean y)] / Square root of {Sum of [(x - mean x)²] × Sum of [(y - mean y)²]}.

The coefficient runs from -1 to +1. Positive values indicate a tendency for higher observations of one measure to accompany higher observations of the other; negative values indicate the opposite direction. Zero describes the absence of linear association, which still leaves room for a curved relationship.

Here, the positive coefficient summarizes a tendency for branches with more completed jobs to have more overtime. It does not measure overtime hours per additional job, or the savings from changing staffing. Those are different questions; the existing regression analysis in Excel guide explains fitted slopes and their checks.

Prove the coefficient from the rows

Subtract each mean, multiply the deviations, and square each deviation. The full calculation is:

With deviations in D and E, their product in F, and their squares in G and H, enter these in row 2 and fill through row 7:

D2: =B2-AVERAGE($B$2:$B$7)
E2: =C2-AVERAGE($C$2:$C$7)
F2: =D2*E2
G2: =D2^2
H2: =E2^2

Then reconstruct the coefficient:

=SUM(F2:F7)/SQRT(SUM(G2:G7)*SUM(H2:H7))

The denominator is the square root of 1,750 × 1,750, or 1,750. Dividing 1,550 by 1,750 gives 0.8857142857, matching CORREL. Both deviation columns also sum to zero, which checks the means and copied formulas. Keep full precision until presentation.

Inspect a scatter chart before interpreting it

Select the paired numbers in B1:C7 and insert an XY scatter chart with markers. Put completed jobs on the horizontal axis and overtime hours on the vertical axis. Microsoft's scatter chart guidance explains that both axes use numerical values, so each point represents an actual pair.

Look for a broad linear pattern, isolated points, curves and distinct branch groups. The NIST scatter plot guide identifies those diagnostic questions and states that association does not prove causation. A coefficient alone hides the shape you need to inspect.

Finance can report: "Across six branches in September, completed jobs and overtime hours have a positive Pearson correlation of 0.886." Then ask operations to examine branch size, staffing and job complexity. Larger branches may simply have more of both measures. The association and cause guide explains how to keep the claim within the evidence.

Where it goes wrong

• Missing observations become zeros. A missing overtime entry is unknown; a recorded zero means no overtime. Keep only complete numeric pairs for this calculation and report exclusions. Check pair counts, rather than relying on automatic omission.
• One influential branch drives the result. Investigate an unusual point against its source. Do not remove a valid observation simply because the coefficient changes.
• A shared trend looks like a driver. When comparing periods, growth or seasonality can move both variables. Review comparable periods and business context before interpreting the association.
• Ordinal ratings are treated as measurements. Satisfaction categories have order without necessarily equal spacing. NIST's rank correlation reference describes correlation of ranked variables and average ranks for ties. Choose the method deliberately rather than assuming numeric category codes make Pearson appropriate.

For an inventory decision about fast, slow and nonmoving items, use FSN analysis in Excel to classify movement before asking whether other measures are associated with it.

Covirage is not affiliated with Microsoft.

Check your own matched observations in Covirage

Bring the branch, customer or period export with both measures and their matching identifiers. Covirage maps the columns; its tools compute and check the figures, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.

See Covirage for finance teams for the business context.