Formatting Issues

How to Exclude Zero Values from Bottom 10% Conditional Formatting in Excel

John WilsonJohn Wilson Nov 2, 2026 869 views

Question details

Highlight the bottom 10% of positive numbers in a dataset while completely excluding any cells that contain zero.

How to Exclude Zero Values from Bottom 10% Conditional Formatting in Excel
Product
Excel
Device & OS
not provided
Scenario
Applying conditional formatting to identify the lowest performing positive values in a dataset without zero values skewing the calculation.
Observed behavior
The standard 'Bottom 10%' conditional formatting rule includes zeros in its calculation, which falsely identifies the zero cells instead of the actual lowest positive values.
Before you start

Ensure your target dataset range does not contain error values, and identify the exact cell reference for the upper-left cell of your range before applying the formula.

Solution 1Recommended

Use a PERCENTILE.INC Formula in Conditional Formatting

Create a custom conditional formatting rule utilizing the PERCENTILE.INC and IF functions to dynamically calculate the 10th percentile for strictly positive values.

The built-in 'Top/Bottom Rules' in Excel do not allow you to filter out specific numbers like zero. To achieve this, you need to use a custom formula that checks two conditions simultaneously: the cell must be greater than zero, and the cell must be less than or equal to the 10th percentile of all positive numbers in that range.

1
Select your data range

Highlight the cells you want to format. For example, select the range A1:H17. Take note of the active cell (e.g., A1), as it will be used as the relative reference in your formula.

2
Open the New Formatting Rule dialog

Go to the 'Home' tab on the Excel ribbon, click on 'Conditional Formatting' in the Styles group, and select 'New Rule' from the dropdown menu.

3
Choose formula-based formatting

In the New Formatting Rule dialog, click on 'Use a formula to determine which cells to format'.

4
Enter the custom percentile formula

In the formula box, type: =AND(A1>0,A1<=PERCENTILE.INC(IF($A$1:$H$17>0,$A$1:$H$17),10%)). Make sure to adjust 'A1' and '$A$1:$H$17' to match your actual selected range, keeping the dollar signs for the absolute range reference.

5
Set the cell format and apply

Click the 'Format' button, select a fill color or font style to highlight the cells, click 'OK', and then 'OK' again to apply the rule.

Use a PERCENTILE.INC Formula in Conditional Formatting
Relative vs Absolute References: Ensuring the initial cell (A1) does not have dollar signs, while the full range ($A$1:$H$17) does, is crucial. This allows Excel to evaluate each cell individually against the static 10% threshold of the entire dataset.
Try WPS Spreadsheet

Highlight Data Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced formula-based conditional formatting, including array functions like PERCENTILE.INC. Easily analyze your data and highlight specific metrics without worrying about expensive software costs.

  1. 1. Open your file: Launch WPS Spreadsheet and open your dataset.
  2. 2. Select the target range: Click and drag to highlight the data range (e.g., A1:H17) you wish to evaluate.
  3. 3. Apply conditional formatting: Navigate to Home > Conditional Formatting > New Rule, choose the formula option, and input the PERCENTILE.INC formula.
  4. 4. Format and save: Pick your highlight color, apply the formatting, and seamlessly save your workbook in .xlsx format.
Free to download and use for your daily tasksFully compatible with Microsoft Excel (.xlsx) formats and conditional rulesSupports advanced formulas and dynamic array calculations smoothlyLightweight with a familiar, easy-to-use tabbed interface
microsoft office alternative - wps office

Frequently Asked Questions

Can I alter the formula to highlight a different percentage, like the bottom 20%?

Yes, you can easily adjust the threshold. Simply change the '10%' at the end of the PERCENTILE.INC function in the formula to '20%' or '0.2' to highlight the bottom 20% of positive values.

Why is the conditional formatting highlighting random cells incorrectly?

This usually happens due to incorrect absolute and relative cell referencing. Ensure that the single cell reference (e.g., A1) is relative (no dollar signs) and the evaluated range (e.g., $A$1:$H$17) is absolute (with dollar signs).

Does this conditional formatting formula exclude blank cells as well?

Yes. Because the formula uses the logical condition 'A1>0', any blank cells, which evaluate as zero in this context, are naturally excluded from being highlighted.

How do I include negative numbers but still exclude zero?

If your dataset contains negative numbers and you want to include them in the bottom 10% calculation while specifically ignoring zeros, change '>0' to '<>0' (not equal to zero) in both instances within the formula.