How Do You Calculate Covariance in Excel?

To calculate covariance in Excel, use the covariance function. The syntax of the function is: COVARIANCE.P(array1,array2), where array1 and array2 are the two sets of data for which the covariance is being determined.

  1. Arrange the two sets of data in array format

    To arrange in array format means to arrange in either a column or row format.

  2. Enter the formula for variance in a separate cell

    To use formulas in Excel, precede the formula with the equal sign. Thus, to determine covariance, enter “=COVARIANCE.P(array1,array2).” Without the equal sign, Excel will not calculate covariance because the formula will be recognized only as text.

  3. Replace the variables array1 and array2 in the covariance formula with real data

    To enter the data for array1, highlight the first cell of the column. A small black square appears at the bottom right corner of the highlighted cell. Place the cursor on it. When the cursor turns into a small black cross, drag down to select the range down to the last cell. Do the same for array2. Press enter; the covariance figure appears in the cell in the place of the formula.