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:
![How to Find Quartiles in Excel? 3 Find Quartiles in Excel](https://quickexcel.com/wp-content/uploads/2021/07/number-list-for-quartiles-1.jpg)
Finding the minimum value using the QUARTILE function:
- Go to cell D2 anf type =QUARTILE(A1:A10,0).
![How to Find Quartiles in Excel? 4 Find Quartiles in Excel](https://quickexcel.com/wp-content/uploads/2021/07/quartile-quart-0.jpg)
- Press the Enter key to display the result.
![How to Find Quartiles in Excel? 5 Find Quartiles in Excel](https://quickexcel.com/wp-content/uploads/2021/07/quartile-quart-0-result.jpg)
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).
![How to Find Quartiles in Excel? 6 Find Quartiles in Excel](https://quickexcel.com/wp-content/uploads/2021/07/quartile-quart-1.jpg)
- Press the Enter key to display the result.
![How to Find Quartiles in Excel? 7 Find Quartiles in Excel](https://quickexcel.com/wp-content/uploads/2021/07/quartile-quart-1-result.jpg)
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).
![How to Find Quartiles in Excel? 8 Find Quartiles in Excel](https://quickexcel.com/wp-content/uploads/2021/07/quartile-quart-2.jpg)
- Press the Enter key to display the result.
![How to Find Quartiles in Excel? 9 quartile quart 2 result](https://quickexcel.com/wp-content/uploads/2021/07/quartile-quart-2-result.jpg)
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).
![How to Find Quartiles in Excel? 10 quartile quart 3](https://quickexcel.com/wp-content/uploads/2021/07/quartile-quart-3.jpg)
- Press the Enter key to display the result.
![How to Find Quartiles in Excel? 11 quartile quart 3 result](https://quickexcel.com/wp-content/uploads/2021/07/quartile-quart-3-result.jpg)
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).
![How to Find Quartiles in Excel? 12 quartile quart 4](https://quickexcel.com/wp-content/uploads/2021/07/quartile-quart-4.jpg)
- Press the Enter key to display the result.
![How to Find Quartiles in Excel? 13 quartile quart 4 result](https://quickexcel.com/wp-content/uploads/2021/07/quartile-quart-4-result.jpg)
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.
![How to Find Quartiles in Excel? 14 quartile color scheme](https://quickexcel.com/wp-content/uploads/2021/07/quartile-color-scheme.jpg)
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.
![How to Find Quartiles in Excel? 15 conditional formatting in styles tab](https://quickexcel.com/wp-content/uploads/2021/07/conditional-formatting-in-styles-tab.jpg)
- From the drop-down that appears, click on New Rule… option.
![How to Find Quartiles in Excel? 16 new rule drop down](https://quickexcel.com/wp-content/uploads/2021/07/new-rule-drop-down.jpg)
- 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.
![How to Find Quartiles in Excel? 17 new rule dialog](https://quickexcel.com/wp-content/uploads/2021/07/new-rule-dialog-box.jpg)
- 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.
![How to Find Quartiles in Excel? 18 fourth quartile rule 2](https://quickexcel.com/wp-content/uploads/2021/07/fourth-quartile-rule-2.jpg)
- Click in OK to complete the highlighting.
![How to Find Quartiles in Excel? 19 4th quartile](https://quickexcel.com/wp-content/uploads/2021/07/4th-quartile.jpg)
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.
![How to Find Quartiles in Excel? 20 conditional formatting in styles tab 1](https://quickexcel.com/wp-content/uploads/2021/07/conditional-formatting-in-styles-tab-1.jpg)
- From the drop-down that appears, click on New Rule… option.
![How to Find Quartiles in Excel? 21 new rule drop down 1](https://quickexcel.com/wp-content/uploads/2021/07/new-rule-drop-down-1.jpg)
- 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.
![How to Find Quartiles in Excel? 22 new rule dialog box 1](https://quickexcel.com/wp-content/uploads/2021/07/new-rule-dialog-box-1.jpg)
- 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.
![How to Find Quartiles in Excel? 23 third quratile formula 1](https://quickexcel.com/wp-content/uploads/2021/07/third-quratile-formula-1.jpg)
- Click in OK to complete the highlighting.
![How to Find Quartiles in Excel? 24 3rd quartile](https://quickexcel.com/wp-content/uploads/2021/07/3rd-quartile.jpg)
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.
![How to Find Quartiles in Excel? 25 conditional formatting in styles tab 2](https://quickexcel.com/wp-content/uploads/2021/07/conditional-formatting-in-styles-tab-2.jpg)
- From the drop-down that appears, click on New Rule… option.
![How to Find Quartiles in Excel? 26 new rule drop down 2](https://quickexcel.com/wp-content/uploads/2021/07/new-rule-drop-down-2.jpg)
- 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.
![How to Find Quartiles in Excel? 27 new rule dialog box 2](https://quickexcel.com/wp-content/uploads/2021/07/new-rule-dialog-box-2.jpg)
- 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.
![How to Find Quartiles in Excel? 28 second quartile formula 1](https://quickexcel.com/wp-content/uploads/2021/07/second-quartile-formula-1.jpg)
- Click in OK to complete the highlighting.
![How to Find Quartiles in Excel? 29 2nd quartile 1](https://quickexcel.com/wp-content/uploads/2021/07/2nd-quartile-1.jpg)
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.
![How to Find Quartiles in Excel? 30 conditional formatting in styles tab 3](https://quickexcel.com/wp-content/uploads/2021/07/conditional-formatting-in-styles-tab-3.jpg)
- From the drop-down that appears, click on New Rule… option.
![How to Find Quartiles in Excel? 31 new rule drop down 3](https://quickexcel.com/wp-content/uploads/2021/07/new-rule-drop-down-3.jpg)
- 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.
![How to Find Quartiles in Excel? 32 new rule dialog box 3](https://quickexcel.com/wp-content/uploads/2021/07/new-rule-dialog-box-3.jpg)
- 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.
![How to Find Quartiles in Excel? 33 first quartile formula 1](https://quickexcel.com/wp-content/uploads/2021/07/first-quartile-formula-1.jpg)
- Click in OK to complete the highlighting.
![How to Find Quartiles in Excel? 34 1st quartile 1](https://quickexcel.com/wp-content/uploads/2021/07/1st-quartile-1.jpg)
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.