Let's say you want to conditionally format your SharePoint exported pivot table (or any Excel table). In this example one column includes the classes A and B and another is a sub-class A1, A2, B1, B2 etc., and you want all class and subclasses show in different colors, in this case if the text in column A starts with A, or B.
The 2 rules you need may look like this: (the range A5:F95 contains the pivot table)
And the specific rule uses the left 1 character of the cell in column A (take away the $ sign from the row # to let the reference be relative):
Now, if you refresh the table, it may look like this:
If you analyze again your rules if will find that they were changed. Why? I don't know:
The work around is just to select the cells in column A starting with A5 (in this example), and 'format copying them (the brush icon) the equivalent cells in B-F columns. You will get the colors back and a refresh will not blow away the colors in the data area.
Excel adds two new rules that apparently solve the issue: