Once upon a time I was investigating whether Excel might be an acceptable tool for interfacing with some of our data. I checked out PivotCharts and slicers and thought some interesting interactive ‘reports’ could be quickly built with them, so I set out to build a proof-of-concept demonstration. It was going fine, until I noticed this issue.
Below are a couple screen shots of scrubbed data for illustration. Can you see the issue?
For the colour-blind or anyone who doesn’t see the issue, I’ll explain. The colours that represent the organizations changed from one view to the next. Org B is orange in the first view and blue in the second. I find that extremely unsettling. I expect the orange thing in one view to represent the same thing even when the filter has changed! What’s the point of the speedy application of filters if you have to stop and totally reevaluate the visual each time it renders?
I understand that the formatting (including the colour palette) is applied after the set has been filtered, and this will cause the attribute’s colour to change if its position within the set changes. I would argue that from a business perspective, the format of an attribute should be applied before filtering, so that it is retained from one view to the next.
I did contact some folks at Microsoft in April of 2015 and they assured me it was on a list. I’m still seeing this behavior today in Excel 2016 with Office 365 subscription, which is the current version according to: https://products.office.com/en-ca/excel I await a new version to see if it’ll be resolved.