Excel 2007 conditional formats

I am a big fan of the quick conditional formats in Excel, but I get very frustrated by the in cell bar graphs and their strange behavior – basically this means they are very unreliable as a way to visualize the real relationship between numbers in a range.  They are however great as a way of quickly (in a couple of clicks) spotting outliers in the data-set.  If you want to make them more accurate, it’s possible to change the formatting rules that Excel users:

From the home tab select Conditional Formatting, and Manage rules.  You will see all the rules in the current table.  Select the rule you want to change as you will see the dialog on the right.

For example you could base the shortest bar on the number 0 and Base the longest bar on the highest value. If you want to see bars based off percentile or some custom formula, then you can make those changes here as well.

For a much more comprehensive discussion on the issues with Excel 2007 conditional formats and how to work around them check out this article by Juice Analytics.