--- title: "QUARTILE in Google Sheets: How to Calculate Quartiles" canonical: "https://www.owox.com/blog/articles/quartile-function-google-sheets" updated: "2026-10-05" --- # QUARTILE function in Google Sheets: how to calculate quartiles, the IQR and outliers QUARTILE splits a dataset into four parts and returns one of the five boundary values: the minimum, the first quartile, the median, the third quartile or the maximum. This guide shows how to calculate quartiles in Google Sheets, how QUARTILE.INC and QUARTILE.EXC differ, how to get the interquartile range and flag outliers, how to calculate quartiles for a group, and what errors to expect. [Google Sheets Tips](/blog/topics/google-sheets-tips) · Updated October 2, 2026 · [Vadym Kramarenko](/team/vadym-kramarenko) · 6 min read ![QUARTILE function in Google Sheets: how to calculate quartiles, the IQR and outliers](https://cdn.owox.com/www/webflow/6a142f1b2c701ed0555fbd3b_USE-THE-QUARTILE-FUNCTION.png/public) To calculate a quartile in Google Sheets, give QUARTILE the data and the number of the quartile: > `=QUARTILE(C3:C12, 1)` The formula returns the first quartile of C3:C12, the value below which a quarter of the data falls. The second argument chooses which of five values to return: | Quartile number | Returns | The same as | | --- | --- | --- | | 0 | The minimum | `MIN(data)` | | 1 | The first quartile, Q1 | `PERCENTILE(data, 0.25)` | | 2 | The median, Q2 | `MEDIAN(data)` | | 3 | The third quartile, Q3 | `PERCENTILE(data, 0.75)` | | 4 | The maximum | `MAX(data)` | ## QUARTILE syntax > `=QUARTILE(data, quartile_number)` * **data:** the range or array of numbers. It does not have to be sorted. * **quartile\_number:** 0, 1, 2, 3 or 4. > `=QUARTILE(C3:C12, 1)` ![Google Sheets with ten monthly sales figures in C3:C12 and the formula =QUARTILE(C3:C12, 1) returning 36050](https://cdn.owox.com/www/webflow/6901f00012f7ed298fb79039_1.-Example-of-QUARTILE.png/public) All five values for one dataset, here eight sales figures: ![Google Sheets with eight sales figures and the results of QUARTILE for the numbers 0 to 4 in a column: 27300, 38150, 43900, 52000 and 60700](https://cdn.owox.com/www/webflow/6901f5b7a337b8ae16660c63_C3C10-4---4.5-Calculating-the-Median-Second-Quartile-with-QUARTILE.EXC.png/public) A quartile is usually not one of the values in the data. When its position falls between two values, the function interpolates: Q1 of the eight figures, 38,150, lies between 35,000 and 39,200. **QUARTILE.INC** is the same function under its full name; in our test both returned identical results. ## QUARTILE.EXC: the exclusive method > `=QUARTILE.EXC(data, quartile_number)` QUARTILE.EXC calculates the position of the quartile differently, in the way [PERCENTILE.EXC](/blog/articles/percentile-function-google-sheets) does. Q1 comes out lower and Q3 higher than with QUARTILE; the median is the same. > `=QUARTILE.EXC(C3:C12, 1)` ![Google Sheets with the same ten sales figures and the formula =QUARTILE.EXC(C3:C12, 1) returning 34475](https://cdn.owox.com/www/webflow/6901f06c2900e1360bd3ff1f_2.-Example-of-QUARTILE.EXC.png/public) On the ten figures of the screenshots, Q1 is 36,050 with QUARTILE and 34,475 with QUARTILE.EXC. QUARTILE.EXC accepts only the numbers 1, 2 and 3. With 0 or 4 it returned #NUM! in our test, since the minimum and the maximum are exactly what the exclusive method leaves out. Use QUARTILE by default, and QUARTILE.EXC when the method you follow asks for it. Keep to one of them within a report: an interquartile range from one and a quartile from the other do not belong together. ## The Turning Point OWOX Data Marts See your first report built in real time. _15 minutes._ 1. Connect your data warehouse 2. Pick your metrics 3. Get a live Google Sheets report In the time it takes to write a ticket. Then imagine never writing that ticket again. [Book a Demo](/book-a-call) We'll use your actual use case ## The interquartile range and outliers The interquartile range, IQR, is the distance between the third and the first quartile. It holds the middle half of the data: > `=QUARTILE(C3:C12, 3) - QUARTILE(C3:C12, 1)` A common rule treats values that lie more than 1.5 IQR beyond the quartiles as outliers. The two limits: > `=QUARTILE(C3:C12, 1) - 1.5 * (QUARTILE(C3:C12, 3) - QUARTILE(C3:C12, 1))` > `=QUARTILE(C3:C12, 3) + 1.5 * (QUARTILE(C3:C12, 3) - QUARTILE(C3:C12, 1))` In our test data of ten amounts, Q1 was 1,962.5 and Q3 2,750, so the IQR was 787.5 and the upper limit 3,931.25. One amount, 4,500, was above it. To list such rows, pass the limit to [FILTER](/blog/articles/google-sheets-filter-function); to count them, to [COUNTIF](/blog/articles/countif-and-countifs-google-sheets). ## Label each row with its quartile To say which quarter each value belongs to, compare it with the three quartiles: > `=ARRAYFORMULA(IFS(D3:D12 <= QUARTILE(D3:D12, 1), "Q1", D3:D12 <= QUARTILE(D3:D12, 2), "Q2", D3:D12 <= QUARTILE(D3:D12, 3), "Q3", TRUE, "Q4"))` One formula fills the column with Q1 to Q4. [IFS](/blog/articles/ifs-function-google-sheets) checks the conditions in order, so each value gets the first quarter it fits into. ## Quartiles for a group There is no QUARTILEIF. Filter the data inside the formula instead: > `=QUARTILE(FILTER(D3:D11, B3:B11 = "North"), 3)` The older pattern does the same with IF inside ARRAYFORMULA, here with PERCENTILE and the region taken from a cell: > `=ArrayFormula(PERCENTILE(IF($B$3:$B$11=$G4, $D$3:$D$11), 0.75))` ![Google Sheets with regions, sales reps and amounts and a formula with ARRAYFORMULA, PERCENTILE and IF returning 58150, the 75th percentile of the North region](https://cdn.owox.com/www/webflow/6901f6de5116528833e37ebb_6.-Using-PERCENTILE-with-IF-Function-1.png/public) The condition can be on the numbers themselves. The third quartile of the sales between 30,000 and 55,000: > `=ArrayFormula(QUARTILE(IF((C3:C11>=30000)*(C3:C11<=55000), C3:C11), 3))` ![Google Sheets with nine sales figures and a formula with ARRAYFORMULA, QUARTILE and IF returning 44600, the third quartile of the values between 30000 and 55000](https://cdn.owox.com/www/webflow/6901f70c57dac7e76631c4ef_7.-Using-QUARTILE-with-IF-Function-2.png/public) Multiplying the two conditions means “and”: a row counts only if both are true. ## Errors in QUARTILE **⚠️ Error:** #NUM! with a quartile number above 4 or below 0. ✅ **Solution:** Use 0 to 4. A decimal is cut to its whole part: in our test 1.9 was read as 1. **⚠️ Error:** #NUM! in QUARTILE.EXC with 0 or 4. ✅ **Solution:** The exclusive method has no minimum and maximum. Use MIN and MAX, or QUARTILE. **⚠️ Error:** #NUM! in QUARTILE.EXC on a very small dataset. ✅ **Solution:** The exclusive method needs enough values: with two numbers, Q1 returned #NUM! in our test. Use QUARTILE. **⚠️ Error:** #NUM! on a range that looks full. ✅ **Solution:** The range has no numbers. Text is ignored, including numbers stored as text. Convert them with VALUE. **⚠️ Error:** The quartiles differ from another tool. ✅ **Solution:** There are several accepted ways to calculate quartiles. Compare QUARTILE.INC and QUARTILE.EXC; one of them usually matches. ## Related Google Sheets functions * [PERCENTILE](/blog/articles/percentile-function-google-sheets): Any share of the data, not only quarters. * [PERCENTRANK](/blog/articles/percentrank-function-google-sheets): Where a value stands in the data. * [MAX, MIN and MEDIAN](/blog/articles/max-min-median-functions-google-sheets): The ends and the middle. * [AVERAGE functions](/blog/articles/average-functions-google-sheets): The mean, to compare with the median. * [RANK](/blog/articles/rank-function-google-sheets): The position of each value. * [Exploratory data analysis](/blog/articles/exploratory-data-analysis-google-sheets): Quartiles as part of a first look at data. ## Quartiles describe the rows in the range, whatever they are Quartiles and outlier limits are computed from every value in the range, so they shift with what the range contains. A partial export, a file cut off at a row limit or a copy from last month each give different quartiles, and a row flagged as an outlier today is ordinary once the missing rows are added. The [OWOX Data Marts extension for Google Sheets](https://workspace.google.com/marketplace/app/owox_data_marts/94902851409) covers that half. A data analyst publishes a data mart (a table or an SQL query in your data warehouse) and makes it available for reports. In Sheets you open the extension, pick the data mart, tick the columns you want and run it. The rows land in a tab, ready for your formulas. ![Google Sheets with the OWOX Data Marts extension open beside an ad spend table: the Unified Ad Spend data mart, nine of fourteen columns ticked and a Save & Run button – the rows in the sheet come from a governed data mart, not a pasted export](https://cdn.owox.com/www/content-marketing-article/quartile-function-google-sheets/screens/1.png/public) What changes for the person who maintains the sheet: * **Refresh replaces re-export.** _Refresh current report_ in the extension menu re-runs the same pull, and a scheduled refresh keeps the tab current without anyone touching it. * **The source is written on the sheet.** The header cell of an imported tab carries a note with the data mart it came from, the time of the import and a link to the report. * **No SQL and no warehouse access needed.** The analyst decides which data marts and columns are available. You choose from that list. The extension does not write or change the analyst’s SQL. QUARTILE in the sheet then runs on the complete, current rows the query returns. The extension comes with the OWOX Cloud plans of [OWOX Data Marts](https://www.owox.com/app-signup); [spreadsheet reporting](/features/spreadsheet-reporting) describes both ways data reaches a sheet. ## Your New Normal Turn your data into decisions. Governed data marts give you the clean foundation ML needs to actually work. * No AI hallucinations * Analyst-governed definitions * Every number traces to SQL [Get started free](https://www.owox.com/app-signup) ## FAQ ## Frequently Asked Questions Is There a QUARTILEIF Google Sheets Function? No, there is no QUARTILEIF function. Filter the data inside the formula: =QUARTILE(FILTER(D3:D11, B3:B11 = "North"), 3) returns the third quartile of the rows where column B is North. What Is the Quartile Function in Google Sheets? The QUARTILE function in Google Sheets calculates quartiles of a dataset. It returns values representing Q1 (25th percentile), Q2 (median), and Q3 (75th percentile) for easier analysis of data distribution. How Do You Find Q1 and Q3 on Sheets? Use =QUARTILE(range, 1) for Q1 and =QUARTILE(range, 3) for Q3. QUARTILE.INC is the same function. QUARTILE.EXC uses the exclusive method and returns a lower Q1 and a higher Q3. How Do You Find and Calculate the Quartiles in Google Sheets? To calculate quartiles in Google Sheets, use QUARTILE.INC or QUARTILE.EXC functions. The formula is =QUARTILE.INC(range, quartile\_number) or =QUARTILE.EXC(range, quartile\_number) for Q1 (25th percentile), Q2 (50th percentile/median), or Q3 (75th percentile). What Do Percentiles Mean When Talking About Quartiles? Quartiles divide data into four equal parts, which align with percentiles in 25% increments. The first quartile is the 25th percentile, the second (median) is the 50th percentile, and the third quartile is the 75th percentile, showing the spread of data in sections. How can I use the QUARTILE function with an array of values? Type the numbers in curly braces instead of a range: =QUARTILE({35000, 42500, 27300, 55600}, 2). The result of another function, such as FILTER, can be passed the same way. What should I do if I get a #NUM! error when using QUARTILE.EXC? QUARTILE.EXC accepts only the quartile numbers 1, 2 and 3; 0 and 4 return #NUM!. The error also appears when the dataset is too small for the exclusive method or contains no numbers. Use QUARTILE (QUARTILE.INC) for the minimum, the maximum and small datasets. How do I calculate the median (second quartile) in Google Sheets? Use =MEDIAN(range). =QUARTILE(range, 2) and =PERCENTILE(range, 0.5) return the same value. ## Who wrote this ![Vadym Kramarenko](https://cdn.owox.com/www/webflow/6798e703afdf787334941cde_Vadym-Kramarenko.png/public) [Vadym Kramarenko](/team/vadym-kramarenko) · Growth Marketing Manager Vadym Kramarenko is a Growth Marketing Manager at OWOX, where he drives user acquisition and product-led growth strategies. He hosts the OWOX podcast, interviewing analytics professionals about data-driven marketing, attribution, and reporting best practices. Vadym specializes in turning complex analytics concepts into practical, actionable marketing frameworks. [LinkedIn](https://www.linkedin.com/in/vadim-kramarenko/) · [All articles](/team/vadym-kramarenko) [Google Sheets Tips](/blog/topics/google-sheets-tips) ## Related articles [Google Sheets Tips · QUERY with CONCATENATE in Google Sheets: Join Two Columns · October 2, 2026](/blog/articles/query-with-concatenate-google-sheets) [Google Sheets Tips · QUERY vs VLOOKUP in Google Sheets: Which One to Use · October 2, 2026](/blog/articles/query-vs-vlookup-google-sheets) [Google Sheets Tips · VLOOKUP in Google Sheets: Formula, Another Sheet and Examples · October 2, 2026](/blog/articles/vlookup-in-google-sheets) [See all articles →](/blog/articles) ## Book a call You are booked. The invitation is on its way to , with the calendar invite and the meeting link. [Reschedule](#) [Cancel](#) ## Schedule a meeting You are booked. The invitation is on its way to , with the calendar invite and the meeting link. [Reschedule](#) [Cancel](#) ## Links Pages this page links to, on this site and on docs.owox.com. Where the page has a Markdown twin, its address follows the link. - [Google Sheets Tips](https://www.owox.com/blog/topics/google-sheets-tips) — /blog/topics/google-sheets-tips.md - [Vadym Kramarenko](https://www.owox.com/team/vadym-kramarenko) — /team/vadym-kramarenko.md - [PERCENTILE.EXC](https://www.owox.com/blog/articles/percentile-function-google-sheets) — /blog/articles/percentile-function-google-sheets.md - [Book a Demo](https://www.owox.com/book-a-call) — /book-a-call.md - [FILTER](https://www.owox.com/blog/articles/google-sheets-filter-function) — /blog/articles/google-sheets-filter-function.md - [COUNTIF](https://www.owox.com/blog/articles/countif-and-countifs-google-sheets) — /blog/articles/countif-and-countifs-google-sheets.md - [IFS](https://www.owox.com/blog/articles/ifs-function-google-sheets) — /blog/articles/ifs-function-google-sheets.md - [PERCENTRANK](https://www.owox.com/blog/articles/percentrank-function-google-sheets) — /blog/articles/percentrank-function-google-sheets.md - [MAX, MIN and MEDIAN](https://www.owox.com/blog/articles/max-min-median-functions-google-sheets) — /blog/articles/max-min-median-functions-google-sheets.md - [AVERAGE functions](https://www.owox.com/blog/articles/average-functions-google-sheets) — /blog/articles/average-functions-google-sheets.md - [RANK](https://www.owox.com/blog/articles/rank-function-google-sheets) — /blog/articles/rank-function-google-sheets.md - [Exploratory data analysis](https://www.owox.com/blog/articles/exploratory-data-analysis-google-sheets) — /blog/articles/exploratory-data-analysis-google-sheets.md - [OWOX Data Marts](https://www.owox.com/app-signup) - [spreadsheet reporting](https://www.owox.com/features/spreadsheet-reporting) — /features/spreadsheet-reporting.md - [Google Sheets Tips · QUERY with CONCATENATE in Google Sheets: Join Two Columns · October 2, 2026](https://www.owox.com/blog/articles/query-with-concatenate-google-sheets) — /blog/articles/query-with-concatenate-google-sheets.md - [Google Sheets Tips · QUERY vs VLOOKUP in Google Sheets: Which One to Use · October 2, 2026](https://www.owox.com/blog/articles/query-vs-vlookup-google-sheets) — /blog/articles/query-vs-vlookup-google-sheets.md - [Google Sheets Tips · VLOOKUP in Google Sheets: Formula, Another Sheet and Examples · October 2, 2026](https://www.owox.com/blog/articles/vlookup-in-google-sheets) — /blog/articles/vlookup-in-google-sheets.md - [See all articles →](https://www.owox.com/blog/articles) — /blog/articles.md