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:
- 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).
- 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)
- 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!
- 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.
- 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.
- 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.
Last modified: Wed Jan 18 17:53:41 Central Standard Time 2006