How to Find IQR in Excel With QUARTILE Functions

Formulas & functions · Updated November 20, 2026

To find the interquartile range (IQR) in Excel, subtract the first quartile from the third quartile: use =QUARTILE.INC(A2:A9,3)-QUARTILE.INC(A2:A9,1) when your numbers are in A2:A9. The result describes the spread of the middle half of your data. Before calculating, decide whether you want Excel’s inclusive or exclusive quartile convention, because the two methods can produce different answers.

What You’ll Need

  • A Microsoft Excel workbook with the numeric observations you want to analyze. The steps below use Excel for Microsoft 365 on Windows.
  • A single column or row of values is easiest to work with. The example uses eight numbers in cells A2:A9, with a heading in A1.
  • A consistent definition of quartiles if you need to compare your result with a textbook, report, or another software package. This guide uses QUARTILE.INC for the main example.

Step-by-Step

  1. Enter or select your data. Put the observations in one range, such as A2:A9, and keep the heading outside the range. For the worked example, enter 12, 15, 18, 20, 22, 24, 27, and 30 in A2 through A9. The values do not have to be sorted for the quartile functions to work, although sorting can make the data easier to inspect. Avoid including totals, notes, or unrelated numbers in the selected range.
  2. Choose where the answer will go. Click a blank cell, such as B2, for the IQR result. You can calculate the answer directly in one cell, or place the first and third quartiles in separate cells first. Separate quartile results are useful if you want to show your working, check the values, or reuse them in other calculations.
  3. Calculate the first quartile, Q1. With the eight example values in A2:A9, enter =QUARTILE.INC(A2:A9,1) in a blank cell. Excel returns 17.25. Q1 is the point at or below which the lower quarter of the observations falls under the inclusive convention. It does not have to be one of the original values: Excel interpolates between observations when the requested percentile falls between them.
  4. Calculate the third quartile, Q3. With the same values in A2:A9, enter =QUARTILE.INC(A2:A9,3) in another blank cell. Excel returns 24.75. Q3 marks the point at or below which roughly three quarters of the observations fall. In this example the values between Q1 and Q3 cover the middle half of the data.
  5. Subtract Q1 from Q3. With Q1 in B2 and Q3 in B3, enter =B3-B2 in B4. The result is 7.5, because 24.75 minus 17.25 equals 7.5. This difference is the interquartile range. It is a measure of spread in the same units as the original observations, so if your data is in centimeters, the IQR is also in centimeters.
  6. Or calculate the IQR in one cell. If the data is still in A2:A9 and you want a direct result, enter =QUARTILE.INC(A2:A9,3)-QUARTILE.INC(A2:A9,1). Excel returns 7.5 for the example. This is convenient for a quick calculation, while the separate-cell method makes Q1 and Q3 visible for auditing. Both methods use the same inclusive quartile definition.
  7. Check which quartile method your work requires. QUARTILE.INC includes the minimum and maximum when determining percentile positions. QUARTILE.EXC excludes the endpoints in its calculation. To see how that affects the example, with the values in A2:A9 enter =QUARTILE.EXC(A2:A9,1) for Q1 and =QUARTILE.EXC(A2:A9,3) for Q3. The answers are 15.75 and 26.25, so the exclusive IQR is 10.5. Do not mix a Q1 calculated with one method and a Q3 calculated with the other.
  8. Label and format the result. Add a clear label such as IQR beside the answer, and use a number format that suits the data. If the source values are measurements with decimal places, choose an appropriate number of decimal places rather than rounding the underlying calculation prematurely. In Excel for Microsoft 365 on Windows, use Home > Number to choose a number format. The same ribbon command is available in Excel for Mac, though the ribbon layout may vary slightly; Excel for the web also supports number formatting from its Home tab.
  9. Interpret the result in context. An IQR of 7.5 means the distance between the first and third quartiles is 7.5 units. It does not mean that every observation is within 7.5 units of the median, nor does it give the full range from the smallest to the largest value. Compare IQRs only when the units and quartile conventions are compatible. A larger IQR indicates more spread in the middle half of the observations, but it does not by itself explain why the data varies.
  10. Optionally check for potential outliers. A common exploratory rule marks values below Q1 minus 1.5 times the IQR, or above Q3 plus 1.5 times the IQR, for review. With Q1 in B2, Q3 in B3, and IQR in B4, enter =B2-1.5*B4 in B5 to get the lower fence, 6, and =B3+1.5*B4 in B6 to get the upper fence, 36. The example observations, from 12 to 30, fall between these fences. These are flags for inspection, not proof that a value is an error or should be removed.

Common Mistakes

Using the wrong range. If a heading, subtotal, or extra cell is included by mistake, the calculation may no longer represent the intended observations. Check the range shown in the formula bar and select only the data cells. If the column contains a header in A1 and values in A2:A50, for example, use A2:A50 rather than A1:A50.

Confusing quartiles with the median and full range. The median is the second quartile, Q2. The full range is the maximum minus the minimum. Neither is the IQR. The IQR specifically uses Q3 minus Q1, so check that the quartile arguments are 3 and 1 in the correct order. Reversing the subtraction produces a negative result, which is not the usual IQR.

Mixing inclusive and exclusive methods. QUARTILE.INC and QUARTILE.EXC are both legitimate Excel functions, but they use different percentile conventions. Their difference is especially noticeable with small samples. Follow the method stated in your assignment or analysis plan, and use the same method for every group you compare. If no method is specified, state which function you used so another person can reproduce the result.

Assuming the answer must be an original observation. Quartile calculations can interpolate between sorted values, so Q1, Q3, and the IQR may contain decimals even when all input values are whole numbers. That is expected. Avoid rounding Q1 or Q3 before subtracting them; round only the displayed result if a report needs a simpler presentation.

Including blanks or non-numeric entries without checking. Blank cells and text in a referenced range do not act like ordinary numeric observations, but a range with missing data may still be incomplete. Check whether blanks mean unavailable measurements or genuine omissions. Error values can also cause a calculation to return an error. Do not replace missing values with zero unless zero is actually a valid observation in your dataset.

Tips

Keep the raw observations unchanged and put calculations in a separate area or worksheet. This makes it easier to review the source data and reduces the risk of accidentally overwriting a value. A small summary table with labels for Q1, Q3, and IQR is clear for both you and anyone who receives the workbook. If you use formulas in that table, make the ranges consistent so each summary refers to the same observations.

For repeated analysis, use an Excel Table or a named range so formulas are easier to read and can adapt when data is added. Be deliberate about which rows belong in the analysis; automatically including new rows is helpful only if every added row is a valid observation. After changing or extending the data, confirm that the formula range covers the intended records.

When comparing categories, calculate each group’s IQR with the same quartile convention and comparable inclusion rules. An IQR is resistant to extreme values compared with the full range, but that does not make it a complete description of a distribution. Consider the median and a chart as well. A box-and-whisker chart can help display quartiles and potential outliers, while the cell formula gives you a precise numeric result.

Function names are the same in current Excel for Windows, Mac, and the web. Excel normally uses commas between formula arguments in English-language settings, as shown here. Some regional settings use semicolons instead; if Excel rejects a correctly written formula, check the list separator configured for your system. The formulas themselves do not require a special keyboard shortcut or add-in.

Troubleshooting

Excel displays #NUM! for QUARTILE.EXC. The exclusive method cannot calculate every percentile for every sample size. For example, a very small range may not provide a valid position for the requested quartile. Check that the range contains enough numeric observations for the exclusive calculation. If your method permits it, use QUARTILE.INC instead and document that choice.

The cell shows the formula instead of a number. The cell may be formatted as Text, or the formula may have been entered with an apostrophe before the equals sign. Change the cell format to General or Number, edit the entry, and press Enter. Also check that Show Formulas has not been turned on; in Excel for Windows, Ctrl+grave accent toggles formula display. On Mac, the shortcut may depend on the keyboard layout; use the Formulas tab and check Show Formulas if needed.

The result is unexpectedly zero or much larger than expected. Inspect the selected cells for repeated values, misplaced data, or a range that includes more than the intended sample. Confirm that the cells contain numbers rather than text that looks numeric. You can click a suspicious cell and check its alignment or use Excel’s number conversion tools, but verify the original source before changing data. A constant dataset legitimately has an IQR of zero.

Excel reports a formula error or will not accept the entry. Check for a misspelled function name, missing parenthesis, or an incorrect separator. In some locales, replace argument commas with semicolons if that is the separator Excel expects. Ensure that the range reference uses a colon between its first and last cells. If the function name is not recognized in an older Excel release, use QUARTILE, which is retained for compatibility, or update Excel; for new workbooks, QUARTILE.INC makes the inclusive method explicit.

Your result differs from a calculator, class handout, or another program. Compare the percentile convention first. The other source may use an exclusive method, a different interpolation rule, or a different rule for an even-sized sample. Also check that both calculations use exactly the same values and exclude missing entries in the same way. Report the method along with the result instead of treating one convention as universally correct.

Frequently Asked Questions

What is the IQR formula in Excel?
Subtract the first quartile from the third quartile. For a range such as A2:A9, calculate Q3 with QUARTILE.INC using quartile number 3 and Q1 using quartile number 1, then subtract Q1 from Q3. The inclusive function is a common choice, but use the convention required for your analysis.

Is QUARTILE.INC or QUARTILE.EXC better?
Neither is always better. They define percentile positions differently, which can change quartiles and the IQR, particularly in small samples. Use the method required by your class, organization, or comparison standard. If there is no requirement, choose one method consistently and name it in your notes or report.

Do I need to sort my data before using QUARTILE.INC?
No. Excel calculates the quartiles from the numeric values in the supplied range; it does not require you to sort the cells first. Sorting may help you spot unusual values or data-entry problems, but it is not a prerequisite for the formula.

Can the IQR be a decimal when the data is whole numbers?
Yes. Excel may interpolate between observations to determine Q1 and Q3, so the quartiles and their difference can be decimal values. This is normal. Keep full precision in the calculation and format the displayed result to an appropriate number of decimal places.

Can I use the IQR to identify outliers?
You can use the 1.5-times-IQR fences as a common screening rule: values below the lower fence or above the upper fence are potential outliers. Investigate flagged values in context before deciding what to do. A legitimate rare observation should not automatically be deleted.

Does this work in Excel for Mac or Excel for the web?
Yes. QUARTILE.INC and QUARTILE.EXC work in current Excel for Microsoft 365 on Windows, Mac, and the web. Enter the same formula in a cell. Ribbon placement and some keyboard shortcuts can differ by platform, but no separate quartile procedure is needed.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.