Tuesday's Tip #21: Excel - Conditional Formatting

Learning Features Like Conditional Formatting

One of the things I have learned about training over all these years on programs like Excel, half the battle is knowing what you need to learn.  Programs like Excel have new features that are constantly being added - yet most users don't know what the features are, much less how to use them. Conditional Formatting is one of those features - although it's been around for a while - most people aren't sure exactly what it does OR how to use it.

I like to put some of the steps together in the blog but I do recommend watching the video - I will go through multiple examples and you'll get a better feel for the features.

What is Conditional Formatting?

Conditional Formatting is when you build logic into your formatting. In simple terms - you automate your formatting. For example - if this cell is above 50 - make it yellow or if this cell contains the word "late" make it red, or if this date is more than 30 days from today - format it in orange.

Although conditional formatting IS formatting - you don't have to set it like you do other types of formats, it sets automatically - when a certain criteria is met.

The other thing to udnerstand about Conditional Formatting is that it is setup for ranges of cells and as rules. 

So when you create a Conditional Format - you select a range of cells to apply it to. When you save it - it saves as a rule. You can have multiple conditional formats on the same range - and they are applied in order. We won't get this complicated in this tip but it is possible and fairly easy to do.

How to Use Conditional Formatting

1. Identify the logic for the formatting and think about how to create it in Excel. For example, cell c3>50.
   
   
2. Identify the range of cells to apply conditional formatting to and decide how you want to format it. For example: D1:D50 - format with yellow cell background.
   
   
3. Create the conditional formatting. Highlight the cells to be formatted (not including header cells) and select Conditional Formatting button on the Home ribbon.
   
4. Choose a preset format from the drop down or create your own rule.
   
5. Fill our the criteria using drop down and/or by selecting the right cells.
   

Modify a Conditional Format

Once a rule is created - you can make changes to the rule.

1. Select Conditional Formatting button on the Home ribbon and Manage Rules...
   
   
   
2. Select the rule you'd like to modify and click Edit Rule...
   
   
3. Make the changes to the rule and click OK.

Clear Conditional Formatting

Clearing Conditional Formatting is different than clearing regular formatting. You have to clear it where you create it, which means you have to delete the formatting rule.

1. Select Conditional Formatting button on the Home ribbon.
   
   
   
2. Select Clear Rules.