How to Calculate a Percentile Using Excel

1. Enter your dataset in a continuous column in Excel. For this example, assume that the data is entered in cells A1 to A100.
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).
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.