How to Show PivotTable Exceptions Below 40 Percent in Spreadsheets
Question details
The user wants to identify and display PivotTable values that fall below a 40% threshold.

- Product
- Spreadsheets
- Device & OS
- not provided
- Scenario
- Trying to filter or highlight exception data (values less than 40%) in a PivotTable, especially when a calculated-field IF formula fails to produce accurate results.
- Observed behavior
- Calculated-field IF formulas do not evaluate percentage thresholds correctly in PivotTables, requiring alternative display methods like conditional formatting or value filters.
Ensure your PivotTable is refreshed with the latest source data and that the target fields are correctly formatted as percentages before applying filters or formatting rules.
Apply a Value Filter to the PivotTable
Filtering the PivotTable directly is the cleanest way to hide unwanted data and only show rows where the value is less than 40%.
Value filters isolate specific data thresholds without the need for complex formulas, making them highly reliable for exception reporting.
Click the drop-down arrow next to the Row Labels or Column Labels in your PivotTable.
Hover over 'Value Filters' in the dropdown menu, then select 'Less Than...' from the list of options.
In the dialog box, ensure your target percentage field is selected in the first drop-down, choose 'is less than', and type '0.4' (or 40%) in the value box.
Click 'OK' to apply the filter. Your PivotTable will immediately hide all values that are 40% or higher.

Highlight Values Below 40% Using Conditional Formatting
Use this method if you want to keep all data visible in your PivotTable while visually highlighting the exceptions below 40%.
Manage PivotTables Easily with WPS Spreadsheet
WPS Spreadsheet offers powerful and intuitive PivotTable features, allowing you to easily filter, format, and analyze your data exceptions without struggling with complex calculated fields.
- 1. Open Your Spreadsheet: Launch WPS Spreadsheet and open your existing .xlsx file containing the PivotTable.
- 2. Analyze Data: Select your PivotTable and navigate to the 'Analyze' or 'Design' tab to access advanced tools.
- 3. Apply Visual Filters: Use the built-in conditional formatting or value filtering tools directly from the ribbon to instantly highlight data below 40 percent.

Frequently Asked Questions
Why doesn't an IF formula work in my PivotTable calculated field for percentages?
Calculated fields in PivotTables evaluate the sum of the underlying data before applying the calculation. Because percentages are ratios, summing them prior to evaluation often causes IF formulas to return incorrect results for percentage thresholds.
Can I filter multiple columns for values under 40% simultaneously?
Standard PivotTable value filters typically apply to one specific data field at a time. To evaluate multiple columns visually, using Conditional Formatting across the entire data range is the most efficient approach.
Will my conditional formatting disappear when I refresh the PivotTable?
No, as long as you apply the conditional formatting correctly. When applying the rule, ensure you select the formatting option to apply the rule to 'All cells showing [Field Name] values' rather than just the currently selected static cells.




