Red means stop, green means go, yellow means watch out. By employing this principle, you can monitor data and inventory performance — green for good, red for not-so-good — across your Excel 2007 documents using conditional formatting.
Excel 2007's conditional formatting can help you track performance against your organizational goals. For example, let's say your goal is to maintain a 10% or less product defect rate. You want to alert management when the defect rate is greater than 10% or trending up toward 10%. So you create a worksheet that tracks the defect rate on a daily basis, with the date of manufacture in Column A and the defect rate (e.g., 10) in Column B. Follow these steps to set up conditional formatting:
1. Select Column B.
2. Click the Home tab.
3. In the Styles group, click the Conditional Formatting drop-down arrow.
4. Point to Icon Sets.
5. Click More Rules.
6. Select Format All Cells Based On Their Values.
7. Click the drop-down arrow of the Icon Style box and select 3 Traffic Lights (Rimmed).
8. Click the Reverse Icon Order check box.
9. Select Number from the Type value box.
10. Enter 10 in the Red value box.
11. Select Number from the Type value box.
12. Enter 5 in the Yellow value box.
13. Click OK.
All cells containing 10 or above will be marked with a red traffic light, those containing 9 through 5 will be marked with a yellow traffic light, while those under 5 will be green. Now managers can scan the data for yellow or red traffic lights and take corrective action in a timely manner.
Miss a tip?
Check out the Microsoft Excel archive, and catch up on our most recent Excel tips.
Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.