In Excel, calculate the lower quartile (Q1) with =QUARTILE.INC(A2:A9,1), replacing A2:A9 with your data range. This returns the 25th percentile using Excel’s inclusive quartile method; if your assignment or analysis specifies the exclusive method, use =QUARTILE.EXC(A2:A9,1) instead.
The lower quartile formula and what it returns
The lower quartile is the value that marks the first quarter of an ordered data set. It is also called Q1 or the 25th percentile. In Excel, the main function for calculating it is QUARTILE.INC:
=QUARTILE.INC(array,1)returns the first quartile.=QUARTILE.INC(array,2)returns the median, or second quartile.=QUARTILE.INC(array,3)returns the third quartile.
The first argument is the range or array containing the values. The second argument, often called k, identifies which quartile to return: 0 is the minimum, 1 is Q1, 2 is the median, 3 is Q3, and 4 is the maximum. For a lower quartile, use 1—not 0.25. The value 0.25 is used with a percentile function, not as the quartile number in QUARTILE.INC.
For a percentile-style alternative, with the numbers in A2:A9, enter =PERCENTILE.INC(A2:A9,0.25). This requests the 25th percentile directly and gives the same result as QUARTILE.INC with k set to 1. These functions are available in Microsoft Excel for Microsoft 365 on Windows. Excel also retains the older QUARTILE function for compatibility, but the explicit INC and EXC names make the calculation method clear.
Worked example with eight numbers
Suppose cells A2:A9 contain 4, 7, 9, 12, 15, 18, 21, and 24. With those values in A2:A9, enter =QUARTILE.INC(A2:A9,1) in another cell. The result is 8.5.
Here is why: the inclusive method locates the 25th percentile at rank 1 + (n − 1) × 0.25, where n is the number of observations. With eight values, the rank is 1 + 7 × 0.25 = 2.75. That is three-quarters of the way from the second sorted value, 7, to the third, 9. The interpolation is 7 + 0.75 × (9 − 7) = 8.5. The example is already in ascending order to make the arithmetic easy to follow, but Excel does not require the cells to be sorted.
Q1 does not have to be one of the values in the data. In this example, 8.5 is between two observations. That is normal: Excel interpolates between values when the calculated position falls between ranks. With repeated values or a different number of observations, the result may land on an existing value or be interpolated between different neighbors.
Inclusive and exclusive quartiles
Excel provides two quartile methods because statistical references and software do not all define quartile positions in exactly the same way. QUARTILE.INC includes the endpoints of the percentile scale. QUARTILE.EXC excludes them. For many ordinary spreadsheet summaries, the inclusive method is a suitable default, but a required method from a course, report, or organization should take priority.
Using the same example values in A2:A9, the inclusive formula =QUARTILE.INC(A2:A9,1) returns 8.5. With those same cells, the exclusive formula =QUARTILE.EXC(A2:A9,1) returns 7.5. The difference comes from the rank rule: the exclusive method uses (n + 1) × 0.25, which gives rank 2.25 for eight observations. That is one-quarter of the way from 7 to 9, so the result is 7.5.
Neither method is universally the one correct definition for every context. If you are reproducing a result from another program, textbook, or published procedure, check its quartile convention and match it. If instructions simply ask for a lower quartile in Excel and do not name a method, QUARTILE.INC is an understandable choice because the method is explicit and it is the inclusive default used by many spreadsheet users.
How to enter the formula in Excel
- Put the numeric observations in a single column or row. For example, use A2:A100 and keep a heading, if any, in A1.
- Select an empty cell where the result should appear.
- Type
=QUARTILE.INC(A2:A100,1), adjusting the range to cover the data, then press Enter. - To calculate Q3 as well, use the same range with k set to 3:
=QUARTILE.INC(A2:A100,3).
For a formula you will copy elsewhere, consider using an absolute reference so the data range does not shift. With the values in A2:A100, the formula =QUARTILE.INC($A$2:$A$100,1) keeps that range fixed when copied. You can also select the cells with the mouse while building the formula instead of typing the address. Excel will insert the selected range into the formula.
Sorting the source column is optional. QUARTILE.INC calculates from the values in the range and handles the ordering internally; sorting may help you inspect the data, but it is not a prerequisite and changes the visible order of your source records.
Which cells Excel includes
When a function refers to a worksheet range, blank cells and text entries are not treated as numeric observations for this calculation. Numeric values are included, including zero. Dates and times stored as Excel numbers are also numeric, so they can be used, although the resulting quartile will be a date or time serial value displayed according to the result cell’s number format.
This matters when a column mixes measurements with labels such as “missing” or “not recorded.” Text labels in the referenced cells are ignored, but they are not a substitute for deciding how missing observations should be handled in your analysis. Check whether blanks mean genuinely absent data, a value of zero, or a value that still needs to be collected. Treating a missing measurement as zero can change Q1 substantially.
Errors in the selected range can cause the quartile calculation to return an error. If the formula shows an error, inspect the source cells as well as the formula’s range. Make sure the range includes the intended numeric observations and does not accidentally include unrelated formulas or error values. Formatting a cell to look numeric does not always make text into a true number; if values were imported as text, convert them to numbers before relying on the result.
Filtered rows, tables, and changing data
QUARTILE.INC calculates from the values in its referenced range; it does not automatically restrict the calculation to rows currently visible after you apply a filter or hide rows. As a result, hiding or filtering observations does not by itself produce a quartile for only the visible records. If the intended analysis is limited to a subgroup, create a range containing that subgroup or use an approach designed to select the relevant records, then calculate the quartile over those selected values.
For a manually prepared subset, copy the included observations to a separate area and point QUARTILE.INC at that area. This straightforward option is often easier to audit than a complex formula, especially when the selection needs to be reviewed by someone else. In a frequently updated workbook, an Excel Table can also make ranges easier to maintain: when rows are added to a table, structured references can expand with the table. Check that the formula refers to the intended table column and that the selected records match the question you are answering.
If data contain outliers, Q1 is still calculated from the ordered observations, but it is less directly affected by extreme high or low values than the mean because it identifies a position in the distribution rather than averaging every observation. It is not immune to changes in the data: adding observations can alter the ranks and therefore change Q1. Keep the population being summarized consistent when comparing quartiles across groups or time periods.
Common mistakes and how to avoid them
- Using 0.25 as the quartile number: In
=QUARTILE.INC(A2:A9,1), the second argument is 1 for Q1. Use 0.25 only with a percentile function, as in=PERCENTILE.INC(A2:A9,0.25). - Choosing the wrong method: QUARTILE.INC and QUARTILE.EXC can return different results. Match the method specified by your source or instructions rather than assuming the numbers must agree.
- Including a heading or the wrong cells: Check the formula’s range in the formula bar. A text heading in a referenced range is ignored, but an overly broad range can include unrelated numeric data.
- Assuming hidden records are excluded: Filtering a list does not make QUARTILE.INC calculate only on visible cells. Build or select the data set that should actually be analyzed.
- Interpreting Q1 as a guaranteed cutoff for exactly 25% of rows: Ties, interpolation, and the chosen quartile convention affect how the result relates to the observed values. Q1 is a percentile summary, not necessarily a value that divides the records into exact groups of equal size.
How to interpret Q1 alongside other statistics
Q1 is most informative when read with the median and Q3. The interquartile range, or IQR, is Q3 minus Q1 and describes the spread of the middle half of the data. With the eight example values in A2:A9, =QUARTILE.INC(A2:A9,3) returns 19.5; together with Q1 of 8.5, the IQR is 11. If Q1 and Q3 are far apart, the middle half of the observations is more spread out. If they are close, that central portion is more concentrated.
A quartile alone does not describe the entire distribution. Two data sets can share the same Q1 but differ in their medians, upper tails, or extreme values. For a fuller summary, consider reporting the minimum, Q1, median, Q3, and maximum, while explaining which quartile method was used. A box-and-whisker chart can show these statistics visually, but confirm the chart’s quartile settings if exact agreement with a formula is important.
Frequently Asked Questions
What is the Excel formula for the lower quartile?
Use =QUARTILE.INC(range,1), replacing range with the cells containing the data. For example, with values in A2:A100, use =QUARTILE.INC(A2:A100,1).
Is Q1 the same as the 25th percentile?
Q1 is another name for the first quartile, commonly described as the 25th percentile. Excel’s QUARTILE.INC method corresponds to PERCENTILE.INC with a percentile argument of 0.25. Different methods can give different numerical results for a finite data set.
Do I need to sort the data first?
No. QUARTILE.INC calculates the quartile without requiring the worksheet cells to be sorted. Sorting can make the observations easier to inspect, but it is optional.
Why do QUARTILE.INC and QUARTILE.EXC give different answers?
They use different rules for locating percentile positions. The inclusive method includes the endpoints of the percentile scale, while the exclusive method excludes them. The difference is especially noticeable with smaller data sets, so match the method required by your instructions.
Does the formula ignore filtered-out rows?
No. A normal range reference includes values in hidden or filtered rows. To calculate Q1 for only a selected subgroup, make a range or data set containing just that subgroup and use that as the formula’s input.
What if QUARTILE.INC returns an error?
Check that the formula has a valid range and that the range contains usable numeric observations. Look for error values in the source cells, imported numbers stored as text, or a range that misses the data. Also confirm the quartile argument is between 0 and 4; use 1 for Q1.
Related
- Excel: Sum Values Based on Other Values
- Excel SUM Visible Rows: Add Filtered Data Correctly
- Excel Sum Text? What SUM Does With Text Cells
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for Difference Between Two Numbers ExplainedTo find the difference between two numbers in Excel, subtract one cell from the other: if the first…
- Round Excel Cell Values: Formulas and Display OptionsTo round a number in an Excel cell and change the value used in calculations, use the ROUND…
- How to Add Single Quotes in an Excel Formula CorrectlyIf A2 contains North and you want the result to display as ‘North’, enter this formula in B2:…
- Excel INDIRECT Function: How to Use It with Cell ReferencesTo make Excel interpret text as a cell reference, use INDIRECT. With “B3” in A1 and 42 in…
- Excel Unique Dropdown List: Create One That UpdatesTo create a unique dropdown list in Excel for Microsoft 365, generate a deduplicated list with UNIQUE, then…
- Excel Formula for File Name: Workbook, Sheet, and PathTo return the current workbook’s file name in Excel, use =TEXTBEFORE(TEXTAFTER(CELL("filename",A1),"["),"]") in Microsoft 365 or Excel 2024. The…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.