Forum

Notifications
Clear all

Pivot Table Conditioning Changes when expand/collapse (cross-post)

2 Posts
2 Users
0 Reactions
412 Views
(@dmj120)
Posts: 2
Active Member
Topic starter
 

Ok, topic #2 Laugh Also posted on MrExcel forum.

I want to highlight a few items, so I created a special condition that extended beyond these dozen or so rows to allow for expanding.

Screenshot-2024-09-17-164707.png

After collapsing (or expanding), the conditional format "applies to" is modified and then I get the below.

Screenshot-2024-09-17-164333.png

 

Advice? 

Thanks

Josh

 
Posted : 18/09/2024 7:49 pm
(@catalinb)
Posts: 1937
Member Admin
 

Best is to provide a sample file so we can test, even if the subject seems simple to replicate, this way you'll get faster responses.
In Excel, maintaining conditional formatting in a pivot table can be challenging, especially when expanding or collapsing rows, as Excel often adjusts the “applies to” range automatically. Here are a few methods to help maintain your conditional formatting regardless of changes in the pivot table’s structure:

1. Apply Conditional Formatting to the Entire Pivot Table Range

  • Select the entire pivot table instead of a limited range. This ensures the conditional formatting covers all potential rows and columns, even when you expand or collapse sections.
  • Go to Conditional Formatting > Manage Rules and set the Applies To range to encompass all cells in the pivot table (e.g.,
    $A$3:$D$100

    ). You may need to estimate the range if it frequently changes in size.

2. Use “New Rule” with a Formula for Dynamic Ranges

  • Instead of formatting specific rows, create a rule based on a formula. This can allow you to apply conditional formatting dynamically without Excel changing the range when expanding or collapsing.
  • Go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Set up a formula that applies to the relevant cells within your pivot table.

3. Set Conditional Formatting as Part of the Pivot Table’s Format Options

  • Excel has options to preserve formatting with pivot tables:
    1. Right-click the pivot table and select PivotTable Options.
    2. In the PivotTable Options dialog, go to the Layout & Format tab.
    3. Check Preserve cell formatting on update.
  • This doesn’t guarantee the formatting will remain when the structure changes, but it helps Excel retain as much of the original formatting as possible during refreshes and updates.
 
Posted : 30/10/2024 12:43 am
Share: