A percentile graph helps you see how a given value ranks against the overall population. It does this by looking at all the data available and generating percentiles based on that data and not theoretical values. One common use of percentile graphs involves grade point averages: a student with a grade point average of 3.5 would appear to be among the best, but in a school where most students earn 3.5 GPA or higher, then that student would fall into a lower percentile. Percentile graphs are also useful in graphing infant growth -- in spite of being a healthy weight, a newborn that consistently moves down in percentile will receive medical attention. Making a percentile graph involves Excel's built-in "PERCENTILE.EXC" function to draw a smooth graph of relative percentage rankings.
Establish Top, Bottom, 25th, Median, and 75th percentiles.
Step
Create five additional columns next to the data you wish to evaluate. Label these columns "0-%", "25-%", "Median", "75-%" and "100-%". If your data is all in the "B" column with the row beginning at two, then your new column labels would go in the C1, D1, E1, F1, G1.
Step
Type "PERCENTILE.EXC([array start]:[array end], .25)", without quotes, into the field beneath "25-%". Do the same with each other column you added, substituting ".5" and ".75" for the zero in the "Median" and "75-%" rows' formulas, respectively. If you're using 25 records in column C, for example, then your formula would look like "PERCENTILE.EXC(C2:C26, .5)" for the "Median."
Step
Type "MIN([array start]:[array end])" without quotes next to the row labeled "0-%".
Step
Type "MAX([array start]:[array end])" without quotes next to the row labeled "100-%".
Step
Select the "0-%" through "100-%" columns as well as the row beneath them.
Step
Click the arrow under "Line" within the Insert Menu. In the pull-down menu that opens, select the first item, "Line chart."
