Normal Quantile Plots in Excel

Normal quantile plots show how well a set of values fit a normal distribution. Here are steps for creating a normal quantile plot in Excel:

1. Place or load your data values into the first column. Leave the first row blank for labeling the columns. Sort the data in ascending order (look under the Data menu).
2. Label the second column as Rank. Enter the ranks, starting with 1 in the row right below the label. Each following row will be one more than the last (note: you can use an expression, copy and then paste to save you time)
3. Label the third column as Rank Proportion. This column shows the rank proportion of each value. Use this expression for the first data value =(b2 - 0.5) / count(b$2:b$N) where N should have the row number of the last cell. Finish the column by copying the first data expression to the remaining rows. Check to make sure your percentiles look like they are correct!
4. Label the fourth column as Rank-based z-scores. Excel provides these values with the normsinv function. Use this function to create the values in the fourth column.
5. Copy the first column to the fifth column. The Excel chart wizard works better if the x-axis values are just to the left of the y-axis values.
6. Select the fourth and fifth column. Select the chart wizard and then the scatter plot. The default data values should be good, but you should provide good labels.

Here's a completed example in Excel.