Returns the quartile of a given data set

Over 1.8 million professionals use CFI to learn accounting, financial analysis, modeling and more. Start with a free account to explore 20+ always-free courses and hundreds of finance templates and cheat sheets. Start Free

What is the QUARTILE Function?

The QUARTILE Function[1] is categorized under Excel Statistical functions. The function will return the quartile of a given data set.

As a financial analyst, QUARTILE can be used to find out, for example, a specific percentage of incomes in a population. The function is very useful in revenue analysis.



The function uses the following arguments:

  1. Array (required argument) – This is the array or cell range of numeric values for which we want the quartile value.
  2. Quart (required argument) – This indicates which value will be returned. It is an integer between 0 and 4, representing the required quartile.

How to use the QUARTILE Function in Excel?

As a worksheet function, QUARTILE can be entered as part of a formula in a cell of a worksheet. To understand the uses of the function, let us consider a few examples:

Example 1

Suppose we are given the following revenue figures:


QUARTILE can return the minimum value, first quartile, second quartile, third quartile, and max value. The function accepts five values for the quart argument, as shown below:

QUARTILE Function - Example 1

The formulas used are below:

QUARTILE Function - Example 1a

Similarly, we used 1 as quart argument for the 25th percentile, 2 for the 50th percentile, 3 for the 75th percentile and 4 for max value.

We get the results below:

QUARTILE Function - Example 1b

Example 2

If we wish to highlight cells by quartile, we can apply conditional formatting with a formula that uses the QUARTILE function. Suppose we are given the following data:

QUARTILE Function - Example 2

If we wish to highlight the minimum quartile, we can use conditional formatting. The formula to be used is:

QUARTILE Function - Example 2a

We get the result below:

QUARTILE Function - Example 2b

If we want, we can highlight every cell based on the quartile it belongs to. For that, we will set four conditional formatting rules:

  1. =A1>QUARTILE(A1:E5,3)
  2. =A1>=QUARTILE(A1:E5,2)
  3. =A1>=QUARTILE(A1:E5,1)
  4. =A1>=QUARTILE(A1:E5,0)

The QUARTILE function is automatic and will calculate the 1st quartile with an input of 1, the 2nd quartile with an input of 2, and the 3rd quartile with an input of 3. With an input of 0, the function returns the minimum value in the data.

We need to arrange the conditional formatting rules so that they run in the same direction. The first rule highlights values greater than the 3rd quartile. The second rule highlights values greater than the 2nd quartile, the 3rd rule highlights data greater than the 1st quartile, and the last rule highlights data greater than the minimum value.

Things to remember about the QUARTILE Function 

  1. #NUM! error – Occurs if either:
    1. The given value of quart is < 0 or > 4
    2. The given array is empty
  2. #VALUE! error – Occurs if the given value of quart is non-numeric.

Click here to download the sample Excel file

Additional Resources

Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly improve your financial modeling. To learn more, check out these additional CFI resources:

Article Sources

  1. QUARTILE Function
0 search results for ‘