How to Use QUARTILE Function in Excel (5 Suitable Examples)

The QUARTILE function is a built-in function in Excel that is categorized as a Statistical Function. Quartiles are values that split your data into quarters. It divides your data into four segments according to where the numbers fall on the number line. It is one of those essential functions. In this tutorial, you will learn every detail of the QUARTILE function in Excel. This tutorial will be on point with suitable examples and proper illustrations.

The above screenshot is an overview of the article. It represents a simplified application of the QUARTILE function in Excel. You’ll learn more about the dataset as well as the methods and procedures under different criteria in the following sections of this article.

Introduction to QUARTILE Function

⏺ Function Objective

Find the quartile (each of four equal groups) for a conveyed set of data.

⏺ Syntax

⏺ Arguments Explanations

⏺ Returns

The QUARTILE function returns a numeric value. The quartile of the dataset.

⏺ Available in

Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000.

How to Use QUARTILE Function in Excel

Basically, to use the QUARTILE function in Excel, we first select a range from a dataset to analyze. Then, we have to enter the quart values. Now, we have already shown the quart values and their meaning.

Before we show you the procedures, let’s have a quick look:

• The first quartile means the 25th percentile – as 25% of the data is lower than this value.
• The second quartile is the median of the dataset, and we can also call it the 50th percentile as 50% of the data is lower than this.
• The third quartile means the 75th percentile, as 75% of the data is lower than this value.

Take a look at this dataset:

Here, you can see we have some random values. Now, we are going to find the first quartile (25th percentile) of this dataset.

📌 Steps

• First, type the following formula in cell D5:

• After that, press Enter.

As you can see, we have successfully calculated the first quartile of the dataset.

Similarly, you can find the rest of the quartiles:

QUARTILE Function in Excel: 5 Practical Examples

In the following sections, we will provide you with five suitable and useful examples that will clear your idea about this function. We recommend you learn and practice all these examples to enrich your Excel knowledge.

1.  Use of QUARTILE Function to Find Quartiles of Salaries

In this example, we will guide you to calculate the 50th percentile or the second quartile from a range of salaries. It will be a pretty simple example. But it will help you in a lot of scenarios.

Take a look at the following dataset:

Here, we have some people’s names and their salaries. Now, our goal is to find the second quartile or the 50th percentile of the salaries.

📌 Steps

• First, type the following formula in cell E5:

• After that, press Enter.

As you can see, we have successfully used the QUARTILE function of Excel to find the second quartile.

2.  QUARTILE Function to Find Quartiles of Marks in Excel

Similar to the previous method, we are going to use the QUARTILE function here.

Now, take a look at the following dataset:

Here, we have some students’ marks. Now, our goal is to find the 3rd quartile of this dataset using the QUARTILE function.

📌 Steps

• First, type the following formula in cell E5:

• After that, press Enter.

As you can see, we have successfully used the QUARTILE function of Excel to find the third quartile.

3.  Find Quartiles for All the “quart” Values

Now, we already know all the possible quart values and their meaning. Now, we are going to use the QUARTILE function to find all the quartile values from the previous dataset.

To demonstrate this, we are going to use the following dataset:

Follow these simple steps to find all the quartiles.

📌 Steps

• First, type the following formula in cell G5:

Here, we are using the Cell reference in the quart argument.

• Then, press Enter.
• Now, drag the Fill handle icon over the range of cells G6:G9.

In the end, we were successful in finding all the QUARTILE values for all the quart values in Excel.

4.  Examples of QUARTILE.INC & QUARTILE.EXC in Excel

After Excel 2007, Microsoft substituted the QUARTILE function with its two new variations: QUARTILE.INC and QUARTILE. EXC.

The QUARTILE function still works in Excel. You can use it in newer versions as well. In our opinion, you should use QUARTILE.INC or QUARTILE.EXC wherever possible. We don’t know when Microsoft will drop the QUARTILE function.

Now, QUARTILE.INC works the same as the QUARTILE function. The “INC” means the function is “inclusive”. This means that QUARTILE.INC contains the minimum and maximum values when computing the quartiles in the data range.

Now, we are going to use this in the previous dataset. Just type the following formula in cell G5:

Then, you drag the Fill handle icon over the range of cells G6:G9. It will show the following:

Now, the QUARTILE.EXC works almost similar to the QUARTILE.INC function. The basic difference is it excludes the minimum and maximum values while calculating the quartiles from the given range.

Now, type the following formula in cell G5:

After that, when you drag the Fill handle icon over the range of cells G6:G9, it will show the following:

As you can see from the example, we have used the QUARTILE.INC and QUARTILE.EXC functions in the same scenarios in Excel.

5. QUARTILE Function in VBA

Now, you can also use the QUARTILE function in Excel. If you are a VBA freak like me, you will like this. Here, we are showing you some simple steps to show you the QUARTILE function in VBA.

📌 Steps

• First, press Alt+F11 on your keyboard to open the VBA editor.
• Then, click on Insert > Module.

• After that, type the following code:

Sub quartile_method()  

Dim my_array 
Dim wf As WorksheetFunction 

my_array = Array(10, 4.7, 13.98, 12, 23.9, 16) 
Set wf = Application.WorksheetFunction 
MsgBox wf.Quartile(my_array, 2) 

End Sub 

• Now, save the file.
• After that, click on Run. It will show you this message box.

As you can see, we have successfully used the QUARTILE function in VBA codes.

💬 Things to Remember

✎ The QUARTILE Function divides the dataset into four equivalent parts.

✎ It will show #NUM! error if the quart value is less than 0 or more than 4.

✎ If your given array is empty, it will also show  #NUM! error.

✎ It will return #VALUE! error if the given value of quart is non-numeric.

Download Practice Workbook 

Download this practice workbook. 

Conclusion

To conclude, I hope this tutorial has provided you with a piece of useful knowledge to use the QUARTILE function in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.

Keep learning new methods and keep growing!

<< Go Back to Excel Functions | Learn Excel