Pivot Table Issues

Fix Pivot Table Conditional Formatting When Columns Collapse

Adam Davis Oct 1, 2026 868 views

Question details

Users need to correct conditional formatting rules that fail to highlight values with more than two decimal places when pivot table columns are collapsed.

Fix Pivot Table Conditional Formatting When Columns Collapse

Product: Spreadsheets
Device & OS: not provided
Scenario: Applying conditional formatting to identify values with fractions beyond two decimal places in a collapsed pivot table.
Observed behavior: Using a ROUND formula causes conditional formatting to fail for values where the third decimal digit is 5 or greater, as rounding up makes the calculated difference negative or zero.

Before you start

Ensure you have the exact cell references for your pivot table data range before updating the conditional formatting rule, and double-check your column aggregation settings.

Solution 1Recommended

Use TRUNC Formula Instead of ROUND in Conditional Formatting

Replace the ROUND function with the TRUNC function in your conditional formatting rule to ensure decimal remainders are accurately calculated without rounding errors.

The ROUND function can inadvertently round up values (for example, 104.006 becomes 104.01). Subtracting this rounded number from the original value results in a negative number, which fails the '>0' condition.

The TRUNC function simply cuts off the extra decimals without rounding them up. This ensures the subtraction always yields a positive remainder for values with extra decimal places, triggering your conditional formatting correctly.

Open Conditional Formatting Rules

Select the target cells in your pivot table. Go to the Home tab, click Conditional Formatting, and select Manage Rules.

Edit the Existing Rule

Select the rule containing your ROUND formula and click Edit Rule.

Update the Formula

Change the formula from =C8-ROUND(C8,2)>0 to =C8-TRUNC(C8,2)>0 (adjusting the 'C8' cell reference to match your actual starting cell).

Apply and Verify

Click OK to apply the updated rule. Test by collapsing the pivot table columns to ensure values like 104.006 are now properly highlighted.

Use TRUNC Formula Instead of ROUND in Conditional Formatting

Precision Preserved: TRUNC guarantees accurate greater-than-zero comparisons by preserving the exact trailing decimals instead of rounding them mathematically.

Efficient Spreadsheet Management

Manage Pivot Tables and Conditional Formatting Easily with WPS Spreadsheet

WPS Spreadsheet fully supports complex conditional formatting and pivot table functions, ensuring formulas like TRUNC work seamlessly and accurately. It is a highly compatible and lightweight solution for your daily data analysis tasks.

1. Open WPS Spreadsheet: Launch WPS Office and open your .xlsx or .xls data file.
2. Select Pivot Table Data: Highlight the data range within your Pivot Table where the conditional formatting needs to be applied.
3. Apply Conditional Formatting: Navigate to the Home tab, click Conditional Formatting > New Rule, and input your TRUNC formula to precisely highlight the exact decimal points.

Fully compatible with Microsoft Excel formulas, conditional formatting, and file formatsAdvanced Pivot Table tools for deep data analysis without performance dropsLightweight application that runs smoothly on almost any deviceFree built-in templates to create professional reports and dashboards

Frequently Asked Questions

Why does the ROUND function fail in my conditional formatting rule?

The ROUND function rounds digits up if the subsequent decimal is 5 or greater. When you subtract the rounded number from the original, it results in a negative value, causing the greater-than-zero rule to evaluate to false.

What is the primary difference between TRUNC and ROUND?

TRUNC forcibly removes the fractional part of a number to a specified precision without rounding it. ROUND mathematically adjusts the number up or down based on the value of the trailing digits.

Will conditional formatting apply properly when I collapse and expand pivot table fields?

Yes, provided the conditional formatting rule is scoped to the Pivot Table fields rather than fixed absolute cell references, and your mathematical formula (like TRUNC) correctly handles the aggregated data values.

Excel & Data Problems