In Excel, use =QUARTILE.INC(A2:A20,3) to find the upper quartile, or third quartile (Q3), of the numbers in A2:A20. The function returns the value at the 75th percentile using Excel’s inclusive method; choose QUARTILE.EXC instead only when your analysis specifically calls for the exclusive method.
The Excel formula for the upper quartile
The standard formula for most everyday spreadsheets is =QUARTILE.INC(array,3), where array is the range containing your data and 3 tells Excel to calculate the third quartile. Replace the sample range with the cells in your worksheet. For example, if the measurements are in D2:D51, use that range in place of the example range above.
In Microsoft 365 for Windows, type the formula in an empty cell and press Enter. Excel for Mac and Excel for the web use the same function syntax. Depending on your regional settings, Excel may expect semicolons rather than commas between arguments; if a comma version is rejected, check the list separator used by your installation.
The older QUARTILE function is also available for compatibility with older workbooks. It uses the inclusive method, so for a new formula, QUARTILE.INC makes the method explicit and is generally easier to interpret. Both functions need a numeric range or array as their first argument and a quartile number from 0 to 4 as their second.
What the upper quartile tells you
Quartiles divide an ordered data set into sections using three cut points. Q1 is the 25th percentile, Q2 is the median or 50th percentile, and Q3 is the 75th percentile. The upper quartile is commonly used to mean the Q3 cut point: roughly three quarters of the observations are at or below it, and roughly one quarter are at or above it.
That description is approximate when the data set is small, contains ties, or produces a cut point between two observed values. A quartile is not necessarily one of the values that appears in the source data. Excel can interpolate between neighboring observations to calculate it. Also, Q3 is a boundary value, not the average of the top quarter of the observations.
Quartiles are useful for summarizing distributions, comparing groups, and building a box-and-whisker plot. The interquartile range (IQR), calculated as Q3 minus Q1, describes the spread of the middle half of the data. Q3 alone does not show how widely the upper values vary, so consider the median, Q1, and the IQR as well when interpreting a distribution.
Worked example with a small data set
Suppose A2:A9 contains these eight values, in any order: 4, 7, 9, 10, 12, 15, 18, and 21. With those values in A2:A9, enter =QUARTILE.INC(A2:A9,3). The result is 15.75.
Here is why. In ascending order, the values are already 4, 7, 9, 10, 12, 15, 18, and 21. The inclusive method locates the 75th percentile at position 1 + (8 − 1) × 0.75, which is 6.25. That position is one quarter of the way from the sixth value, 15, to the seventh value, 18. The interpolation is 15 + 0.25 × (18 − 15), or 15.75. The result need not be an observation in the original list.
For comparison, with the same values in A2:A9, =QUARTILE.EXC(A2:A9,3) returns 17.25. Its position is (8 + 1) × 0.75 = 6.75, three quarters of the way from 15 to 18. The two answers differ because the methods use different rules for locating percentile positions, not because one formula has sorted the data differently.
Inclusive and exclusive quartiles compared
QUARTILE.INC includes the endpoints of the percentile scale: quartile number 0 returns the minimum and quartile number 4 returns the maximum. It is usually the straightforward choice for general descriptive summaries, and it is often a sensible default when a teacher, report, or analysis has not specified a different convention.
QUARTILE.EXC uses an exclusive percentile convention. Its quartile argument must be 1, 2, or 3; it does not accept 0 or 4. With some sample sizes, its position falls between observations and is interpolated. With very small samples, a requested exclusive percentile may not be defined, so Excel can return an error. Do not assume that “exclusive” means Excel simply removes the smallest and largest numbers from the range.
There is no single convention that every field or software package must use for every purpose. If you are reproducing a published result, follow the method named in that source. If no method is specified, state that you used the inclusive method. Consistency matters when comparing quartiles across reports, teams, or software, because different conventions can return different values from the same data.
Choosing between QUARTILE and PERCENTILE
For Q3, the inclusive quartile formula has a direct percentile equivalent. With the observations in A2:A9, =PERCENTILE.INC(A2:A9,0.75) returns the same 15.75 as QUARTILE.INC for that range. The percentile function uses a fraction from 0 to 1, so 0.75 represents the 75th percentile; the quartile function uses the integer 3.
Choose the quartile function when you want to communicate Q1, Q2, or Q3 using familiar quartile numbering. Choose a percentile function when your target is another percentile, such as the 90th percentile. Excel also has PERCENTILE.EXC for the exclusive convention. As with quartiles, match the inclusive or exclusive method to the requirements of the analysis rather than switching methods just to obtain a preferred result.
For a formula that is easy for another person to audit, use a clearly labeled range and specify the method in a nearby note or column heading. A label such as “Q3, inclusive” makes it less likely that a later reader will compare the result with an exclusive calculation and assume the workbook is wrong.
Prepare the data range before calculating
Quartile results are only as useful as the values included in the range. Check that the selected cells contain the observations you intend to analyze, rather than a title, subtotal, or unrelated number. If you are calculating separate quartiles for separate groups, give each group its own range or use a carefully defined filtering approach; a single range containing multiple groups returns a quartile for all included numbers together.
Before calculating, consider these practical checks:
- Confirm that each included numeric value represents one observation and that units are consistent. Mixing minutes and hours, for example, makes the resulting cut point hard to interpret.
- Decide how to handle missing values. A genuinely empty cell is not a numeric observation, but a zero is a number and will be included.
- Check whether numbers imported as text are being treated as numbers. Text entries in a referenced range are not reliable substitutes for numeric observations; convert valid numeric text to numbers if it belongs in the analysis.
- Decide whether filtered-out or hidden rows should count. A regular range-based quartile calculation is not a visible-rows-only summary; it can include values in hidden rows.
- Use a range that can expand with your data if new records will be added, such as an Excel table column, and verify that the formula refers to the intended records.
These checks prevent a common misunderstanding: Excel does not infer which rows are “real,” which zeros are mistakes, or which records belong to a particular category. The formula calculates from the numeric values in its supplied array; data-cleaning decisions remain part of the analysis.
Common errors and unexpected results
If Excel returns #NUM!, check the second argument. The quartile number must be between 0 and 4 for the inclusive and legacy functions, and between 1 and 3 for QUARTILE.EXC. A misspelled function name can produce a name error, while an incompatible argument or invalid range can also prevent the calculation.
If the result seems too high or too low, first inspect the range for included zeros, accidental totals, values from another group, or numbers stored as text. Then confirm that the intended method is being used. Sorting the source cells is not required: the quartile functions calculate the order of numeric values internally. Sorting can help you inspect the data, but it should not be necessary to make the formula work.
A quartile may show decimal places even when every source value is a whole number. That is normal when Excel interpolates between observations. Formatting the result cell to show fewer decimal places changes only how the result is displayed, not the underlying calculated value. Conversely, a displayed rounded result can look identical to a different value, so increase the displayed decimal places when checking a discrepancy.
Finally, an upper quartile is not an automatic outlier detector. A value above Q3 is simply in the upper portion of the distribution; many such values may be ordinary. A common box-plot rule flags possible high outliers above Q3 + 1.5 × IQR, but that is a screening convention, not proof that a value is erroneous. Investigate the data and the context before removing observations.
Frequently Asked Questions
What is the exact formula for Q3 in Excel?
Use =QUARTILE.INC(range,3), replacing range with the cells that contain your numbers. The 3 requests the third quartile, or 75th percentile, using the inclusive method.
Is the upper quartile the same as the 75th percentile?
In ordinary statistical usage, yes: the upper quartile is Q3, the cut point at the 75th percentile. The exact numeric result can depend on the percentile convention used, especially when the data set is small.
Should I use QUARTILE.INC or QUARTILE.EXC?
Use QUARTILE.INC when no method is specified and you want the inclusive convention. Use QUARTILE.EXC when a course, report, or standard specifically requires the exclusive convention. Keep the method consistent when comparing results.
Does the data have to be sorted first?
No. Excel calculates the quartile from the numeric values in the supplied range without requiring you to sort the worksheet. Sorting is optional and may make it easier to review the observations.
Why is my Q3 not one of the values in the data?
Excel may interpolate between two neighboring values to locate the percentile position. A decimal result between observations is therefore normal and does not mean the formula is incorrect.