How To SheetsHow To Sheets
Formulas and Functions

Mastering the Google Sheets 90th Percentile: Formulas, Tips, and Real-World Use

Learn to compute the 90th percentile in Google Sheets using PERCENTILE.INC and PERCENTILE.EXC. Includes practical formulas, data cleaning, and real-world examples for students, professionals, and small teams.

How To Sheets
How To Sheets Team
·5 min read
Count-Data-Google-Sheets
90th Percentile Guide - How To Sheets
Photo by MART PRODUCTIONvia Pexels
Quick AnswerDefinition

In Google Sheets, the 90th percentile is the value below which 90% of data points fall. Use PERCENTILE.INC or PERCENTILE.EXC with 0.9, e.g., =PERCENTILE.INC(A2:A100, 0.9). For data with non-numeric entries or blanks, wrap the range in FILTER or ISNUMBER to exclude them, e.g., =PERCENTILE.INC(FILTER(A2:A100, ISNUMBER(A2:A100)), 0.9). This approach supports basic percentile analysis in Sheets.

What is the 90th percentile and why it matters in Google Sheets

The 90th percentile represents the value below which 90% of your observations fall. In practical terms, it helps you set targets, benchmark performance, and identify top-end behavior in datasets ranging from student scores to sales figures. According to How To Sheets, percentile-based metrics are a cornerstone of pragmatic data analysis in Google Sheets and are widely used in dashboards to summarize distributions for students, professionals, and teams. In this section, you’ll learn the core formulas and when to apply them.

Key formulas you’ll use:

  • PERCENTILE.INC: inclusive; includes endpoints
  • PERCENTILE.EXC: exclusive; excludes endpoints
  • PERCENTILE: legacy function (often equivalent to INC for many datasets)
Excel Formula
=PERCENTILE.INC(A2:A101, 0.9)
Excel Formula
=PERCENTILE.EXC(A2:A101, 0.9)

If your data contains blanks or text, clean it first and then compute the percentile. A robust pattern is to filter numeric values and compute on the filtered range:

Excel Formula
=PERCENTILE.INC(FILTER(A2:A101, ISNUMBER(A2:A101)), 0.9)

This handles non-numeric values gracefully and avoids misleading results. You can also use the legacy PERCENTILE function, but INC/EXC is preferred for clarity across datasets.

Excel Formula
=PERCENTILE(A2:A101, 0.9)

codeBlocksCountedAsMarkdownSections":3,

INC vs EXC: Choosing the right 90th percentile for your data

When choosing between INC and EXC, consider whether including endpoints matters for your interpretation. Use PERCENTILE.INC when you want to include the minimum and maximum values in the calculation; use PERCENTILE.EXC when you want to exclude the absolute endpoints from the percentile. For most dashboards and educational datasets, INC is the safer default.

Excel Formula
=PERCENTILE.INC(A2:A101, 0.9)
Excel Formula
=PERCENTILE.EXC(A2:A101, 0.9)

A practical approach is to start with INC and then compare with EXC to gauge sensitivity. If your data contains outliers, reflect that in your interpretation rather than changing the calculation method quietly.

Excel Formula
=PERCENTILE.INC(FILTER(A2:A101, ISNUMBER(A2:A101)), 0.9)

blockTypeLabel":"section"},{

Steps

Estimated time: 15-25 minutes

  1. 1

    Identify your data range

    Scope the numeric data you’ll analyze, e.g., A2:A101 for a single sheet. Confirm there are no non-numeric values that would skew the percentile, or plan to filter them out. This step sets up a clean baseline for reliable results.

    Tip: Document the range used for auditability.
  2. 2

    Choose INC or EXC

    Decide whether you want the inclusive (INC) or exclusive (EXC) method. INC is the default and includes endpoints; EXC excludes them. This choice affects the interpretation of the 90th percentile in your report.

    Tip: Start with INC for most datasets and compare with EXC if endpoints matter.
  3. 3

    Enter the 90th percentile formula

    Enter the core formula in a blank cell to compute the 90th percentile. Use a clean range, e.g., =PERCENTILE.INC(A2:A101, 0.9) and verify the result aligns with expectations.

    Tip: Test with known datasets to validate correctness.
  4. 4

    Clean data before calculation

    If there are blanks or text, filter them out first. This prevents errors and ensures numeric-only input for percentile computation.

    Tip: Prefer FILTER to isolated manual cleaning.
  5. 5

    Validate with a subset

    Compare the result on a small, known data subset to a hand-calculated percentile to validate the approach.

    Tip: Use a small test set to quickly verify behavior.
  6. 6

    Apply consistently and document

    Apply the method across your dataset and document the chosen approach (INC vs EXC) for dashboards and audits.

    Tip: Add a short notes column or doc entry describing the method.
Pro Tip: Always clean your data before percentile calculations to prevent skew from non-numeric values.
Warning: Be careful with empty ranges; many percentile functions return errors or unexpected results.
Note: For large datasets, filtering can substantially reduce processing time during analysis.
Pro Tip: Document whether you used INC or EXC in dashboards to avoid misinterpretation.

Prerequisites

Required

Optional

  • Optional: named ranges for dynamic calculations
    Optional

Keyboard Shortcuts

ActionShortcut
CopyCopy selected cell(s)Ctrl+C
Paste values onlyPaste without formattingCtrl+⇧+V
FindSearch within sheetCtrl+F
UndoRevert last actionCtrl+Z
Auto-fill downFill selection downCtrl+D
Open formula barEdit active cellF2

FAQ

What is the difference between PERCENTILE.INC and PERCENTILE.EXC in Google Sheets?

PERCENTILE.INC includes the range endpoints in the calculation, while PERCENTILE.EXC excludes them. The choice can slightly shift the 90th percentile value, especially in small datasets or when data include high outliers. Start with INC for typical analyses and compare with EXC if your interpretation requires it.

INC includes endpoints, EXC excludes them, which can change the 90th percentile slightly, especially in small datasets.

Can I calculate the 90th percentile for data with blanks?

Yes, but you should filter out blanks before computing the percentile. Use FILTER and ISNUMBER to include only numeric entries, e.g., PERCENTILE.INC(FILTER(A2:A101, ISNUMBER(A2:A101)), 0.9).

Yes, just remove blanks with FILTER(ISNUMBER(...)) before calculating.

Why might I get a #NUM! error when computing the 90th percentile?

A #NUM! error usually means the input range has no numeric values or is effectively empty after filtering. Use IFERROR to handle this and provide a user-friendly message, e.g., IFERROR(PERCENTILE.INC(...), 0) or 'N/A'.

The error often means there are no numeric values after filtering.

Is there a reason to use the legacy PERCENTILE function?

The legacy PERCENTILE function is kept for compatibility with older sheets. In most new work, prefer PERCENTILE.INC or PERCENTILE.EXC for clarity and explicit endpoint handling.

Use the modern INC/EXC forms for clarity; PERCENTILE is mainly for compatibility.

How can I verify percentile results are correct?

Cross-check the calculated percentile against a manually ranked subset or use a small test dataset where you can compute the 90th percentile by hand. This sanity check helps confirm the formula behaves as expected.

Double-check with a small test dataset by hand to confirm the result.

The Essentials

  • Use PERCENTILE.INC for standard 90th percentile results
  • Clean data with FILTER(ISNUMBER(...)) to exclude non-numerics
  • Prefer EXC only if endpoint exclusion matters
  • Guard formulas with IFERROR for missing data
  • Document your INC vs EXC choice in dashboards

Related Articles

← More in Formulas and Functions