Normality Test Excel

In the previous article, we discussed the concept of normal distributed data and how to check normality test using Excel through the Chi-Sq Goodness of Fit method.

Normal distribution, also called Gaussian distribution, is probably the most important distribution related to continuous data from a statistical analysis standpoint. It is sometimes called the “bell curve,” although the tonal qualities of such a bell would be less than pleasing. A normal, or Gaussian, distribution is depicted below.

Normal data is shaped symmetrically surrounding the mean, represented above by the x-bar line. A normal curve is beneficial for determining the probability that a given data point in a population will fall inside a certain range within the distribution.

Since the normality test will examine the probability of data falling inside the normal distribution range, we can use the second method to check whether the data falls under normal distribution or not by using the normal probability plot as the visual inference of normality test in Excel.

This method is more straightforward than the Chi-Sq Goodness of Fit test. It is suitable for beginner analysts to understand and examine the data and whether it falls under the normal distribution.

A normal probability plot can determine if small data sets come from a normal distribution. This involves using the probability properties of the normal distribution. We will eventually make a plot that we hope is linear. We will demonstrate the procedure using the data below.

This tutorial will show how to create the normal probability plot step by step.

Step 1: Let’s create a dataset

We will use a fake data set and sort the data in ascending order.


Step 2: Calculate the z-values

Next, we’ll use the following formula to calculate the z-value that corresponds to the first data value

=NORM.S.INV((RANK(A2, $A$2:$A$16, 1)-0.5)/COUNT(A:A))

We’ll copy this formula down to each cell in column B:


Step 3: Create the Normal Probability Plot

Next, we’ll create the normal probability plot.

First, highlight the cell range A2:B16 as follows:


Along with the top ribbon, click the Insert tab. Under the Charts section, click the first option under Scatter.


This automatically produces the following chart, and feel free to change the title, axis, or other cosmetic features to make it more aesthetic.

Normal Probability Plot in Excel

How to Interpret a Normality Test

An informal approximation of a normality test, called “the fat pencil test”, is often applied to a probability plot. Imagine a “fat pencil” lying on top of the fitted line:

  • If it covers all the data points on the plot, the data are probably normal.
  • If points are far enough from the fitted line that they are visible beyond the edges of the fat pencil, the data are probably nonnormal.

Since the “fat pencil” in the probability chart below can cover all of the points on the plot. Hence, we can conclude that our data set falls under the normal distribution.


This informal approach is not a substitute for the statistical inference of the normality test itself, but it is helpful as a quick visual assessment.

Fat Pencil is mentioned by Dr Douglas Montgomery in his book “Design and Analysis of Experiments.”

Learn more about analysis toolset in our signature Six Sigma Council’s accredited  courses.