A Whole Other Dimension to Formatting, Conditional Fomatting in Excel 2007

Hello Again:

Hope all survived the Holiday Season and Happy New Year.

Although Conditional Formatting is not new to Excel 2007, the possibilities of conditional formatting have been greatly expanded in 2007 relative to prior versions.

Conditional formatting (hence the name) allows formats to be applied based on defined conditions. A conditional format can be defined based on the following:

Range of Cell Values - Format a range containing values from lowest to highest. Formats can be 2 or 3 color variations (color shade varies from low to high values), data bars (bars of color in cell based on cell value within range, like a bar graph), or icon sets (icons related to value of cell within range). An example would be a range of values form 1 to 10 vary from blue to red.

Cell Contents - Format a cell based on contents (containing a specific value, text, date). An example would be cells within a range containing 10 would be blue.

Cell Range Rankings - Format a range containing values based on ranking in range (top 10%, eg). An example would be the cells that are in the top 10% of a range of values from 1 to 100 would be green.

Cell Range Average - Format a range containing values based on average of range (above or below average). An example would be the cells that are above average within a range of values from 1 to 100 would be green.

Cell Range Unique or Duplicate Values - Format a range containing unique or duplicates values. An example would be cells containing duplicates within a range of values would be red.

Cell Formulas - Format a range based on a formula being true or false. An example would be a formatting odd rows on a worksheet by using =MOD(ROW(),2)=1 as the formula and then defining the rows and format like gray fill. 10 Bonus Points to those whose see the formula logic!

So where is the Conditional Format option anyway??

In the Home ribbon, Styles command group, there is a button for Conditional Formatting. There are 5 predefined options (Highlight cells, Top/bottom, Data Bars, Color Scales, and Icon Sets). At the bottom of the list are options for New Rules, Clear Rules, or Manage Rules. New Rules allow one to create and define custom rules as per above.

As you can imagine, there are now many options as to formatting other than just bold. HA!

Take care in the meantime until next time, Joel.