How to Find Quartiles in Excel: Functions, Formulas, and What Affects Your Results

Quartiles divide a dataset into four equal parts, giving you a clearer picture of how your data is distributed. Whether you're analyzing sales figures, student scores, survey responses, or financial data, Excel makes it straightforward to calculate quartiles — but knowing which function to use, and why your results might differ from another tool or colleague's spreadsheet, matters more than most people realize.

What a Quartile Actually Tells You

Before diving into Excel mechanics, it's worth being clear on what you're calculating:

• Q1 (First Quartile): The value below which 25% of your data falls
• Q2 (Second Quartile): The median — the midpoint of your dataset
• Q3 (Third Quartile): The value below which 75% of your data falls
• IQR (Interquartile Range): Q3 minus Q1, representing the middle 50% of your data

Quartiles are especially useful for spotting outliers, understanding spread, and comparing distributions across groups.

The Two Excel Quartile Functions

Excel offers two quartile functions, and the difference between them is frequently misunderstood.

QUARTILE.INC — Inclusive Method

=QUARTILE.INC(array, quart) 

This is the default choice for most users. The .INC stands for inclusive, meaning the minimum (0) and maximum (4) values of the dataset are included in the calculation range.

Example: If your data is in cells A2 through A50, the formula for Q1 would be:

=QUARTILE.INC(A2:A50, 1) 

QUARTILE.EXC — Exclusive Method

=QUARTILE.EXC(array, quart) 

The .EXC version excludes the minimum and maximum from the interpolation calculation. This method is less common but aligns with certain statistical standards used in academic research and some financial analysis contexts.

Key difference: On the same dataset, QUARTILE.EXC and QUARTILE.INC will often return slightly different values — neither is "wrong." They reflect different statistical methodologies for handling boundary values.

📊 If you're working in a professional or academic context where a specific quartile method is required, confirm which standard your field or institution uses before choosing a function.

The Legacy QUARTILE Function

Older Excel spreadsheets may use simply =QUARTILE(array, quart) without a suffix. This function still works and behaves identically to QUARTILE.INC. Microsoft retained it for backward compatibility, but the .INC version is the current recommended syntax.

Step-by-Step: Calculating All Four Quartiles

If your data sits in column A (rows 2 through 100), here's a clean setup:

For the IQR, you can also write it directly:

=QUARTILE.INC(A2:A100, 3) - QUARTILE.INC(A2:A100, 1) 

Using Quartiles to Flag Outliers

A common application is identifying outliers using the 1.5 × IQR rule. Values below Q1 - 1.5×IQR or above Q3 + 1.5×IQR are considered potential outliers.

In Excel, you can use this logic inside an IF statement or conditional formatting to highlight those data points automatically — useful in datasets with hundreds or thousands of rows.

What Affects Your Quartile Results 🔍

Several variables mean that two people working with "the same data" can get different quartile results:

• Function choice:QUARTILE.INC vs. QUARTILE.EXC will produce different outputs, especially on small datasets
• Dataset size: On very small datasets (fewer than 10–12 values), quartile calculations can be heavily influenced by individual data points, and results vary more noticeably between methods
• Data type consistency: Mixed data types — cells that look like numbers but are stored as text — will be silently excluded from calculations, skewing results without any error message
• Blank cells vs. zero values: Excel ignores blank cells in quartile calculations but treats zero as a valid data point; confusing the two is a common source of unexpected results
• Excel version: Older versions pre-2010 only have the legacy QUARTILE function; the .INC and .EXC variants were introduced in Excel 2010

Quartile Functions Across Different Excel Environments

The functions behave consistently across Excel for Windows, Excel for Mac, and Excel Online, assuming you're on a version that supports .INC and .EXC. However, if you're collaborating across platforms and someone opens the file in Google Sheets, be aware that Sheets uses QUARTILE (equivalent to .INC) by default, and results may differ slightly due to differences in interpolation logic on certain dataset sizes.

LibreOffice Calc also supports quartile functions but handles some edge cases differently — worth double-checking if your workflow spans multiple spreadsheet applications.

Choosing Between Methods Depends on Your Context

For everyday data analysis — tracking expenses, comparing scores, reviewing survey data — QUARTILE.INC is the practical default. It's what most Excel users expect, and it matches how many statistics courses teach quartiles.

For formal statistical reporting, financial risk modeling, or situations where your output needs to match a specific software tool or industry standard, the choice between inclusive and exclusive methods becomes meaningful. The same dataset can yield noticeably different Q1 and Q3 values depending on which method is applied, especially when the dataset is small or the distribution is uneven.

Understanding which question you're answering with your quartile — and who will be reading or acting on that number — shapes which function actually fits your work.