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 1
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 2
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 3
Type "MIN([array start]:[array end])" without quotes next to the row labeled "0-%".
Step 4
Type "MAX([array start]:[array end])" without quotes next to the row labeled "100-%".
Step 5
Select the "0-%" through "100-%" columns as well as the row beneath them.
Step 6
Click the arrow under "Line" within the Insert Menu. In the pull-down menu that opens, select the first item, "Line chart."
Tip
Because your percentile rows may move over time, it’s a good idea to place a “$” before the column letter and row number in each formula. When moved, these formulas automatically update, and continue identifying the same set of data. Your formula for the “25-%” row might then look like “PERCENTILE.EXC($C$2:$C$26, .25)”.
You can define your data with a name through the Define->Name option under the Insert menu. If you define the name “MyData,” then the formula would read “PERCENTILE.EXC(MyData, .25)”
You can format the names of your percentile columns in any way you choose. The column “25-%”, for example, could be written as “25th Percentile.” Ultimately, it is the formulas below these headers that is most important.
Warning
The process describe assumes Microsoft Excel online or 2010 versions. Steps may vary with different versions.