Microsoft Excel’s Conditional Formatting allows you to create rules whereby the format of one or more cells changes based on the data in your worksheet. You would want to do this so that you can focus on the items that need review or correction. Examples could be employees with excess overtime, departments with high absenteeism or injuries, products with a high number of returns, or salesmen failing to make sales goals. Starting with the 2007 version Excel has not only made it easier to apply conditional formatting, but it also has made more formatting options available.
The following screen image shows some test data used in this post.
Clicking on the Conditional Formatting on the Home tab presents five main categories:
- Highlight Cell Rules
- Top/Bottom Rules
- Data Bars
- Color Scales
- Icon Sets
Highlight Cell Rules allow you to format cells whose value is greater than/less than/equal to a value or between two values. This example highlights the cells greater than the value 3989.
Top/Bottom Rules allow you to format cells whose values are in the top/bottom percentage/count of the items or are above/below the average of the values. This example highlights the cells below average.
Data Bars formatting places a vertical line just to the left of the values and then visually shows the relationship between the values.
Color Scales formatting highlights the largest value with the top color of the scheme chosen, the smallest value with the bottom color of the scheme chosen, and then shades the remaining cells based on the relative values.
Icon Sets formatting places symbols at the beginning of each cell. The first three groups of symbols attempt to show whether a cell is good, bad, or indifferent. The last group of symbols attempts to show the values of the cells in relation to each other.
All of the conditional formatting techniques are ‘live’: the formatting changes as soon as the values change and the spreadsheet recalculates.
Copyright 2013 – VBA Consultants Ltd