Excel Conditional Formatting

Why to Use Conditional Formatting in Excel

Conditional formatting is a powerful feature that gives your reports a very polished look, and can also help identify exceptions in your data, such as blanks, differences, or variances outside tolerable thresholds.  Conditional formatting is also great for showing relativeity through icon sets, data bars, color scales, and top/bottom sets.  In all both cases, it is applied automatically when conditions are met, which you define.  This is a very flexible way to set up your formatting, whether it’s for font color, type, style, size, fill color, border style, size, color, or even the number format.  The screenshots below show some different types of conditional formatting:

How to Use Conditional Formatting in Excel

To apply conditional formatting, select the range you want to apply to, then from the Home ribbon tab, click the Conditional Formatting box.  Here there are several built-in conditioanl formatting options.  I generally click Manage Rules to show a list of all the rules in place (if any).  From there, you can add, edit, and delete conditional formatting rules.  You can also display this dialog box by clicking Alt-H-L-R, or using the XLEV8 Conditional Formatting Picker, which we’ll discuss more below.  You can toggle between showing rules for the current selection or the entire sheet with the drop-down box at the top.

If you click New Rule, you’re presented with a few differnt built-in options you can apply on the top, and the specific settings on the bottom.  I like to use the bottom option – using a formula – because it is very flexible.  You can use a formula to apply the conditional formatting, similar to how you would enter a formula to calculate the cell’s value, except that an IF() function is inferred and not necessary (i.e. =A2=”Frog”).  When the formula results with a TRUE result, any formatting you have selected will be applied.

Note that all of the rules that you apply are applied in a prioritized order, with the highest priority displayed at the top.  You can reorder these using the up and down buttons next to the delete button.  This ordering is used to resolve formatting conflicts if multiple rules result to TRUE.

The XLEV8 Excel add-in contains a few macros that help you quickly apply some common custom conditional formatting options, including the options shown in the screenshots above.  These items and others can be quickly applied with the Conditional Formatting Picker macro.  Just select the range to apply to, run the Conditional Formatting Picker, enter the letter corresponding to the type you want to apply, then click Enter!

Example Files

Conditional Formatting – Before

Conditional Formatting – After

Excel Conditional Formatting Video