Conditional Formatting
Many of us are still using spreadsheets for tracking hours in professional service organizations. It’s easy to get time-sheets and track metrics in a quick Excel spreadsheet so we do it instead of soliciting IT to build or buy a system. Once you get more than a few sheets in your workbook it’s a common practice to build a cover worksheet that contains summary data - a dashboard of sorts.
Using conditional formatting, you can turn simple metrics into a little something more. You can set automatic formatting on cells to reflect the value within. This means you can automatically set the background color to red when the value is below an acceptable range. Here’s how you do it…
First, create your table (hopefully using a function referencing the actual data):
Select “Conditional Formatting…” from the “Format” menu:
Using this dialog, you can define ranges of values and assign formatting elements. In this case I used numbers (percentages) but you can use text also. For instance you can set a color to green, use “greater than or equal to” in the middle box, and “Y” for the value on the right. This codes “Yes” values to green. The next rule could be “N” and mapped to red would give you a green/red based on answers to a questionnaire.
When you’re done, juts click the “OK” button and your formatting will be applied to the cells. You can always select the cells and return to this dialog to adjust the settings if you made a mistake or want to add another range.
This formatting makes key values really stand-out among a busy worksheet.
