How to Use the Quartile Function in Excel
Excel's Quartile function determines the upper range of each quartile of data, each of which contains 25 percent of numerically sorted data.
Quartiles divide data into four groups that are delineated by ranges, each of which contain 25 percent of data points. To manually find quartiles, you need to sort data numerically and draw three evenly spaced lines to create four distinct groups. Where the lines fall in the sorted data indicate the upper values of the preceding quartiles.
As an example, if your data consisted of 12 one's and one 10, the division line between the first, second and third quartiles falls on a 1, but the forth quartile falls on 10, as indicated in the image above.
In large data sets, manually finding quartiles is not only tedious, it's also inaccurate, so Microsoft Excel 2013 includes three quartiles functions to simplify the task. The Quartile function persists from previous Excel versions for backwards compatibility, which also means it's a good choice when sharing spreadsheets with users who may not have the latest Excel version.
Enter your data in an empty column or group of cells.
Enter the Quartile function in an empty cell using the following format:
The Quartile function is antiquated and not guaranteed to be included in future versions of Excel. Unless you're sending a spreadsheet to a user of previous versions of Excel, use the Quartile.Inc function instead to future-proof your spreadsheet. This updated function produces the same results as the Quartile function and uses the same format.
Change "range" to reflect the range of cells that contain your data and "quartile" to reflect the quartile you need. In the above example, enter =QUARTILE(A1:A12,3) to find the upper value in the third quartile. Likewise, change "quartile" to 1, 2 or 4 to find the upper point of the first, second or fourth quartile, respectively. Enter 0 to find the lowest point in the first quartile.
As the image above illustrates, manually sorting and finding the quartiles produces similar results, but the results are not as accurate.
Quartile.Exc is another function worth considering, but it generally widens the second and third quartiles at the expense of the first and fourth quartiles. This consequence helps locate outliers that fall into the latter groups. As an example, you might prefer this function to locate sales staff who fall far behind, or greatly surpass, the rest of the sales team.