Q1 and Q3 in Excel: A Formula for Finding Quartiles

To find the first and third quartiles in Excel, use QUARTILE.INC with quartile numbers 1 and 3. If your numeric data is in A2:A9, enter =QUARTILE.INC(A2:A9,1) for Q1 and =QUARTILE.INC(A2:A9,3) for Q3. These results mark the 25th and 75th percentiles using Excel’s inclusive method. Have your observations in one range, ideally with a header kept outside that range.

What You’ll Need

Excel for Microsoft 365 on Windows, or Excel for Mac or the web. The formulas below work in current versions of all three.
A range containing the numeric observations you want to analyze. The values do not have to be sorted first.
Two empty cells where you can display Q1 and Q3. A separate cell for each result makes them easy to label and reuse.

Step-by-Step

Arrange the data in a single column or row. For example, enter eight measurements in A2:A9 and put a descriptive heading such as Score in A1. A single range is easiest to select and check. You can leave blank cells within the data range, but avoid including unrelated numbers or totals that are not observations.
Choose the quartile convention. For most general summaries, use QUARTILE.INC, Excel’s inclusive quartile method. It includes the 0th and 100th percentiles as possible endpoints and is a common choice for reporting quartiles. Some textbooks, statistical packages, or workplace standards use an exclusive method instead. If you need to match another source, confirm its convention before calculating; Q1 and Q3 can differ slightly between methods.
Pick an output cell and calculate Q1. With values in A2:A9, select an empty cell such as C2, type =QUARTILE.INC(A2:A9,1), and press Enter. The 1 tells Excel to return the first quartile. In the example data 4, 7, 9, 12, 15, 18, 21, and 24, the result is 8.5. Excel interpolates between observations where the percentile falls between two values; it does not always simply select one item from the sorted list.
Calculate Q3 in a second cell. With the same values still in A2:A9, select C3 and enter =QUARTILE.INC(A2:A9,3). The 3 requests the third quartile. For the example data, this returns 18.75. Use the identical data range for both calculations so the pair describes the same set of observations.
Add labels so the results are clear. Type Q1 in B2 and Q3 in B3, or place the labels beside your output cells. The labels are not required for Excel to calculate anything, but they prevent confusion when you return to the worksheet or share it with someone else. You can also apply a number format that matches your data, such as one or two decimal places.
Check that the selected range contains the intended observations. Click each formula cell and inspect the range inside the parentheses. Make sure the first and last observations are included, and that a heading, subtotal, or unrelated number has not been included accidentally. Sorting is not necessary for the function, but sorting a copy of the values can help you visually confirm that the range is right.
Use the results in your summary. Q1 is a boundary below which approximately 25% of the observations fall, while Q3 is a boundary below which approximately 75% fall. The interval from Q1 to Q3 contains the middle half of the data under the selected quartile convention. You can calculate the interquartile range by subtracting Q1 from Q3, if that is useful for your analysis.

In the example, the observations are 4, 7, 9, 12, 15, 18, 21, and 24. The inclusive percentile positions are 2.75 for Q1 and 6.25 for Q3 when the sorted values are numbered from 1. Q1 is therefore 7 plus 75% of the difference between 9 and 7, giving 8.5. Q3 is 18 plus 25% of the difference between 21 and 18, giving 18.75. This interpolation explains why quartile answers can be decimals even when every original value is a whole number.

Common Mistakes

Including the heading in the range. If the heading is in A1, selecting A1:A9 instead of A2:A9 can lead to a misleading result or an error. Start the range at the first observation, not the column label.
Using the wrong quartile number. The second argument must be 1 for Q1 or 3 for Q3. Entering 2 returns the median, not Q1 or Q3. Check that the two formulas differ only in that final argument.
Mixing inclusive and exclusive methods. QUARTILE.INC and QUARTILE.EXC do not always return the same values. Do not compare results calculated with different methods as if they were interchangeable. The older QUARTILE function is retained for compatibility and corresponds to the inclusive method.
Assuming values must be sorted. Sorting is not required; Excel evaluates the numeric values in the range. Sorting can help with a manual check, but do not rearrange data in a way that breaks related rows or records.
Overlooking numbers stored as text or errors. A value that looks like a number may be stored as text and not be treated as a numeric observation. An error value in the range can also interfere with the calculation. Check the source cells if your result is unexpected.

Tips

Use a clear range reference. For a fixed dataset, a reference such as A2:A9 is straightforward. If you expect the list to grow, put the data in an Excel table and use its column reference in the formula. Table references expand as table rows are added, which helps prevent new observations from being left out.
Keep the method consistent across reports. If a team, course, or reporting template specifies a quartile definition, follow it even if another method is more familiar. Note the method in a nearby cell or report footnote when other people may need to reproduce the result.
Use formatting rather than changing the data. Set the output cells to the decimal places appropriate for your measurements. Formatting changes how a number appears, not the underlying calculated value, so avoid rounding source observations just to make the quartiles look tidier.
Remember what the quartiles do not say. Q1 and Q3 describe positions in an ordered distribution; they do not show the full shape of the data or explain why values vary. Pair them with context such as the sample size and a suitable chart when presenting a summary.

Troubleshooting

The formula returns an error. Check the spelling of the function, the range boundaries, and whether the selected cells contain errors. If your Excel regional settings use semicolons between function arguments, replace the comma in the formula with a semicolon. Do not change the comma used as a decimal mark in a value unless your locale requires it.

Q1 or Q3 seems wrong. Confirm that the range includes every intended number and excludes headers, totals, and unrelated data. Then check for numbers stored as text, hidden rows you meant to include or exclude, and an accidental mismatch between the Q1 and Q3 ranges. The function works with the values in its referenced range, regardless of whether rows are hidden.

Your answer does not match a manual calculation or another program. Compare the quartile convention first. A manual approach that splits the sorted list into lower and upper halves may use a different rule from Excel’s inclusive interpolation. The other program may also use an exclusive method or another percentile definition. To match it, identify the method it uses and select the corresponding Excel function where appropriate.

Excel shows the formula as text instead of calculating it. Change the cell’s number format from Text to General, then re-enter the formula. Also check that the entry begins with an equals sign and that Show Formulas has not been turned on for the worksheet. In Excel for Microsoft 365 on Windows, the Show Formulas command is on the Formulas tab; the same formula behavior applies in current Mac and web versions.

The result is a decimal even though your data uses whole numbers. This is normal when the requested percentile falls between two sorted observations. Excel interpolates to estimate the quartile. A decimal result is not, by itself, evidence of a bad range or incorrect formula.

Frequently Asked Questions

What is Q1 in Excel?

Q1 is the first quartile: a value at the 25th percentile under the method you choose. With QUARTILE.INC, Excel calculates it using the inclusive percentile convention. Roughly one-quarter of the observations lie at or below this boundary, with exact interpretation depending on ties and the data.

What is Q3 in Excel?

Q3 is the third quartile, or 75th percentile. It marks a boundary at or below which roughly three-quarters of the observations fall. In Excel, use the third-quartile argument with your chosen quartile function.

Should I use QUARTILE.INC or QUARTILE.EXC?

Use QUARTILE.INC when you want Excel’s inclusive method and do not have a required alternative. Use QUARTILE.EXC when you specifically need the exclusive convention used by a standard, course, or comparison source. Exclusive calculations can return a number error for small datasets, so make sure there are enough numeric observations for the requested quartile.

Do I need to sort my data before finding quartiles?

No. Both quartile functions calculate from the values in the selected range without requiring you to sort them. Sorting can make it easier to inspect a short list or explain the calculation, but it is optional.

Can I calculate Q1 and Q3 when some cells are blank?

Yes. Blank cells within a referenced range are ignored by the quartile function. However, make sure blanks are genuinely missing observations rather than zeros that should be included. A zero is a numeric value and affects the result.

Why does my quartile differ from the median of the lower or upper half?

That manual method divides the ordered observations into halves and finds a median within each half. Depending on the number of observations and the splitting rule, it may not match Excel’s inclusive percentile interpolation. Use one method consistently and state which one you used when the distinction matters.

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.