How to Calculate the Interquartile Range in Google Sheets

Key Insights

Calculate IQR in Google Sheets with a single formula: =QUARTILE(range, 3) - QUARTILE(range, 1) to find the spread of your middle 50% of data
Use the 1.5×IQR rule to create outlier boundaries—any value below Q1 - 1.5×IQR or above Q3 + 1.5×IQR is a statistical outlier worth investigating
Choose QUARTILE.INC for most business applications (includes endpoints) and QUARTILE.EXC for stricter statistical analysis (excludes endpoints)

Introduction to IQR

The interquartile range (IQR) measures the spread of the middle 50% of your data. It’s calculated by subtracting the first quartile (Q1) from the third quartile (Q3). While that sounds academic, IQR solves a real problem: understanding how spread out your data is without getting fooled by extreme values.

Standard deviation gets all the attention in statistics courses, but IQR is often the better choice for real-world data analysis. Standard deviation assumes your data follows a normal distribution and gets heavily skewed by outliers. IQR doesn’t care about either problem. If you’re analyzing sales figures, customer response times, or any dataset that might have a few extreme values, IQR gives you a more honest picture of your data’s spread.

IQR also forms the foundation of the most practical outlier detection method. The 1.5×IQR rule gives you concrete boundaries for identifying unusual data points—something standard deviation can’t do as cleanly.

Understanding Quartiles in Google Sheets

Before calculating IQR, you need to understand the quartiles themselves. Quartiles divide your sorted data into four equal parts:

Q1 (25th percentile): 25% of data falls below this value
Q2 (50th percentile): The median—50% of data falls below this value
Q3 (75th percentile): 75% of data falls below this value

Google Sheets provides three functions for calculating quartiles:

=QUARTILE(data, quart)      // Original function, same as QUARTILE.INC
=QUARTILE.INC(data, quart)  // Inclusive method (includes 0 and 1 percentiles)
=QUARTILE.EXC(data, quart)  // Exclusive method (excludes endpoints)

The quart parameter accepts values 0-4:

0 = Minimum value
1 = First quartile (Q1)
2 = Median (Q2)
3 = Third quartile (Q3)
4 = Maximum value

Here’s the basic syntax for extracting Q1 and Q3:

=QUARTILE(A2:A50, 1)    // Returns Q1
=QUARTILE(A2:A50, 3)    // Returns Q3

The difference between INC and EXC matters when precision counts. QUARTILE.INC uses linear interpolation that includes the minimum and maximum as the 0th and 100th percentiles. QUARTILE.EXC excludes these endpoints, which some statisticians prefer because it treats quartiles as strictly between the extremes. For most business applications, QUARTILE.INC (or the plain QUARTILE function) works fine.

Method 1: Manual IQR Calculation

The most straightforward approach calculates IQR in a single cell by subtracting Q1 from Q3:

=QUARTILE(A2:A50, 3) - QUARTILE(A2:A50, 1)

This formula does everything in one step. Google Sheets calculates Q3, calculates Q1, then subtracts. You get your IQR immediately.

For better visibility into your calculations, break it into separate cells:

Cell B2: =QUARTILE(A2:A50, 1)    // Q1
Cell B3: =QUARTILE(A2:A50, 3)    // Q3
Cell B4: =B3 - B2                // IQR

This approach makes debugging easier and lets stakeholders see each component. When someone asks “how did you get that number?”, you can point to the individual calculations.

Method 2: Using Named Ranges for Cleaner Formulas

When you reference the same data range repeatedly, named ranges make your formulas readable and maintainable. If your data range changes, you update it once instead of hunting through every formula.

To create a named range in Google Sheets:

Select your data range (e.g., A2:A50)
Click Data → Named ranges
Enter a name like SalesData and click Done

Now your formulas become self-documenting:

=QUARTILE(SalesData, 1)                           // Q1
=QUARTILE(SalesData, 3)                           // Q3
=QUARTILE(SalesData, 3) - QUARTILE(SalesData, 1)  // IQR

Anyone reading your spreadsheet immediately understands what data you’re analyzing. This matters when you revisit the sheet six months later or hand it off to a colleague.

For complex analysis sheets, I create named ranges for the calculated values too:

Named range "Q1": =QUARTILE(SalesData, 1)
Named range "Q3": =QUARTILE(SalesData, 3)
Named range "IQR": =Q3 - Q1

Then outlier formulas become readable English: =Q1 - (1.5 * IQR)

Detecting Outliers with IQR

The 1.5×IQR rule is the standard method for identifying outliers. It creates lower and upper boundaries—any value outside these bounds is flagged as an outlier.

Calculate the boundaries with these formulas:

Lower Bound: =QUARTILE(A2:A50, 1) - (1.5 * (QUARTILE(A2:A50, 3) - QUARTILE(A2:A50, 1)))
Upper Bound: =QUARTILE(A2:A50, 3) + (1.5 * (QUARTILE(A2:A50, 3) - QUARTILE(A2:A50, 1)))

With named ranges, this simplifies dramatically:

Lower Bound: =Q1 - (1.5 * IQR)
Upper Bound: =Q3 + (1.5 * IQR)

To highlight outliers automatically, apply conditional formatting:

Select your data range (A2:A50)
Click Format → Conditional formatting
Under “Format rules,” select Custom formula is
Enter: =OR(A2 < $D$2, A2 > $D$3) (where D2 is your lower bound and D3 is your upper bound)
Choose a highlight color (red works well for outliers)
Click Done

To count outliers, use COUNTIF with your bounds:

=COUNTIF(A2:A50, "<" & LowerBound) + COUNTIF(A2:A50, ">" & UpperBound)

Or with a single SUMPRODUCT for elegance:

=SUMPRODUCT((A2:A50 < LowerBound) + (A2:A50 > UpperBound))

Practical Example: Complete Walkthrough

Let’s build a complete IQR analysis for a dataset of product prices. Assume your raw data lives in column A with a header in A1.

Set up your analysis panel in columns D and E:

D1: "Metric"          E1: "Value"
D2: "Count"           E2: =COUNT(A2:A100)
D3: "Minimum"         E3: =MIN(A2:A100)
D4: "Q1"              E4: =QUARTILE(A2:A100, 1)
D5: "Median"          E5: =QUARTILE(A2:A100, 2)
D6: "Q3"              E6: =QUARTILE(A2:A100, 3)
D7: "Maximum"         E7: =MAX(A2:A100)
D8: "IQR"             E8: =E6 - E4
D9: "Lower Bound"     E9: =E4 - (1.5 * E8)
D10: "Upper Bound"    E10: =E6 + (1.5 * E8)
D11: "Outlier Count"  E11: =SUMPRODUCT((A2:A100 < E9) + (A2:A100 > E10))

This layout gives you a complete statistical summary at a glance. The formulas reference the calculated cells above them, so changing your data range in E2-E7 automatically updates everything downstream.

For identifying which specific values are outliers, add a helper column next to your data:

B1: "Outlier?"
B2: =IF(OR(A2 < $E$9, A2 > $E$10), "Yes", "No")

Drag this formula down to match your data. Now you can filter column B to show only outliers, or use COUNTIF to verify your outlier count matches E11.

Tips and Troubleshooting

Real-world data is messy. Here’s how to handle common problems.

Blank cells and errors: Wrap your quartile functions in IFERROR to handle edge cases gracefully:

=IFERROR(QUARTILE(A2:A50, 1), "Insufficient data")

The QUARTILE function needs at least one numeric value to work. If your range is empty or contains only text, it throws an error. IFERROR catches this and displays a friendly message instead.

Handling mixed data: If your range contains text mixed with numbers, QUARTILE ignores the text automatically. But blank cells can cause confusion—they’re ignored too, which might not match your expectations. Use COUNTA to verify how many values you’re actually analyzing:

=COUNTA(A2:A50)  // Counts non-empty cells
=COUNT(A2:A50)   // Counts only numeric cells

If these numbers differ significantly, you have non-numeric data in your range.

QUARTILE.INC vs. QUARTILE.EXC: Use QUARTILE.INC (or plain QUARTILE) for most business applications. It’s the Excel-compatible default and works intuitively for typical datasets. Switch to QUARTILE.EXC when you’re doing formal statistical analysis that requires the exclusive interpolation method, or when matching output from statistical software like R that uses exclusive quartiles by default.

The practical difference is usually small. For a dataset of 100 values, the IQR might differ by 1-2% between methods. But for small datasets (under 20 values), the difference grows more pronounced. Pick one method and use it consistently throughout your analysis.

Dynamic ranges: If your data grows over time, use a dynamic range formula instead of fixed references:

=QUARTILE(FILTER(A:A, A:A<>""), 3) - QUARTILE(FILTER(A:A, A:A<>""), 1)

This calculates IQR for all non-empty values in column A, automatically including new data as you add it.

IQR analysis in Google Sheets takes five minutes to set up and provides lasting value for understanding your data’s distribution. Build the analysis panel once, and you’ll have instant insight into spread and outliers for any dataset you throw at it.