Inputting spreadsheet data can be a real headache. Once you get lots of rows and columns going on, it can be difficult to track where you are and what you are supposed to be doing.
Most people know that you can use conditional formatting for aesthetic reasons, but on top of that, there are real usability benefits from using conditional formatting too.
In fact, we can use conditional formatting to direct the user to specific areas of your spreadsheet that they really need to focus on right now.
Working Example
In our example we have a table with a list of contractors, with the following fields:
- Consultant ID
- Consultant Name
- Field of expertise
- Contract signing date
- Contract expires on
Imagine we have hundreds of these consultants to keep track of. We want to focus the user’s attention just on contracts that are about to expire (for example in seven days or less).
In addition to contracts that are about to expire, we want to clearly differentiate those that have already expired.
We can use conditional formatting to show the about to expire rows in orange and the already expired in red.
The trick is to use a formula to set the conditional formatting. While the orange “about to expire” is quite complex, the red formula is really easy:
=TODAY()>$G5
If today is greater than the value in $G5 then set the formatting.
To work out if the contract is about to expire is a little more involved.
AND(TODAY()<=$G5,DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())+7)>$G5)
It actually looks more complicated than it is simply because we have to generate a date seven days in the future using the DATE function:
DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())+7)
This returns a correctly formatted date so we can check that today is less than or equal to this future date.
If we set most of the dates far in the future we can see how formatted this way, the rows just jump out at you.
Summary
While we could create complicated macros and data-entry forms, sometimes the simple solution is the best. By highlighting the aspect of the spreadsheet we should spend particular attention on we can cut through the noise inherent in dealing with a lot of data and get the job done fast!
About the author
Yoav Ezer co-authors the technology and productivity blog Codswallop. He is also the CEO of a company that produces PDF to Excel conversion software.
For more Excel tips from Yoav, join him on Facebook or Twitter
Comments
Leave a comment Trackback