How to Calculate Percentiles in Google Sheets

Key Insights

• Google Sheets offers three percentile functions—PERCENTILE, PERCENTILE.INC, and PERCENTILE.EXC—each using different calculation methods that can produce varying results on the same dataset.
• The PERCENTRANK function works in reverse, telling you where a specific value falls within your distribution rather than finding the value at a given percentile.
• Combining percentile calculations with conditional formatting creates powerful data visualization that automatically highlights outliers and top performers.

Introduction to Percentiles

Percentiles divide your data into 100 equal parts, telling you what percentage of values fall below a specific point. If your salary is at the 80th percentile, you earn more than 80% of the population in your comparison group. This differs fundamentally from percentages, which represent proportions of a whole.

Common use cases include:

• Test scores: SAT and GRE scores are reported as percentiles to show relative performance
• Salary benchmarking: HR teams use percentiles to set competitive compensation bands
• Performance metrics: Sales teams identify top performers (above 90th percentile) and those needing support (below 25th percentile)
• Quality control: Manufacturing uses percentiles to identify outlier measurements

Google Sheets provides robust percentile functions that handle these calculations without requiring statistical software. Let’s examine each approach.

The PERCENTILE Function

The core PERCENTILE function uses straightforward syntax:

=PERCENTILE(data, percentile)

The data argument accepts a range of numeric values. The percentile argument must be a decimal between 0 and 1, where 0.5 represents the 50th percentile (median) and 0.75 represents the 75th percentile.

Here’s a practical example with monthly sales data:

| A           | B          |
|-------------|------------|
| Sales Rep   | Revenue    |
| Alice       | 45000      |
| Bob         | 32000      |
| Carol       | 67000      |
| David       | 51000      |
| Eve         | 89000      |
| Frank       | 41000      |
| Grace       | 73000      |
| Henry       | 28000      |

=PERCENTILE(B2:B9, 0.75)

This formula returns 69500, meaning 75% of your sales reps generated less than $69,500 in revenue. The function interpolates between values when the exact percentile falls between data points.

For multiple percentiles, you can create a summary table:

=PERCENTILE(B2:B9, 0.10)  // 10th percentile: 29600
=PERCENTILE(B2:B9, 0.50)  // 50th percentile: 48000
=PERCENTILE(B2:B9, 0.90)  // 90th percentile: 84200

A critical mistake I see frequently: using whole numbers instead of decimals. Writing =PERCENTILE(B2:B9, 75) triggers a #NUM! error because 75 exceeds the valid 0-1 range.

PERCENTILE.INC vs PERCENTILE.EXC

Google Sheets provides two additional functions that handle edge cases differently. Understanding the distinction matters when your results need to match specific statistical standards.

PERCENTILE.INC (inclusive) considers values at the 0th and 100th percentiles as valid endpoints. This matches Excel’s default behavior and works well for most business applications.

PERCENTILE.EXC (exclusive) excludes the endpoints, requiring percentile values strictly between 0 and 1 (exclusive). This method aligns with certain statistical conventions and produces different results, especially for small datasets.

Compare the results:

Dataset in A1:A5: 10, 20, 30, 40, 50

=PERCENTILE(A1:A5, 0.25)      // Returns: 20
=PERCENTILE.INC(A1:A5, 0.25)  // Returns: 20
=PERCENTILE.EXC(A1:A5, 0.25)  // Returns: 15

=PERCENTILE(A1:A5, 0.75)      // Returns: 40
=PERCENTILE.INC(A1:A5, 0.75)  // Returns: 40
=PERCENTILE.EXC(A1:A5, 0.75)  // Returns: 45

The exclusive method produces different values because it uses a different interpolation approach. For datasets with fewer than 10 values, these differences become pronounced.

My recommendation: Use PERCENTILE.INC for business reporting and general analysis. Reserve PERCENTILE.EXC for situations requiring strict statistical methodology or when matching results from statistical software that defaults to exclusive calculations.

Calculating Common Percentiles (Quartiles)

The QUARTILE function provides shortcuts for the most commonly used percentiles. It accepts a quart argument from 0 to 4:

• 0 = Minimum value
• 1 = 25th percentile (Q1)
• 2 = 50th percentile (Median)
• 3 = 75th percentile (Q3)
• 4 = Maximum value

Building a five-number summary becomes trivial:

| A              | B                              |
|----------------|--------------------------------|
| Statistic      | Value                          |
| Minimum        | =QUARTILE(Data!B2:B100, 0)     |
| Q1 (25th)      | =QUARTILE(Data!B2:B100, 1)     |
| Median (50th)  | =QUARTILE(Data!B2:B100, 2)     |
| Q3 (75th)      | =QUARTILE(Data!B2:B100, 3)     |
| Maximum        | =QUARTILE(Data!B2:B100, 4)     |
| IQR            | =B5-B3                         |

The interquartile range (IQR) in the last row measures the spread of your middle 50% of data. This metric resists outlier influence better than standard deviation.

Like PERCENTILE, Google Sheets offers QUARTILE.INC and QUARTILE.EXC variants. The same inclusive/exclusive logic applies.

For identifying outliers using the IQR method:

Lower fence: =QUARTILE(B2:B100, 1) - 1.5 * (QUARTILE(B2:B100, 3) - QUARTILE(B2:B100, 1))
Upper fence: =QUARTILE(B2:B100, 3) + 1.5 * (QUARTILE(B2:B100, 3) - QUARTILE(B2:B100, 1))

Values falling outside these fences are statistical outliers worth investigating.

Finding the Percentile Rank of a Value

The inverse problem—determining where a specific value ranks—uses PERCENTRANK:

=PERCENTRANK(data, value, [significance])

The optional significance argument controls decimal places in the result (default is 3).

Consider a classroom scenario:

| A         | B      |
|-----------|--------|
| Student   | Score  |
| Student 1 | 72     |
| Student 2 | 85     |
| Student 3 | 91     |
| Student 4 | 68     |
| Student 5 | 79     |
| Student 6 | 88     |
| Student 7 | 95     |
| Student 8 | 74     |

// What percentile is a score of 85?
=PERCENTRANK(B2:B9, 85)  // Returns: 0.571 (57th percentile)

// What percentile is a score of 95?
=PERCENTRANK(B2:B9, 95)  // Returns: 1 (100th percentile)

To calculate each student’s percentile rank dynamically:

| C                        |
|--------------------------|
| Percentile Rank          |
| =PERCENTRANK($B$2:$B$9, B2) |

Drag this formula down to populate percentile ranks for all students. The absolute reference ($B$2:$B$9) ensures the comparison range stays fixed.

PERCENTRANK.INC and PERCENTRANK.EXC follow the same inclusive/exclusive patterns as their PERCENTILE counterparts. Use the matching pair for consistency—if you calculated percentiles with PERCENTILE.EXC, use PERCENTRANK.EXC for rank calculations.

Practical Application: Conditional Formatting with Percentiles

Combining percentile calculations with conditional formatting transforms raw data into actionable visualizations. Here’s how to highlight top performers automatically.

Step 1: Calculate your threshold value in a helper cell:

Cell E1: =PERCENTILE(B2:B100, 0.9)

Step 2: Apply conditional formatting:

1. Select your data range (B2:B100)
2. Format → Conditional formatting
3. Choose “Custom formula is”
4. Enter: =B2>=$E$1
5. Set your highlight color (green for top performers)

For a more sophisticated approach, create tiered highlighting:

// Helper cells
E1 (90th percentile): =PERCENTILE(B2:B100, 0.9)
E2 (75th percentile): =PERCENTILE(B2:B100, 0.75)
E3 (25th percentile): =PERCENTILE(B2:B100, 0.25)
E4 (10th percentile): =PERCENTILE(B2:B100, 0.1)

// Conditional formatting rules (apply in order)
=B2>=$E$1        // Dark green - Top 10%
=B2>=$E$2        // Light green - 75th-90th
=B2<=$E$4        // Red - Bottom 10%
=B2<=$E$3        // Orange - 10th-25th

This creates a heat map where exceptional and concerning values stand out immediately.

For dynamic percentile labels in an adjacent column:

=IFS(
  B2>=PERCENTILE($B$2:$B$100, 0.9), "Top 10%",
  B2>=PERCENTILE($B$2:$B$100, 0.75), "Above Average",
  B2<=PERCENTILE($B$2:$B$100, 0.1), "Bottom 10%",
  B2<=PERCENTILE($B$2:$B$100, 0.25), "Below Average",
  TRUE, "Average"
)

Troubleshooting Common Errors

#NUM! Error

This occurs when the percentile argument falls outside the valid range:

=PERCENTILE(A1:A10, 1.5)   // Error: 1.5 > 1
=PERCENTILE(A1:A10, -0.1)  // Error: -0.1 < 0
=PERCENTILE.EXC(A1:A10, 0) // Error: EXC excludes 0
=PERCENTILE.EXC(A1:A10, 1) // Error: EXC excludes 1

#VALUE! Error

Non-numeric data in your range causes this error. Clean your data with:

=PERCENTILE(FILTER(A1:A100, ISNUMBER(A1:A100)), 0.5)

This filters out text, blanks, and errors before calculating.

Empty Cells

The PERCENTILE function ignores empty cells automatically, but cells containing spaces or zero-length strings may cause issues. Use TRIM or data validation to prevent hidden characters.

Inconsistent Results Across Functions

If PERCENTILE and PERCENTILE.INC return different values than expected, verify you’re comparing equivalent functions. Mixing inclusive and exclusive methods produces inconsistent analysis.

Small Dataset Warnings

Percentiles become less meaningful with fewer than 20 data points. The interpolation between values introduces significant estimation error. Consider whether percentiles are the right tool for small samples, or collect more data before drawing conclusions.