How to Calculate Deciles in Excel (With Examples)


In statistics, deciles are numbers that split a dataset into ten groups of equal frequency.

The first decile is the point where 10% of all data values lie below it. The second decile is the point where 20% of all data values lie below it, and so forth.

We can use the following function to calculate the deciles for a dataset in Excel:

=PERCENTILE(CELL RANGE, PERCENTILE)

The following example shows how to use this function in practice.

Example: Calculate Deciles in Excel

Suppose we have the following dataset with 20 values:

The following image shows how to calculate the deciles for the dataset:

Deciles in Excel

The way to interpret the deciles is as follows:

  • 20% of all data values lie below 67.8.
  • 30% of all data values lie below 76.5.
  • 40% of all data values lie below 83.6.

And so on.

To place each data value into a decile, we can use the PERCENTRANK.EXC() function, which uses the following syntax:

=PERCENTRANK.EXC(CELL RANGE, DATA VALUE, SIGNIFICANCE)

The following image shows how to use this function for our dataset:

Example of calculating deciles in Excel

Note that this function finds the relative rank of a value in a dataset as a percentage and rounds to one digit, which is equivalent to finding the decile that the value falls in.

The way to interpret the output is as follows:

  • The data value 58 falls between the percentile 0 and 0.1, thus it falls in the first decile.
  • The data value 64 falls between the percentile 0.1 and 0.2, thus it falls in the second decile.
  • The data value 67 falls between the percentile 0.1 and 0.2, thus it falls in the second decile.
  • The data value 68 falls between the percentile 0.2 and 0.3, thus it falls in the third decile.

And so on.

Additional Resources

How to Calculate a Five Number Summary in Excel
How to Normalize Data in Excel
How to Easily Find Outliers in Excel

One Reply to “How to Calculate Deciles in Excel (With Examples)”

  1. Hey,

    I’ve followed this formula to calculate the deciles of a dataset, but some zero’s have been returned. Can you have a 0th percentile?

    Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *