How to Calculate a Percentile Using Excel

By Pete Collins

Percentiles are a useful way of measuring or manipulating the spread of a dataset. The percentile is the value below which that percentage of the dataset is found. For example, the 20th percentile in a dataset is the value below which 20% of the dataset resides. Thus, the 50th percentile is the value "in the middle," also known as the median. Excel allows you to calculate percentiles using the "Percentile" function.

Step 1

Enter your dataset in a continuous column in Excel. For this example, assume that the data is entered in cells A1 to A100.

Step 2

Determine what percentile you want to calculate, and convert it to a number between zero and one. For example, the 20th percentile is 0.2, the 55th percentile is 0.55, the 100th percentile is 1, and so on. This example will use the 40th percentile (0.4).

Step 3

Enter the following formula in the cell where you want the percentile result to show:=PERCENTILE(A1:A100,0.4).This tells Excel to calculate the 40th percentile of the data in cells A1 to A100.