I am a very visual person. I like to glance at something and glean some basic important information. For example, I maintain a spreadsheet that serves as a client dashboard. I designed it in such a way that I can quickly gauge the status of my jobs in relation to certain due dates. To accomplish this, I use the Conditional Formatting function in Excel 2007 to show me a green, yellow, or red icon depending on how close today’s date is to the due date. Note: This only works in Excel 2007 or later.
Here is an example:
Here is how to create this:
- Set cell B1 to today’s date using the formula “=now()” without quotes. That way, the date will always be current.
- List the project due dates in column B.
- In column C next to the due dates, add a formula to calculate the difference between the due date and today’s date. In this example, cell C4 contains the formula “=B4-$B$1” without quotes. Drag the formula down to all project rows.
- In column C, set the font size to 18 and the text color to white so we do not see the actual values.
- Select cells C4 through C7 and go to Conditional Formatting –> New Rule… on the Home ribbon.
- Under “Select a Rule Type,” leave the default selected (Format all cells based on their values).
- Under “Format Style” select Icon Sets
- Set the rules as shown below and click OK. Note: Set the Type to Number first before setting the Value.
Your worksheet should now look like the example. This should be a jumping off point for more creative uses of conditional formatting and the expanded options available in Excel 2007 and later. If you develop something particularly creative, please share in the comments!