Excel Video 52 continues our discussion of Conditional Formatting. We’ve been through each of the major Conditional Formatting categories in prior videos. Starting with Excel Video 52, we’ll now go through the six Rule Types in the New Formatting Rule window. Many of the options under Rule Types are available from the Conditional Formatting menus we’ve worked through in the last few videos, but the New Formatting Rule window provides several customization options you’ll find helpful. Starting with this video, we’ll work through an example for each of the Rule Types.
The first Rule Type is Format all cells based on their values. You’ve seen the Color Scales, Data Bars, and Icon Sets that are available in this Rule Type in prior Excel Videos and we’ve done a little bit of customization. Here’s another way to customize your Conditional Formatting.
The New Formatting Rule window allows you to choose how to calculate the lowest value, the midpoint, and the highest value for Color Scales. By changing just the midpoint percentile from 50 to 20 and 80, the same data can be formatted three different ways. If you have data that’s skewed either to the high or low side, or if you have data where you want to focus primarily on the high or the low values, try changing the midpoint calculation like we do in this video.
You can do the same thing by putting a number in for the midpoint. For example, if you have physician collections in a table and you aren’t concerned as long as collections are at least $600,000 during the period, you can set the midpoint to $600,000 so that the focus will be on physicians collecting less than $600,000. Play with the options in these menus. Excel gives you a ton of flexibility.
