How to Perform a Jarque-Bera Test for Normality in Excel

Understanding the Significance of the Jarque-Bera Test

In the realm of statistics, determining whether a dataset follows a specific distribution is a fundamental requirement for many analytical procedures. The Jarque-Bera test is a specialized goodness-of-fit test designed specifically to ascertain if sample data possesses the skewness and kurtosis matching a normal distribution. This test is particularly valuable because many parametric statistical methods, such as t-tests and linear regression, rely on the assumption that the underlying data is normally distributed. If this assumption is violated, the results of those analyses may be biased or misleading, making the Jarque-Bera test a critical diagnostic tool for researchers and analysts across various disciplines.

The mathematical foundation of the Jarque-Bera test rests on the comparison of the third and fourth standardized moments of the sample data against those of a theoretical normal distribution. In a perfectly normal distribution, the skewness is zero, indicating perfect symmetry, and the kurtosis is three (or excess kurtosis is zero), indicating a specific “peakedness” or tail weight. The Jarque-Bera statistic aggregates the deviations of the sample’s skewness and kurtosis from these ideal values into a single numerical value. A test statistic that is significantly higher than zero provides evidence that the data deviates from normality, whereas a value close to zero suggests that the data is likely normally distributed.

Utilizing Microsoft Excel to perform this test offers a practical and accessible way to handle large datasets without requiring complex specialized software. While Excel does not feature a single “Jarque-Bera” button in its standard Data Analysis Toolpak, the test can be easily constructed using standard functions. This flexibility allows analysts to build transparent models where every step of the calculation—from the initial data input to the final p-value derivation—is clearly visible and verifiable. By following a structured approach in Excel, one can ensure that the statistical assumptions of their model are sound before proceeding with more advanced inferential techniques.

Theoretical Foundations: Skewness and Kurtosis

To fully appreciate how the Jarque-Bera test functions, one must understand the two components it measures: skewness and kurtosis. Skewness refers to the measure of the asymmetry of the probability distribution of a real-valued random variable about its mean. In a dataset with positive skew, the tail on the right side is longer or fatter than the left side, often pulling the mean to the right of the median. Conversely, negative skew indicates a longer left tail. Because the Jarque-Bera formula squares the skewness value, any deviation from zero—whether positive or negative—increases the overall test statistic, signifying a departure from the symmetry expected in a Gaussian distribution.

Kurtosis, on the other hand, describes the “tailedness” of the distribution. It provides insight into whether the data is heavy-tailed or light-tailed relative to a normal distribution. High kurtosis indicates a distribution with fat tails and a sharp peak, suggesting that the data may contain frequent outliers. Low kurtosis indicates a distribution with thin tails and a flatter peak. In the context of the Jarque-Bera test, we specifically look at “excess kurtosis,” which is the sample kurtosis minus three. The formula effectively tests if the combination of skewness and excess kurtosis is statistically different from zero, which is the hallmark of normality.

The relationship between these moments is captured in the test statistic formula, denoted as JB. The formula is expressed as: JB = (n/6) * (S^2 + (C^2/4)), where n represents the sample size, S represents the sample skewness, and C represents the excess kurtosis. It is important to note that as the sample size increases, the test becomes more sensitive to even minor deviations from normality. This characteristic makes it a powerful tool for large datasets common in econometrics and quantitative finance, where ensuring the validity of the normal distribution assumption is paramount for risk management and forecasting.

Step 1: Data Preparation and Organization

The first practical step in performing a Jarque-Bera test in Excel involves the meticulous organization of your raw data. For the test to function correctly, your sample data should be arranged in a single, contiguous column. This structure allows Excel’s built-in statistical functions to easily reference the entire range. Before beginning calculations, it is advisable to inspect the data for any obvious errors, such as non-numeric characters or empty cells, which could disrupt the accuracy of the aggregate functions. Clean data is the prerequisite for any reliable statistical inference, and taking the time to format your spreadsheet correctly will prevent errors in subsequent steps.

Once your data is placed in a column (for example, column A), it is helpful to label your columns and create a dedicated area for your summary statistics. This “calculation block” will house the intermediate values required for the JB formula, including the count of observations, the skewness, and the kurtosis. By separating the raw data from the analysis, you maintain a clean worksheet environment that is easier to audit and present to others. Labeling each cell—such as “n,” “Skewness,” “Kurtosis,” and “JB Statistic”—ensures that the logic of your spreadsheet remains intuitive and professional.

Referencing the visual guide below, you can see how the initial dataset is typically entered into Excel. Notice how the values are listed vertically, which is the standard format for univariate analysis. This layout facilitates the use of the SKEW function and KURT function in the next phase of the process.

Step 2: Calculating Descriptive Statistics

With the data properly organized, the next phase is to calculate the three essential components of the Jarque-Bera formula: the number of observations (n), the sample skewness (S), and the sample kurtosis (C). Excel provides straightforward formulas for each of these. To find the sample size, you can use the =COUNT(range) function, which returns the total number of numeric values in your dataset. This value is critical because the JB statistic is directly proportional to the number of observations, reflecting the increased statistical power that comes with larger samples.

To determine the skewness, use the =SKEW(range) function. This function calculates the unbiased estimate of the skewness of a distribution based on a sample. Similarly, to find the kurtosis, use the =KURT(range) function. It is important to note that Excel’s KURT function automatically calculates the excess kurtosis (kurtosis relative to the normal distribution), which is exactly what is needed for the Jarque-Bera formula. In other words, if the data were perfectly normal, the KURT function would return a value close to zero rather than three. This simplification by Excel makes the subsequent JB calculation more direct.

In your spreadsheet, you should arrange these calculations clearly. For instance, if your data resides in cells A2 through A21, your formulas would be =COUNT(A2:A21), =SKEW(A2:A21), and =KURT(A2:A21). By placing these results in a structured table, you prepare the groundwork for the final JB statistic calculation. This modular approach not only reduces the risk of manual calculation errors but also allows the spreadsheet to dynamically update if the underlying data in column A is modified or expanded.

Step 3: Computing the Jarque-Bera Test Statistic

Now that you have the sample size (n), skewness (S), and excess kurtosis (C), you can compute the Jarque-Bera test statistic. The formula, as previously discussed, is (n/6) * (S^2 + (C^2/4)). In Excel syntax, this would look something like =(Cell_n/6) * (Cell_S^2 + (Cell_C^2/4)). This calculation combines the squared skewness and the squared quarter-kurtosis, weighting the entire sum by the sample size divided by six. The result is a single scalar value that summarizes how much the sample distribution deviates from the Gaussian ideal.

The JB statistic is always a non-negative number. If the dataset follows a normal distribution, the skewness and excess kurtosis will both be near zero, resulting in a JB statistic that is also very small. However, if the data is highly skewed or has heavy tails (large kurtosis), the JB statistic will grow significantly. Because the components are squared, the direction of the skewness (left or right) does not matter; only the magnitude of the departure from zero is relevant. This makes the Jarque-Bera test an omnibus test for normality, as it checks for multiple types of deviations simultaneously.

It is worth noting that the Jarque-Bera test is an asymptotic test. This means that its reliability and the validity of its sampling distribution improve as the sample size increases. For very small samples, the test might not be as robust as other normality tests, such as the Shapiro-Wilk test. However, for the large datasets often encountered in financial analysis or economic modeling, the JB test is highly regarded for its balance of simplicity and statistical power.

Step 4: Deriving the P-Value Using Chi-Squared Distribution

The final numerical step in the process is to calculate the p-value associated with your JB statistic. Under the null hypothesis that the data is normally distributed, the Jarque-Bera statistic follows a chi-squared distribution with two degrees of freedom. The two degrees of freedom correspond to the two parameters being estimated: skewness and kurtosis. To find the p-value in Excel, you use the CHISQ.DIST.RT function, which returns the right-tailed probability of the chi-squared distribution.

The syntax for this function is =CHISQ.DIST.RT(x, deg_freedom), where x is your calculated JB statistic and deg_freedom is 2. The resulting p-value tells you the probability of obtaining a test statistic at least as extreme as the one calculated, assuming that the null hypothesis is true. A high p-value (typically greater than 0.05) suggests that the observed deviations in skewness and kurtosis are likely due to random sampling error, and thus you “fail to reject” the null hypothesis of normality.

In the example provided in the images, the calculated p-value is 0.5921. Because this value is much higher than the standard significance level (alpha) of 0.05, we conclude that there is insufficient evidence to suggest the data is not normally distributed. This allows the researcher to proceed with parametric tests that require normality. If the p-value were below 0.05, the researcher would have to consider non-parametric alternatives or data transformations to address the lack of normality.

Interpreting Your Results and Statistical Significance

Interpretation is the most crucial phase of the statistical hypothesis testing process. When you perform a Jarque-Bera test, you are essentially testing the null hypothesis (H0): “The data follows a normal distribution.” The alternative hypothesis (H1) is: “The data does not follow a normal distribution.” The p-value acts as the bridge between your data and these hypotheses. It is vital to remember that failing to reject the null hypothesis does not “prove” the data is normal; it simply means there is not enough evidence to claim otherwise.

The choice of a significance level, usually denoted as alpha (α), is a decision the analyst must make before conducting the test. While 0.05 is the most common threshold, a more stringent level like 0.01 might be used in fields where the cost of a Type I error (falsely rejecting the null hypothesis) is high. Conversely, in exploratory research, a level of 0.10 might be acceptable. Your interpretation must always be framed within the context of this alpha level. If p < α, you reject the null hypothesis and conclude the distribution is non-normal.

Furthermore, one should always complement the Jarque-Bera test with visual inspections of the data. Tools such as histograms or Q-Q plots (quantile-quantile plots) provide a qualitative view of the distribution that numerical tests might miss. For example, a Jarque-Bera test might reject normality due to a single extreme outlier, even if the rest of the data is perfectly Gaussian. By combining the formal JB test statistic with visual exploratory data analysis, you gain a more comprehensive understanding of your dataset’s characteristics and the validity of your statistical assumptions.

Practical Applications in Finance and Economics

The Jarque-Bera test is widely utilized in finance and economics, particularly in the analysis of asset returns. Many financial models, such as the Black-Scholes model for option pricing or the Modern Portfolio Theory, assume that log-returns of assets are normally distributed. However, empirical evidence often shows that financial returns exhibit “fat tails” (high kurtosis) and skewness. The Jarque-Bera test allows analysts to formally test these assumptions, helping them decide whether to use standard models or more complex ones that account for non-normality.

In the field of econometrics, the test is frequently applied to the residuals of regression models. One of the Gauss-Markov assumptions for Ordinary Least Squares (OLS) regression is that the error terms are normally distributed. If the residuals fail the Jarque-Bera test, it indicates that the confidence intervals and hypothesis tests (like the t-test and F-test) for the regression coefficients may not be reliable, especially in small samples. This makes the JB test an essential step in the validation of econometric models.

Beyond finance, the test is applicable in the social sciences and industrial quality control. Wherever a process is assumed to follow a normal distribution—such as the height of a population or the dimensions of a manufactured part—the Jarque-Bera test provides a rigorous way to check that assumption. By mastering this test in Excel, professionals in these fields can ensure their data-driven decisions are based on a sound understanding of the underlying data distribution, leading to more accurate predictions and more effective strategies.