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.

How to Use Quartile Function in Excel

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.


Download Practice Workbook 

Download this practice workbook. 


Introduction to QUARTILE Function

⏺ Function Objective

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

⏺ Syntax

=QUARTILE (array, quart)

⏺ Arguments Explanations

Arguments Required/Optional Explanation
array Required The array or cell range of numeric values for which you want to calculate the quartile value.
quart Required Indicates which value to return. 
  • If quart=0, it indicates the minimum value 
  • If quart=1, it indicates the First quartile (25th percentile) 
  •  If quart=2, it indicates the Median value (50th percentile) 
  • If quart=3, it signifies Third quartile (75th percentile) 
  • If quart=4, it represents the maximum value

⏺ 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, 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:

dataset of QUARTILE function

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:
=QUARTILE(B5:B10,1)
  • 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:

How to Use Quartile Function in Excel

Read More: How to Use MEDIAN Function in Excel (4 Suitable Examples)


5 Practical Examples with QUARTILE Function in Excel

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:

Use of QUARTILE Function to Find Quartiles of Salaries

Here, we have some persons’ 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:
=QUARTILE(C5:C10,2)
  • After that, press Enter.

Use of QUARTILE Function to Find Quartiles of Salaries

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

Read More: How to Use PERCENTILE Function in Excel (With an Example)


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:
=QUARTILE(C5:C10,3)
  • After that, press Enter.

QUARTILE Function to Find Quartiles of Marks in Excel

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


Similar Readings


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:
=QUARTILE($C$5:$C$10,E5)

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.

Find Quartiles for All The “quart” Values

In the end, we are successful to find 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, the QUARTILE.INC works as the same as the QUARTILE function. The “INC” means in 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:

=QUARTILE.INC($C$5:$C$10,E5)

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

Example of QUARTILE.INC & QUARTILE.EXC in Excel

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:

=QUARTILE.EXC($C$5:$C$10,E5)

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.

Read More: How to Use VAR Function in Excel (4 Examples)


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.

QUARTILE Function in VBA 

  • 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


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.

Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.

Keep learning new methods and keep growing!


Related Articles

Shanto

Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo