How to Make a Percentile Graph in Excel

Techwalla may earn compensation through affiliate links in this story. Learn more about our affiliate and product review process here.
Percentile graphs show you where a piece of data sits relative to others.
Image Credit: Thinkstock/Comstock/Getty Images

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.

Advertisement

Establish Top, Bottom, 25th, Median, and 75th percentiles.

Video of the Day

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.

Advertisement

Video of the Day

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."

Advertisement

Step 3

Type "MIN([array start]:[array end])" without quotes next to the row labeled "0-%".

Advertisement

Step 4

Type "MAX([array start]:[array end])" without quotes next to the row labeled "100-%".

Advertisement

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."

Advertisement

Advertisement

references