Quartile 1 and 3 in Excel: Find Q1 and Q3 Correctly

To find the first and third quartiles in Excel, use QUARTILE.INC with 1 for Q1 and 3 for Q3. For example, if your data is in A2:A11, Q1 is the 25th percentile and Q3 is the 75th percentile. These formulas work in Excel for Microsoft 365 on Windows, and also in current Excel for Mac and Excel for the web.

Quartiles divide an ordered set of numbers into four parts. Q1 marks the point below which about 25% of the values fall; Q3 marks the point below which about 75% fall. They are useful for summarizing a dataset, comparing groups, and finding the interquartile range, which is Q3 minus Q1.

What You’ll Need

A range of numeric data in an Excel worksheet. Put one observation in each cell of a single column or row.
A blank cell for each result, so the formulas do not overwrite your source data.
A decision about which quartile convention to use. QUARTILE.INC is the usual choice for general summaries and is the default most readers expect; use QUARTILE.EXC only when your method or assignment calls for the exclusive convention.

Step-by-Step

Enter and check your data. Put the values you want to analyze in one continuous range, such as A2:A11, with a heading in A1 if useful. Each observation should be a number. You do not need to sort the values first: Excel orders the numeric values internally when it calculates quartiles. Remove any values that do not belong in the analysis, such as a subtotal or a note accidentally included in the selected range.
Choose the quartile method. For the inclusive method, Excel calculates quartiles using a rank that includes the endpoints of the data distribution. This is the common choice for descriptive statistics and is generally appropriate when you want the 25th and 75th percentile values. The exclusive method uses a different rank calculation and can give different answers, especially for small datasets. If a textbook, report, or workplace procedure specifies a method, follow that specification rather than mixing conventions.
Select a blank cell for Q1. Click a cell outside your data, such as C2, and type a formula using the actual range in your sheet. With the values in A2:A11, enter =QUARTILE.INC(A2:A11,1). Press Enter. The second argument, 1, tells Excel to return the first quartile.
Calculate Q3 in another blank cell. With the values still in A2:A11, click C3 and enter =QUARTILE.INC(A2:A11,3), then press Enter. The argument 3 requests the third quartile. You can label B2 as Q1 and B3 as Q3, or place the labels beside the results, to make the output clear.
Check the results with a worked example. Suppose A2:A11 contains these ten values, in any order: 4, 7, 9, 10, 12, 15, 18, 21, 24, and 30. With that exact range, =QUARTILE.INC(A2:A11,1) returns 9.25, and =QUARTILE.INC(A2:A11,3) returns 20.25. For the inclusive method, the Q1 rank is 1 + (10 − 1) × 0.25 = 3.25, so Excel interpolates one-quarter of the way from the third value, 9, to the fourth value, 10. That gives 9.25. The Q3 rank is 1 + (10 − 1) × 0.75 = 7.75, between 18 and 21; three-quarters of the way from 18 to 21 gives 20.25. A quartile does not have to be one of the original observations.
Use the exclusive method only if needed. With the same ten values in A2:A11, enter =QUARTILE.EXC(A2:A11,1) for Q1 or =QUARTILE.EXC(A2:A11,3) for Q3. These return 8.5 and 21.75, respectively. The values differ from the inclusive answers because the rank formula is different. The exclusive method may return an error when the dataset is too small for the requested quartile rank, so it is not always interchangeable with the inclusive method.
Label and format the answers. Add labels such as Q1 (25th percentile) and Q3 (75th percentile) near the output cells. If your data has decimal places, format the results to a suitable number of decimal places using Home > Number. Formatting changes how a result appears, not the underlying value used in later calculations.
Optionally calculate the interquartile range. The interquartile range (IQR) is the distance between Q3 and Q1. With Q1 in C2 and Q3 in C3, enter =C3-C2 in another blank cell. Using the example above, the result is 20.25 − 9.25 = 11. This describes the spread of the middle half of the values; it is not another quartile.

The formulas use the standard English function names and comma separators shown in Excel for Microsoft 365 on Windows. The same functions are available in current Excel for Mac and Excel for the web. Depending on your regional settings, Excel may display or require semicolons between arguments instead of commas; use the separator your installation expects if it rejects a formula.

Common Mistakes

Choosing the wrong method. QUARTILE.INC and QUARTILE.EXC can produce different results. Record which method you used, particularly when sharing results or comparing them with another report.
Using the wrong quartile number. The second argument is 1 for Q1 and 3 for Q3. Using 2 returns the median, not Q3.
Including extra cells. A heading is usually ignored when it is text, but a numeric ID, subtotal, or unrelated value in the selected range will affect the result. Check the range shown in the formula bar.
Assuming the answer must appear in the data. With an even-sized dataset, Excel may interpolate between observations and return a decimal value that is not present in the source range. That can be correct.
Overlooking numbers stored as text. Values that look numeric may not be recognized as numbers. If the result seems wrong, check the cells’ contents and convert text-formatted numbers to actual numbers before recalculating.

Tips

Keep the source data and outputs in separate areas. This makes it easier to review the range and reduces the chance of replacing an observation with a formula.
Use clear labels that identify the convention, such as “Q1 (INC)” and “Q3 (INC),” when the distinction could matter to someone reading the workbook.
If you add new observations regularly, use an Excel Table for the source data. A table expands as you add rows, helping keep the data range current. Check that the formula includes all intended rows after adding data.
For a quick visual summary, select the data and choose Insert > Insert Statistic Chart > Box and Whisker, if that chart is available in your Excel version. The chart’s quartile convention and presentation may not match a separately calculated result in every context, so consult its settings and document requirements before comparing values.
Use the same method and data-cleaning rules across groups. A comparison is misleading if one group includes missing values or uses a different quartile convention.

Troubleshooting

The formula returns an error. Check that the function name is spelled correctly, the quartile argument is 1 or 3, and the range address exists. If Excel rejects commas, replace them with the argument separator used by your regional settings. QUARTILE.EXC can return an error for a dataset that is too small for its exclusive rank; try the inclusive method only if that is acceptable for your analysis.

The result is unexpectedly high or low. Inspect the selected cells for an accidentally included value, such as a total, date serial, or numeric code. Confirm that all intended observations are in the range and that numbers stored as text have been converted. Sorting the range is not required, but scanning it can help reveal outliers or data-entry mistakes.

The result shows too few decimal places. The stored result may be more precise than the displayed value. Select the result cell and use Home > Increase Decimal or Home > Number to adjust its display. Do not round the result unless your reporting rules require it; rounding can affect later calculations such as the IQR.

The formula does not update after editing data. Confirm that the edited cell lies inside the referenced range. If calculation is set to manual, use Formulas > Calculate Now to recalculate the workbook, or change calculation options to Automatic. Also check that the formula refers to the correct worksheet if the source data is elsewhere.

Frequently Asked Questions

What is the difference between Q1 and Q3?
Q1 is the first quartile, or 25th percentile; Q3 is the third quartile, or 75th percentile. Roughly one-quarter of the observations lie at or below Q1, and roughly three-quarters lie at or below Q3, subject to the chosen calculation method and ties.

Should I use QUARTILE.INC or QUARTILE.EXC?
Use QUARTILE.INC for a general-purpose quartile calculation unless your instructions specify otherwise. Use QUARTILE.EXC when you need the exclusive convention for a particular statistical procedure. State the method so readers can reproduce the result.

Do I need to sort the data before using the formula?
No. Excel evaluates the numeric values in the referenced range without requiring you to sort them. Sorting can make the data easier to inspect, but it does not need to happen before the formula works.

Why is Q1 or Q3 not one of my data values?
Excel may interpolate between two ordered observations to calculate a quartile. In the ten-value example, inclusive Q1 is 9.25, between 9 and 10. That is expected and does not mean the formula is broken.

Can I calculate quartiles when some cells are blank?
Blank cells in a referenced range are ignored, so the calculation uses the numeric values present. Still, check whether blanks represent missing observations and whether your analysis requires a specific way to handle missing data. Text entries and errors should also be reviewed rather than assumed to represent valid numeric observations.

Can I use the same formula in Excel for Mac or the web?
Yes. Current Excel for Mac and Excel for the web support QUARTILE.INC and QUARTILE.EXC. The main difference you may encounter is the argument separator required by your regional settings; the calculations themselves use the same quartile methods.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.