Conditionally formatting a pivot - “Apply Rule To:” is missing

StephenD

New Member
Joined
Feb 19, 2016
Messages
6
MS Office 365 Apps for Enterprise v16.0.13127.21624
Experience with Excel - 8 years

Usually, when creating a conditional formatting rule for a data column in a pivot, I will see a section at the top of the “Edit Formatting Rule” dialog box labeled “Apply Rule To:” followed by 3 choices:
O Selected Cells
O All cells showing ....
O All cells showing .... for ....

I know that I must select a data cell in the pivot in order for the “Apply Rule To” section to appear.

Earlier today, I created a pivot on an existing workbook with other pivots on the sheet, and added conditional formatting on a calculated field in that pivot. I saw and used the “Apply Rule To” section “All cells shows “....” values.
That pivot looks and acts as expected. Subsequent rechecks of this pivot continued to show the "Apply Rule To" section.

Later, with the sheet still open, I selected a data cell in a different pivot that I had created on a previous day, that already had conditional formatting in a different column, and attempted to add conditional formatting to a different column in the pivot, however, the “Apply Rule To: section did not appear.

I checked (Home->Conditional Formatting->Manage Rules) the formatting rules of the “conditioned” column in the pivot, but no rules appeared in the Rule Manager even though selecting other choices from a linked slicer caused the formatted column colorations to match the new data in that column??!

I created a duplicate of that pivot from the data table (Insert->Pivot Table), and named it the same title (via PivotTable Options...) as the original pivot (just to see what would happen). The conditional formatting in the original table disappeared!
I renamed the replacement pivot table a unique name and checked for the “Apply Rule To” section - it still did not appear in the “Edit Formatting Rule” dialog box.

Any ideas why the “Apply Rule To:” section doesn’t appear when conditional formatting this pivot?
Are there any other caveats to make that section appear?
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

StephenD

New Member
Joined
Feb 19, 2016
Messages
6
FOLLOW-UP: After I saved and closed the macro-enabled file (.xlsm), I tried to open it later. Excel offered a selection to update external connections or not, clicking either resulted in Excel aborting, no matter the selection. It must have been corrupted somewhere along the way. Auto-save was enabled, so there were no prior versions available in browser-based OneDrive.
 

Forum statistics

Threads
1,141,583
Messages
5,707,207
Members
421,498
Latest member
matinebi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top