How to Exclude Zero Values from Bottom 10% Conditional Formatting in Excel
Question details
Highlight the bottom 10% of positive numbers in a dataset while completely excluding any cells that contain zero.

- 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.
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.
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.
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.
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.
In the New Formatting Rule dialog, click on 'Use a formula to determine which cells to format'.
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.
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.

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. Open your file: Launch WPS Spreadsheet and open your dataset.
- 2. Select the target range: Click and drag to highlight the data range (e.g., A1:H17) you wish to evaluate.
- 3. Apply conditional formatting: Navigate to Home > Conditional Formatting > New Rule, choose the formula option, and input the PERCENTILE.INC formula.
- 4. Format and save: Pick your highlight color, apply the formatting, and seamlessly save your workbook in .xlsx format.

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.




