Purpose

To elaborate and explain Excel's limitations with conditional formatting when it comes to BPC's EVDRE table.

Overview

Excel does not handle conditional formatting inside of an EVDRE as one would expect, as such one must use a workaround in order to get the desired format.

How Excel Handles Conditional Formatting inside of an EVDRE table

Excel treats Conditional Formatting as if the EVDRE table was static. Executing an Expand All deletes the entirety of the EVDRE table and recreates it in the same location. Excel recalculates the cells targeted by Conditional Formatting and removes the cells that were a part of the EVDRE table since they were deleted. 

Even though users will not see any deletion Excel will and adjusts accordingly, this can be viewed by applying a Conditional Formatting to an entire column that passes through an EVDRE table. After doing an Expand All, go back to the Conditional Formatting window and the range will have changed to explicitly exclude the section that was the EVDRE table.

Workaround

Since every time Expand All is used Excel's Conditional Formatting is removed, formatting needs to be imported from an area outside of the EVDRE table. * - In EVDRE Parameter section, select a cell outside of the EVDRE table for the AfterRange value

- In the cell select reproduce the title, and then in the cell below create a formula that references the cell immediately to its left. This is to let the AfterRange have the exact same values as the default total. * - In the cell below the title format it with Conditional Formatting as desired

- You can hide the default total field so that only the AfterRange one is displayed; additionally you can hide the cells references by AfterRange in order to maintain a standard EVDRE layout.

Related Content

Related Documents

Related Notes

Example:
KBA Note: 1702730 BPC: Excel’s Conditional Formatting Rules deleted after refreshing a report using BPC’s Expand All