# What are some ways to graphically display non-normal distributions in Excel?

The ultimate goal is to show users, at a glance, if their data is normally distributed.

The first attempt is a kludge that plots the data in a frequency graph. Then, the observed mean and standard deviation are used to build a "normal curve" graph. The frequency chart is laid over the the normal-curve chart and put next to some key statistics. The frequency chart also colors positive bins green and negative bins red.

It looks like this: I understand the fallacy of this approach, but for now it's practical. What is a better way to approach this issue?

• There's something wrong here: the normal curve overlaid on the bar chart does not agree with the bars.
– whuber
Apr 30 '11 at 1:23
• Correct, the curve is built using only the mean and std dev of the observed data and is meant to show what the data would look like if it were normal. The frequency bars are the actual observations. Given the statistics shown to the left of the chart indicating positive skewness and fat tails the point is made fairly well. There is more green than red and there are frequency bins above the normal curve tails. May 2 '11 at 0:11
• the problem goes beyond that: the curve is uniformly too high. You need to rescale it so its total area equals the total area of the bars.
– whuber
May 2 '11 at 14:14
• Gotcha. Yet another reason this isn't an ideal solution. May 2 '11 at 17:45

A normal probability plot is an excellent way to compare an empirical distribution to a normal distribution. Its merits are that it clearly displays the nature of any deviations from normality: ideally, the points lie along the diagonal; vertical deviations from the diagonal depict deviations from normality. Its disadvantages are that many people do not know how to read it, so beware!

To create a normal probability plot in Excel, rank the data (with the RANK function) and convert them to a normal score via

NORMSINV((rank-1/2)/count)


where 'count' is the amount of data and 'rank' references a cell with the rank, as shown in the illustrations. The formulas in this spreadsheet are 