Home / Statistical Tools / Analysis Tools / Correlation and Covariance / Preparing Your Data

Preparing Your Data

Correlation and Covariance analyzes the relationships between two or more numeric columns. This page explains the data requirements and helps you choose the right analysis type.

When to Use Each Type

Three analysis types are available from the Excel ribbon dropdown. Choose the one that best fits your data:

Type	When to Use
Pearson Correlation	Linear relationships between numeric variables. Assumes data is approximately normally distributed. Best for measuring how strongly two variables move together in a straight-line pattern.
Spearman Correlation	Non-linear but monotonic relationships, ordinal data, or non-normal data. Uses ranks instead of raw values, making it robust to outliers.
Covariance	Unstandardized measure of joint variation. Useful when you need magnitudes in original units (e.g., portfolio analysis). Includes variance on the diagonal.

How Data Types Work

The table below shows what happens when you assign each column type in the Correlation dialog. Click a numbered badge to see an example using that configuration.

		Data Column
		Nominal	Continuous	Integer	Count	DateTime
Frequency Column	None	Not Allowed	Column header is name of row/column in the matrix. Coefficients calculated between each pair of selected columns. 123	Column header is name of row/column in the matrix. Coefficients calculated between each pair of selected columns.	Column header is name of row/column in the matrix. Coefficients calculated between each pair of selected columns.	Not Allowed
	Nominal	—
	Continuous	—
	Integer	—
	Count	—
	DateTime	—

— indicates this frequency type is not available for selection. Correlation and Covariance does not support frequency columns.

Examples

1 Quick Start — Pearson — Two-column linear correlation

2 Spearman Correlation — Rank-based correlation across multiple columns

3 Covariance — Unstandardized joint variation with variance on the diagonal

Data Layout Options

Raw Numeric Data

Each column contains numeric values. The first row is the column header, and subsequent rows are data points.

Height	Weight	Age
62	115	28
64	125	32
66	140	35

Each selected column becomes one row and one column in the resulting matrix. Quantum XL calculates coefficients between each pair.

Common Mistakes

Avoid These Issues

• Selecting fewer than 2 columns — Correlation and Covariance requires at least 2 columns. Select more columns to build a larger matrix.
• Selecting Nominal or DateTime columns — Only numeric columns (Continuous, Integer, Count) are allowed. Nominal and DateTime columns are filtered out of the selection list.
• Fewer than 2 data points — Each column needs at least 2 values to compute coefficients.
• Missing data — Rows with empty cells in any selected column are excluded from the calculation.