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:

1. Consultant ID
2. Consultant Name
3. Field of expertise
4. Contract signing date
5. 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

photo by Marco Bellucci

It’s been more than 3 weeks now since I couldn’t use my iPhone app for my financial budget. The software developers proposed a 2 weeks testing and debugging on their software as the problem came from a low version upgrade to their most recent release. Well, I couldn’t really put the blame on them as I didn’t regularly update my apps as I was quite happy using the current versions. So this could be one of those problems with the software as there seems to be a missing link or you just need to directly update your software.

Now I’ve come across this article that I could use as a solution or even a replacement for my paid software. The author’s quite an expert in excel and he shared his technique on his blog post entitled “Easy Way to Add Up Expenses in Your Spreadsheets” from couplemoney.com. He shows you how you could add colors to your list of expenses, each color representing a category or even a mixed budget for you and your partner. Then easily add up the expenses based on each color.

I would say that it’s really a great idea and you could visually see and pinpoint which ones are good and need-to-cut expenses. He also provided the code and macros on how to set it up yourself, a detailed instruction and some images so I think that it’s not that difficult to accomplish. Try it out yourself and you’ll see that it really does make wonders.

This is another way on how we could maximize on the features that excel is able to provide us. So what do you think? Has it changed the way you look on how to use Excel? I do hope that you enjoy it as I did. Your comments and suggestions are welcome.