There is a very powerful feature that comes with Excel that will allow you to format the contents of a cell based completely on conditions that YOU specify. Of course, this is called Conditional Formatting.
When you decide to use conditional formatting, it’s all up to you and whether you want to define a single formatting condition or multiple conditions. For our purposes, we are going to decide how to use a single formatting condition before we move on to multiple conditions. So read on as I explain how and what it means to define a single condition.
When using conditional formatting, the first step is to select the cell whose formatting you would like to be conditional.
Next, with the Home tab of your Ribbon displayed, in the Styles group, click Conditional Formatting.
Excel will then display a list of the various conditions you can define. See below:
Highlight Cell Rules – These rules are designed to allow you to indicate how a specific cell should be highlighted, based on the condition that you specify.
Top/Bottom Rules – These rules are used to format cells based on whether they are in the upper or lower portion of a range of values.
Data Bars – These rules will allow you to specify a graphic bag that will appear based on the value in your cell.
Color Scales – These rules are used to indicate a range of colors that should apply to a cell, based on the value in that cell.
Icon Sets – These rules will allow you to apply different graphic icons based on the condition that you specify.
Actually, each condition selected from the options above is just a shortcut to filling in the settings of the New Formatting Rule dialog box.
In the top of the dialog box you will select a type of rule that you would like applied to your selected cells. There are six types:
- Format All Cells Based On Their Values
- Format Only Cells that Contain
- Format Only Top or Bottom Ranked Values
- Format Only Values that are Above or Below Average
- Format Only Unique or Duplicate Values
- Use a Formula to Determine which Cells to Format
When you select a rule, Excel will change the settings you can make in the bottom of the dialog box. Each rule has value, but the last one is the most powerful simply because you use a formula to define your formatting.
When you select a rule and then adjust the settings in the bottom of the dialog box, depending upon the rule, you can click the Format button to specify the formatting Excel should apply if the conditions in the rule are met.
Once you are satisfied with the settings for your Rule and formatting options, you can simply click OK to close the New Formatting Rule dialog box.
Your formatting has now been applied and you can continue working in your worksheet as you normally would.
Hopefully, now that you understand the workings of defining a single condition, you will no doubt find may instances where you can use this robust feature to make your Excel life just a bit easier.