Ok, topic #2 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.
After collapsing (or expanding), the conditional format "applies to" is modified and then I get the below.
Advice?
Thanks
Josh
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:
- Right-click the pivot table and select PivotTable Options.
- In the PivotTable Options dialog, go to the Layout & Format tab.
- 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.