Conditional Formatting

Be sure to know how to apply different kinds of conditional formatting in Excel.

On the Home tab, you'll find the option for Conditional Formatting in the middle.

You can select between multiple styles of formatting which will be altered based on the values entered in them. Cells can be highlighted based on relative value of a number entered in them, a word or phrase they contain, or whether they contain duplicate values from the same range.

You can designate formatting for the a certain number of the top or bottom values; a certain percentage of the top or bottom values; or those which are above or below average.

Data bars will enter a small bar graphic in the cell, which will be sized according to the numeric value in the cell.

Color scales will shade the cells on a color spectrum which mirrors the progression from high to low values.

Finally you can also choose among several icons, which will show the direction the value in any one cell is from the top.

Keep in mind that applying a new conditional format for a range will not automatically overwrite a previous rule for the same range. You need to use the clear rules option in the Conditional Formatting menu in order to remove any previous settings. If you don't do this, Excel will overlay the new conditional formatting over the old, and this will give an undesired result.