How to Quintile in Excel: Free Quintile Calculator Included

Photo of author
Written By Chris

Just a simple "data guy". Changing the world one formula at a time.

When working with data in Excel, you may want to categorize your dataset into equal parts for better analysis and interpretation. One such method is by using quintiles, which divide the data into five equal groups, each representing 20% of the dataset. Understanding how to calculate quintiles in Excel allows you to gain insights and make data-driven decisions.

Calculating quintiles in Excel is straightforward and can be achieved using the built-in PERCENTILE function. This function takes two arguments: the range of cells containing your data and the desired quintile in decimal format (e.g., 0.2 represents the first quintile). 

With this knowledge, you’ll be able to quickly generate quintiles and better comprehend the distribution of your data.

By following these guidelines I’m about to show you, you’ll become proficient in calculating quintiles within Excel, enhancing your data analysis and decision-making capabilities. 

And for those of you on the go, I’ve got a handy quintile calculator at the end that gives you all the power of Excel without having to open your spreadsheet.

What is the Purpose of Quintiles?

How to quintile in excel image 1

Quintiles can be applied in various real-world scenarios to analyze different aspects of a population or dataset. For instance, economists may use quintiles to examine the income distribution among different age groups or to study large data sets such as a country’s population.

Similarly, a company may calculate quintiles to determine the contribution of its lowest-selling products to its overall sales. Quintiles can also establish cut-off points for large data sets, such as employment statistics and agricultural census, at the economic level.

But whatever the reason you need to analyze quintiles, Excel is up to the task.

Quintiles vs. Percentiles vs. Deciles

Quintiles, percentiles, and deciles are different ways to divide a dataset into smaller groups based on the ranking of data values within the set. And just so we’re all on the same page, let’s be clear about what quintiles do and don’t do.

  • Quintiles divide a dataset into five equal parts, each representing 20% of the distribution. For example, the first quintile is where 20% of all data values lie below it, and the second quintile is where 40% of all data values lie below it.
  • Percentiles divide a dataset into 100 equal parts, making it more granular than quintiles. Each piece represents 1% of the distribution, with the 50th percentile being the median.
  • Deciles split the dataset into 10 equal parts, with each part constituting 10% of the distribution. They offer a balance between the detail of percentiles and the simplicity of the quintiles.

In Excel, you can calculate quintiles using the =PERCENTILE function. To do this, enter the formula =PERCENTILE(CELL RANGE, QUINTILE) with the appropriate cell range and quintile value (in decimal format, e.g., 0.2 for the first quintile, 0.4 for the second quintile, etc.).

=PERCENTILE(array, k)

By understanding how quintiles work in Excel, you can uncover insights within your data and gain a deeper understanding of its distribution.

Preparing Your Data

Let’s look at this formula in action with a simple range of product price data.

Organizing Data

Before you begin calculating quintiles in Excel, it’s crucial to have your data appropriately organized. Start by placing your data values in a single column to ensure easy calculations. You may want to create a separate column to store the quintile values later. 

Remember to label your columns to enhance readability and maintain a clear layout.

How to quintile in excel helper formula 2

Data Cleaning

Once your data is organized, it’s time to clean it up. Check for any errors or inconsistencies in your cell entries. Remove duplicate entries, correct any typos, and ensure your values are consistent in units and format. A clean dataset provides accurate results when calculating quintiles and makes your analysis more reliable.

Calculating Quintiles using PERCENTILE Function

Now that our data is prepped and a helper column in place, let’s give our PERCENTILE function a spin!

To apply the Percentile function, follow these steps:

  1. In the cell you want your values returned, start your formula with =PERCENTILE(.
  2. Now select your array as your first argument. In my case, that’s cells A2:A25. Make sure you set absolute references to avoid any errors.
  3. After your range, enter a comma and select the helper column cell of our quintile, D2. If you’re not using a helper column, enter the first quintile, 0.2. Do not lock in this cell, or you’ll get the same value for all your cells.
  4. Close your function with an ending parenthesis and drag your formula down to the other quintiles. If you didn’t create a helper column, adjust your second argument as needed (0.4, 0.6, 0.8, 1).

That’s it! You’ve used Excel to determine the maximum value of each quintile in your data.

Specifically, your formula returned the uppermost limit for each quintile. 

max value of each quintile in excel 3

Try out your numbers in my quintile calculator below for a different perspective. You’ll get the total sum of each quintile and the percentage that value is of the whole sum of data.

Enjoy!

Quintiles Calculator

Results

Input NumbersSorted NumbersQuintile TotalPercentage

Leave a Comment