Pivot Table Issues

How to Show PivotTable Exceptions Below 40 Percent in Spreadsheets

Kushani NimanthikaKushani Nimanthika Nov 3, 2026 869 views

Question details

The user wants to identify and display PivotTable values that fall below a 40% threshold.

How to Show PivotTable Exceptions Below 40 Percent
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.
Before you start

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.

Solution 1Recommended

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.

1
Access Filter Options

Click the drop-down arrow next to the Row Labels or Column Labels in your PivotTable.

2
Select Value Filters

Hover over 'Value Filters' in the dropdown menu, then select 'Less Than...' from the list of options.

3
Set the Threshold

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.

4
Apply Filter

Click 'OK' to apply the filter. Your PivotTable will immediately hide all values that are 40% or higher.

Apply a Value Filter to the PivotTable
Dynamic Updates: Value filters automatically adjust and re-evaluate your data thresholds whenever the PivotTable is refreshed.
Advanced Spreadsheet Analysis

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. 1. Open Your Spreadsheet: Launch WPS Spreadsheet and open your existing .xlsx file containing the PivotTable.
  2. 2. Analyze Data: Select your PivotTable and navigate to the 'Analyze' or 'Design' tab to access advanced tools.
  3. 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.
Fully compatible with Microsoft Excel (.xlsx) formats and PivotTables.Intuitive interface for applying conditional formatting and value filters.Free and lightweight alternative to heavy spreadsheet programs.Built-in advanced data analysis and charting tools.
QA img-9

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.