By Susie Hudacheck, Senior Consultant, Technology Management
Are you still using just a small percentage of what Excel 2007 has to offer? If so, you may want to take some time to explore many of the new/updated features available. One I find helpful is calling attention to cells in a spreadsheet by using ‘Conditional Formatting.’
This function has been updated from what was available in 2003. It’s now easier to apply and manage formatting to ranges of cells based on conditions. There are more formatting options too, such as color scales, data bars, icon sets, top/bottom ranked values, above/below average values, unique/duplicate values, and table column comparisons.
For example, if questions were asked and responses recorded as y(es), n(o), or q(uestion) and you want to call attention to the cells that contain ‘n’ and ‘q,’ you could conditionally format just those answers by changing the font, modifying the cell border, and/or filling a cell.
Example 1:
- Where the cell value contains ‘n’ format the font to be bold, italicized and red; and
- Where the cell value contains ‘q’ format the font to be blue and outline the cell in blue.
Another way to apply conditional formatting is to use bars, icons (e.g., stop light, arrows), and/or a color scale to call attention to cells.
Example 2:
- The length of the blue data bar indicates lowest to highest value; and
- When the value is less than ‘3’ show a red stop light, greater than or equal to ‘3’ show a yellow stop light, and greater than or equal to ‘7’ show a green stop light.
So now you’re thinking, “That could be helpful but where do I find it?” You may expect it to be in the ‘View’ or ‘Formula’ ribbon, but you’ll actually find it in the ‘Styles’ section of the ‘Home’ ribbon.
To use it, you can simply highlight the cells to which you would like to apply the formatting and then click on ‘Conditional Formatting.’ From there, select how you wish to format the cells. My recommendation is to just go in and play; you’ll find it’s easier than you think! To help get you started, study the rules applied to the examples above.
When using ‘Conditional Formatting,’ you may find the ‘Manage Rules’ feature to be useful. You can view all rules by either a specific cell selection or from a worksheet. You have the ability to create a new rule and edit/delete an existing one. You can also stop a rule from this area.
I hope you find ‘Conditional Formatting’ useful and easy to use.