To find the interquartile range (IQR) in Excel, subtract the first quartile from the third quartile. With your numeric data in A2:A11, enter =QUARTILE.INC(A2:A11,3)-QUARTILE.INC(A2:A11,1) in an empty cell. This returns the spread of the middle half of your data. The guide below explains the inclusive method, the alternative exclusive method, and how to check your result.
What You’ll Need
- Microsoft Excel for Microsoft 365 on Windows. The formulas also work in current Excel for Mac and Excel for the web.
- A column or row of numeric observations. Keep a header outside the selected range, and decide whether zeros are valid values or placeholders that should be excluded.
- An empty cell for the result. You do not need to sort the data before calculating quartiles.
Step-by-Step
-
Enter and check your data. Put one observation in each cell, ideally in a single column, and keep labels out of the calculation range. For the example in this guide, enter the following ten numbers in A2:A11: 2, 4, 5, 7, 8, 10, 12, 15, 18, and 20. You can enter them in any order; Excel calculates quartiles without requiring a sorted list. Check that values that look like numbers are actually stored as numbers, not text. Blank cells are usually fine, but an accidental zero is not blank and affects the result.
-
Choose which quartile convention to use. Excel offers an inclusive method, QUARTILE.INC, and an exclusive method, QUARTILE.EXC. They use different interpolation rules and can produce different answers, particularly with small data sets. QUARTILE.INC is a practical default for many everyday summaries and is the method used in the main example. If a class, report, organization, or existing workbook specifies a convention, follow it consistently rather than switching methods to get a preferred result.
-
Calculate the first quartile, Q1. Q1 is the 25th percentile: roughly one-quarter of the observations fall at or below it. With the ten example values in A2:A11 and an empty output cell selected, enter
=QUARTILE.INC(A2:A11,1). The result is 5.5. Excel interpolates between values when the calculated quartile falls between observations, so Q1 does not have to be one of the numbers in the data. The second argument, 1, tells the function to return the first quartile. -
Calculate the third quartile, Q3. Q3 is the 75th percentile: roughly three-quarters of the observations fall at or below it. With the same values in A2:A11, enter
=QUARTILE.INC(A2:A11,3)in another empty cell. The result is 14.25. The second argument, 3, requests the third quartile. Keeping Q1 and Q3 in separate cells makes the calculation easy to inspect and useful if you want to show your work. -
Subtract Q1 from Q3. The IQR is the distance between these two quartiles. With Q1 in D2 and Q3 in D3, enter
=D3-D2in D4. For this example, Excel calculates 14.25 − 5.5 = 8.75. A larger IQR means the central half of the observations is spread across a wider range; a smaller IQR means that half is more tightly grouped. -
Alternatively, calculate the IQR in one cell. If you do not need to display the quartiles separately, calculate both from the data range and subtract them directly. With the example data still in A2:A11, enter
=QUARTILE.INC(A2:A11,3)-QUARTILE.INC(A2:A11,1)in an empty cell. The result is 8.75, the same as the separate calculation. This formula updates when numeric values in the referenced range change, though you may need to extend the range when adding new rows below A11. -
Use the exclusive method when it is required. QUARTILE.EXC excludes the endpoints when locating quartiles and may return a different IQR. With the same ten observations in A2:A11, enter
=QUARTILE.EXC(A2:A11,3)-QUARTILE.EXC(A2:A11,1)in an empty cell. It returns 11 for this example: the exclusive Q1 is 4.75 and the exclusive Q3 is 15.75, so 15.75 − 4.75 = 11. Do not compare this result with an inclusive result as if they used the same convention. -
Label and verify the answer. Add a nearby label such as IQR (inclusive) so someone reading the sheet knows what the number represents. Check that the range contains only the intended observations, that the chosen method is consistent with your requirements, and that the displayed result is not rounded in a way that hides useful precision. Applying a number format changes how a value looks, not the underlying IQR.
Common Mistakes
- Reversing the subtraction. IQR is Q3 minus Q1, not Q1 minus Q3. Reversing the order produces a negative value, which is not the usual IQR measure.
- Mixing inclusive and exclusive methods. Calculating Q1 with QUARTILE.INC and Q3 with QUARTILE.EXC combines two conventions. Use the same function for both quartiles.
- Including a header or unrelated cells. If the range includes a heading, notes, totals, or another measurement, the calculation may no longer describe the intended data. Select only the observations you want summarized.
- Deleting zeros automatically. A zero may be a genuine observation, such as a measurement of none, or a placeholder for missing data. Decide what it means before excluding it; removing valid zeros changes both quartiles.
- Expecting a quartile to match a listed value. Excel can interpolate between observations. A decimal result such as 5.5 is not necessarily an error, even when every input is a whole number.
Tips
- Keep the quartiles visible. Showing Q1, Q3, and IQR in separate labeled cells makes review easier and helps others verify the subtraction.
- Make the range easier to maintain. If you expect to add observations, convert the data column to an Excel table using Insert > Table. Table references can expand as rows are added, but check the generated formula to confirm that it covers the intended column and excludes headers.
- Use the same rule across comparisons. When comparing IQRs for several groups, use the same quartile convention and comparable data-cleaning rules for every group. Otherwise, differences may reflect calculation choices rather than the data.
- Remember what IQR does and does not say. IQR describes the spread of the middle 50% of observations. It is less affected by extreme values than the full range, but it does not tell you the direction of a trend, the average, or how values are distributed within that middle half.
- Keep precision for calculations. You can format the result to show a chosen number of decimal places, but avoid rounding Q1 and Q3 in intermediate cells before subtracting if accuracy matters. Subtract the stored values, then format the final result for display.
Troubleshooting
The formula shows an error. Check that the range contains at least one numeric value and that the quartile argument is valid. For QUARTILE.INC, use 0 through 4; for QUARTILE.EXC, use a value greater than 0 and less than 4. The values 1 and 3 request Q1 and Q3. Also check for error values in the selected cells, which can affect the calculation.
The result is unexpectedly small, large, or negative. Confirm the range in the formula bar, and look for a reversed subtraction, an unintended zero, or an omitted observation. If you used separate Q1 and Q3 cells, make sure the subtraction points to the correct cells and that both quartiles use the same function.
The formula is displayed as text instead of a result. The cell may be formatted as Text, or the entry may begin with an apostrophe. Change the cell format to General, then re-enter the formula. Pressing F2 and then Enter can also prompt Excel to recalculate a formula already in the cell.
Excel reports a problem with the formula. Confirm that the function name is spelled correctly and that the range uses a colon, as in A2:A11. Excel normally uses commas between function arguments in standard settings. Some regional settings use semicolons instead; if Excel rejects the comma-separated formula, use the separator required by your installation.
New rows do not change the IQR. A formula that refers to A2:A11 will not automatically include data entered in A12. Expand the range in the formula or use an Excel table so added rows can be included. In Excel for Mac and Excel for the web, the quartile formulas work the same way; ribbon layouts and some table controls can look different, but the calculation itself does not change.
Frequently Asked Questions
Is there a direct IQR function in Excel?
No. Excel does not provide a dedicated IQR function. Calculate Q3 and Q1 with the same quartile method, then subtract Q1 from Q3.
Should I use QUARTILE.INC or QUARTILE.EXC?
Use the convention required by your assignment, reporting standard, or existing analysis. If none is specified, choose one method, document it, and use it consistently. The two methods can give different results.
Do I need to sort my values first?
No. QUARTILE.INC and QUARTILE.EXC determine the quartiles from the values in the range. Sorting can help you inspect the data, but it is not a prerequisite.
Can I calculate the IQR for a row instead of a column?
Yes. Use a horizontal range in place of the column range. For example, with values in B2:K2, use that range in both quartile calculations and subtract Q1 from Q3.
Why does my IQR have decimal places?
Excel may interpolate between sorted observations when the quartile position falls between two values. Decimals are normal; the method and input values determine the result.
Does the IQR identify outliers by itself?
No. It measures the middle-half spread. A common outlier rule compares observations with Q1 − 1.5 × IQR and Q3 + 1.5 × IQR, but those thresholds are a screening convention, not proof that a value is wrong.
Related
- Enable Scroll Lock in Excel: Keyboard and On-Screen Methods
- How to Enable the Share Option in Excel for Collaboration
- Link Quantities in Excel: A Step-by-Step Guide
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Outlook Export Contacts: CSV Steps and Common FixesTo export Outlook contacts, open People and choose Manage contacts > Export contacts in new Outlook or Outlook…
- Excel: Highlight the Bottom 10 Values in RedTo highlight the 10 lowest values in red in Excel, select the cells containing your numbers, then choose…
- How to Copy Text With Track Changes in Microsoft WordTo copy text with its existing tracked edits, show all markup in the source document, select the passage,…
- Excel Desktop Keyboard Shortcuts: A Practical GuideFor Excel desktop on Windows, start with these everyday shortcuts: Ctrl+C and Ctrl+V to copy and paste, Ctrl+Z…
- Create Rules in Outlook for Gmail: A Step-by-Step GuideTo create a rule for Gmail in classic Outlook for Windows, open File > Manage Rules & Alerts…
- Excel Notched Box Plot: What It Shows and How to Make OneExcel for Microsoft 365 can make a standard Box & Whisker chart, but it does not have a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.