Skip to main content
v2026.11,986 entries · CC-BY 4.0

How to Find Covariance in Excel: COVARIANCE.S and COVARIANCE.P

Find covariance in Excel with COVARIANCE.S or COVARIANCE.P: when to use each, worked examples you can check, and what #N/A and #DIV/0! mean.

Written and maintained by CASRAI Editorial Board

Published · Last updated

On this page

To find covariance in Excel, use =COVARIANCE.S(array1, array2) for a sample or =COVARIANCE.P(array1, array2) for a whole population. Both take two equal-length ranges of numbers and return one value: the average of the products of each pair’s deviations from its own mean. Pick the function that matches your data, because the two differ by the divisor (n−1 versus n). This page is for researchers and analysts who have two columns of paired measurements and need a covariance they can check by hand.

Ask CASRAI · free to tryQuestion about this guide? Ask CASRAI answers with linked sources. 2 free questions a day, no account.

Ask your first question freeSubscribe

What covariance is used for in research

Covariance measures whether two variables tend to move together. A positive value means that when one variable is above its mean the other tends to be above its mean too; a negative value means they tend to move in opposite directions; a value near zero means no consistent linear co-movement. Covariance carries the units of both variables multiplied together, so its size is hard to compare across studies. When you need a unit-free measure, scale it into a correlation: see correlation coefficient in SPSS and correlation coefficient vs coefficient of determination.

Which function to use: COVARIANCE.S or COVARIANCE.P

Function Use when Microsoft’s description Worked example from Microsoft
COVARIANCE.S Your data are a sample drawn from a larger population (most research data) Returns the sample covariance, the average of the products of deviations for each data point pair in two data sets =COVARIANCE.S({2,4,8},{5,11,12}) returns 9.666666667
COVARIANCE.P Your data are the entire population of interest Returns population covariance, the average of the products of deviations for each data point pair in two data sets For data1 = 3, 2, 4, 5, 6 and data2 = 9, 7, 12, 15, 17, =COVARIANCE.P(A2:A6,B2:B6) returns 5.2

The choice of “sample” or “population” follows from your design, not from Excel. Checking the arithmetic for the first example: the deviations of x from its mean are about −2.667, −0.667 and 3.333, those of y are about −4.333, 1.667 and 2.667, and the products sum to about 19.333. Dividing by n−1 = 2 gives 9.667 (the sample value); dividing by n = 3 would give about 6.444 (the population value).

Access: which Excel versions have the functions

According to Microsoft’s documentation (read 2026-11-12), COVARIANCE.S is available in Microsoft 365 for Windows and Mac, Excel 2024, 2021, 2019 and 2016, and Excel for the web. COVARIANCE.P is documented for Microsoft 365, Excel 2024, 2021, 2019 and 2016. Neither needs an add-in. The Analysis ToolPak, which has its own Covariance tool for producing a whole matrix, is a separate add-in; enabling it is covered in our Excel Analysis ToolPak guide.

Step by step: how to calculate covariance in Excel

How to compute covariance in Excel from two columns

  1. Enter the two variables in adjacent columns with one observation per row, for example X in A2:A6 and Y in B2:B6. Each row must be a matched pair.
  2. Click an empty cell for the result.
  3. Type =COVARIANCE.S(A2:A6,B2:B6) for a sample, or =COVARIANCE.P(A2:A6,B2:B6) for a population, and press Enter.
  4. Compare the result with your expectation of sign and order of magnitude (see the next section).

Excel covariance formula with typed-in values

The arguments can be ranges, named ranges, references or array constants, so =COVARIANCE.S({2,4,8},{5,11,12}) works without any cells. Microsoft’s example returns 9.666666667, and the same data entered in A3:A5 and B3:B5 with =COVARIANCE.S(A3:A5,B3:B5) gives the identical value.

Output: how to read the result

  • Sign: positive means the variables tend to rise together; negative means one tends to rise as the other falls.
  • Magnitude: depends on the units and spread of both variables, so 5.2 is not “strong” or “weak” on its own. Convert to a correlation to compare across datasets.
  • Self-check: reproduce Microsoft’s published examples (9.666666667 for the sample case, 5.2 for the population case) in a blank sheet before applying the function to your own data.

Troubleshooting

What you see Cause (per Microsoft’s documentation)
#N/A The two arrays contain different numbers of data points.
#DIV/0! Either array is empty, or (for COVARIANCE.S) there is only one data point.
Result looks wrong with text or blanks in the range Text, logical values and empty cells in a reference are ignored, but cells containing the value 0 are counted. Zeros that actually mean “missing” will distort the result.

Free alternatives

If you do not have Excel, the same quantity can be computed in free tools. Check each tool’s own documentation for whether it divides by n or n−1 before comparing numbers with Excel’s COVARIANCE.S or COVARIANCE.P.

Tool Function What the documentation says Source (read 2026-11-12)
Google Sheets COVAR(data_y, data_x) Calculates the covariance of a dataset. Text in the value arguments is ignored. Google notes that covariance magnitude is hard to interpret and points to CORREL or PEARSON as normalized versions. Google Docs Editors Help, COVAR
R cov(x, y) Returns the covariance of two vectors, or a covariance matrix for matrices and data frames. The denominator is n−1, so it matches the sample covariance (COVARIANCE.S), not the population version. Missing values are controlled by the use argument. See also our guide to cor() in R. R documentation, cor/cov

For other spreadsheet statistics, see how to find a slope in Excel.

FAQ

What is the covariance formula in Excel?

There is no operator, only two functions: =COVARIANCE.S(array1,array2) for samples and =COVARIANCE.P(array1,array2) for populations.

How do I find covariance on Excel for more than two variables?

The two functions compare one pair at a time. Use one formula per pair, or the Covariance tool in the Analysis ToolPak (set up as described in our ToolPak guide).

Why does Excel return #N/A?

The two ranges have different lengths. Make both cover the same number of rows.

Is Excel covariance the same as correlation?

No. Covariance keeps the original units; correlation rescales it to a unit-free value between −1 and 1.

Sources

Follow CASRAI

Research-administration guidance, standards updates and independent tool reviews.

Ask CASRAI · free to try

Ask about How to Find Covariance in Excel: COVARIANCE.S and COVARIANCE.P

2 free questions a day, no account.

An AI assistant for research: funder rules, compliance, methods and publishing. It links the sources behind its answers and labels answers that come from a web search.

Answers draw on CASRAI's guides and dictionary plus the federal and funder documents we index: Federal Register, Grants.gov, Regulations.gov and UKRI.

Works on this site and inside Claude, Cursor and the AI tools you already use.

Everything CASRAI publishes — this page, the dictionary, the guides and the news — stays open to read, with no account and no card.

Referenced across the research world

University of Cambridge logoColumbia University logoCrossref logoUniversity of Edinburgh logoHarvard University logoUniversity of Oxford logoPrinceton University logoStanford School of Medicine logoUniversity College London logoORCID logoUniversity of Cambridge logoColumbia University logoCrossref logoUniversity of Edinburgh logoHarvard University logoUniversity of Oxford logoPrinceton University logoStanford School of Medicine logoUniversity College London logoORCID logo
  • University of Cambridge logo
  • Columbia University logo
  • Crossref logo
  • University of Edinburgh logo
  • Harvard University logo
  • University of Oxford logo
  • Princeton University logo
  • Stanford School of Medicine logo
  • University College London logo
  • ORCID logo

View CASRAI adoption →