Conditional Formatting Trick 3 – The percentmin Property
Folks that have been using data bars (see here for more information) in Excel 2007 sometimes bump into a situation where the size of the bar painted by Excel on the smallest value in the dataset seems too big. An example will probably help. Take a look at this fake data and accompanying data bars:
The size of the data bar for the last value – 170 – is too big given the relative size of 170 to the other numbers in the range (hundreds of thousands). Why would that be – the data bar should technically be 0 pixels wide? The answer is that when we were doing usability testing of this area in Excel, we found that users preferred not to see blank data bars, so Excel’s default was set to a 10% minimum width. While there is no UI to tweak that setting, there is an OM property – percentmin – that you can use to set the minimum width to whatever you like. Accordingly, if I select the range, open VBE and type “activecell.formatconditions(1).percentmin = 1” in the immediate window, I see this:
There are probably other interesting uses for this property … for example, I have seen someone write a bit of VBA to always make sure there was at least one pixel showing in this sort of situation, but the threshold was dynamic, not set to a fixed value like 10%.
