Let’s learn how we can find quartiles in Excel. A quartile is a statistical term that is used to describe the division of a dataset into four equal intervals. Each quartile has 25% of the total number of data points in a dataset. The four equal intervals are divided by three points namely – lower quartile, median and upper quartile. The median is the value that separates the upper and lower halves of a dataset or population or simply the middle number in a sorted list of numbers.

## Find Quartiles in Excel Using the QUARTILE Function

The **QUARTILE** function in Excel returns the quartile of a data set. Syntax:** QUARTILE(array,quart)**, where array is the numeric values or the cell ranges containing the numeric values and quart parameter specifies which value should be returned. When quart = **0**, the minimum value of the dataset is returned, quart = **1** returns the first or lower quartile, quart = **2 **returns the median value, quart = **3** returns the third or upper quartile, and quart = **4** returns the maximum value of the dataset.

Let us consider a sample dataset of numbers to be divided into quartiles:

Finding the minimum value using the **QUARTILE** function:

- Go to cell D2 anf type
**=QUARTILE(A1:A10,0)**.

- Press the Enter key to display the result.

As you can see when quart = 0, the minimum value in the dataset = 11 is returned.

Finding the first or lower quartile using the **QUARTILE** function:

- Go to cell D3 and type
**=QUARTILE(A1:A10,1)**.

- Press the Enter key to display the result.

As you can see when quart = 1, the first or lower quartile of the dataset = 23.5 is returned.

Finding the median value using the **QUARTILE** function:

- Go to cell D4 and type
**=QUARTILE(A1:A10,2)**.

- Press the Enter key to display the result.

Here you can see that when quart = 2, the median value of the dataset = 38.5 is returned.

Finding the third or upper quartile using the **QUARTILE** function:

- Go to cell D5 and type
**=QUARTILE(A1:A10,3)**.

- Press the Enter key to display the result.

Here you can see that when quart = 3, the third or upper quartile of the dataset = 55.25 is returned.

Finding the maximum value using the **QUARTILE** function:

- Go to cell D6 and type
**=QUARTILE(A1:A10,4)**.

- Press the Enter key to display the result.

As you can see when quart = 4, the maximum value of the dataset = 89 is returned.

Note:

- The
**QUARTILE**function returns the**#NUM!**error value if the array is empty. - If the parameter
**quart**is less than zero or greater than four, the**QUARTILE**function returns the**#NUM!**error value. - If the parameter
**quart**is non-numeric, the**QUARTILE**function returns the**#VALUE!**error value. - The
**QUARTILE**function returns the same value as**MIN**,**MEDIAN**, and**MAX**when the value of the parameter**quart**is 0, 2, and 4 respectively.

## Highlight Quartiles in Excel

It is always good to highlight quartile values of a data set for easy identification. This can be achieved using **Conditional Formatting** in Excel.

First, select the color code for each quartile.

To highlight the data points above the third quartile:

- Select the cells containing the data points.
- Click on
**Conditional Formatting**in the**Styles**group on the**Home**tab.

- From the drop-down that appears, click on
**New Rule…**option.

- Select
**Use a formula to determine which cells to format**option from the**Select a Rule Type**section in the**New Formatting Rule**dialog box.

- In the
**Format values where this formula is true**text box, type**=A1>=QUARTILE($A$1:$A$10,3)**and select a color for highlighting as per the color code from the**Format…**option.

- Click in
**OK**to complete the highlighting.

To highlight the data points in between median and third quartile:

- Select the cells containing the data points.
- Click on
**Conditional Formatting**in the**Styles**group on the**Home**tab.

- From the drop-down that appears, click on
**New Rule…**option.

- Select
**Use a formula to determine which cells to format**option from the**Select a Rule Type**section in the**New Formatting Rule**dialog box.

- In the
**Format values where this formula is true**text box, type**=AND(A1>=QUARTILE($A$1:$A$10,2),A1<QUARTILE($A$1:$A$10,3))**and select a color for highlighting as per the color code from the**Format…**option.

- Click in
**OK**to complete the highlighting.

To highlight the data points in between the first quartile and median:

- Select the cells containing the data points.
- Click on
**Conditional Formatting**in the**Styles**group on the**Home**tab.

- From the drop-down that appears, click on
**New Rule…**option.

- Select
**Use a formula to determine which cells to format**option from the**Select a Rule Type**section in the**New Formatting Rule**dialog box.

- In the
**Format values where this formula is true**text box, type**=AND(A1>=QUARTILE($A$1:$A$10,1),A1<QUARTILE($A$1:$A$10,2))**and select a color for highlighting as per the color code from the**Format…**option.

- Click in
**OK**to complete the highlighting.

To highlight the data points in below first quartile:

- Select the cells containing the data points.
- Click on
**Conditional Formatting**in the**Styles**group on the**Home**tab.

- From the drop-down that appears, click on
**New Rule…**option.

- Select
**Use a formula to determine which cells to format**option from the**Select a Rule Type**section in the**New Formatting Rule**dialog box.

- In the
**Format values where this formula is true**text box, type**=AND(A1>=QUARTILE($A$1:$A$10,0),A1<QUARTILE($A$1:$A$10,1))**and select a color for highlighting as per the color code from the**Format…**option.

- Click in
**OK**to complete the highlighting.

## Conclusion

In this tutorial, we learned how to find quartiles using an inbuilt function **QUARTILE** and to highlight them using **Conditional Formatting** for easy identification in Excel.