Conditional Formatting
Excel has an option called Conditional Formatting, which is a powerful tool that automatically updates the format of cells based on what data is inside them. This singular feature would have saved me many hours formatting spreadsheets to highlight data, had I known about it at the time.
How to Use
The Conditional Formatting option is on the Home ribbon next to the general cell formatting options:
There are a number of ways you can highlight cells based on the data contained, including:
- Greater than
- Less than
- Between
- Equal to
- Other options around text, date, and duplicate values
Overall, the tool can handle almost all automatic formatting needs you might have. After you select the type of logic you want to use, you are able to plug in the actual values and select the format you want the cell to have.
Excel provides good formats for use, like red, green, and yellow. I recommend sticking with these standard options, as they do a good job of highlighting these cells for the user. However, there is the option to create your own formats if you so desire.
One word of caution
When you click on a cell, you can see what data is in the cell by looking at the formula bar. However, there is no easy way to see if a cell has auto-formatting rules in place. My recommendation is to make sure that when applying these rules, you apply them to an entire column afteryou are finished with the workbook. If you do it before you have finished entering the data, there is a chance you could add a new row that will not have these auto-formatting rules.
This is a powerful tool in Excel that many people do not know about. I make sure to think about using it anytime there is a need to highlight cells that meet certain criteria before I start trying to highlight them by hand. Have Excel do the work for you!