Microsoft Excel is a widely used tool in American business. For that matter, the computer spreadsheet category, in all its incarnations, is one of the primary business tools period, next to the word processor.

I happen to use Excel frequently for data analysis and general business math (add, subtract, multiply and divide!). Nothing fancy really. However, I do consider myself to be a power user, relatively speaking. Now, I haven’t mastered statistical formulas, pivot tables, or every feature of the graphs and charts, but I believe that I have mastered the 20% of functionality that yields the 80% of results, and then some (maybe 35/90).

I believe that one of the most overlooked and powerful features of Excel is the conditional formatting feature. This allows things like changing the background color of a cell based on cell values or formulas–simple but very powerful. In fact, with a few simple techniques, you can find data mismatches, unused data, duplicate data, or matching data–all visually so the opportunity for error is greatly diminished.

Amazingly, I’ve found that very few (actually none) of the people to whom I have mentioned this feature have ever heard of it, much less used it. That is why I feel it to be my personal duty to extol the virtues of this feature to the few that read this blog.

The conditional formatting dialog can be found under the Format menu. Note that it does not appear when you right-click on a cell. I believe that this is an unfortunate omission on Microsoft’s part, not only as a missing convenience, but also as an opportunity to advertise this useful feature.

A cell is allowed to have up to three conditional formats. For example, a number value could have three different colors if it was positive, zero, or negative, respectively. Or a cell could be specially colored if its value was the same as the cell preceeding or following it. This can be helpful for spotting duplicates, although there are more effective ways to do this without using conditional formatting.

I just used this feature today to compare values that were supposed to match but didn’t. Rather than using the error prone technique of manually inspecting and formatting the data, I used conditional formatting and a formula to highlight mismatched values. The result was a professional-looking spreadsheet that I presented to management to visually illustrate a scenario that needed remedied. And of course, since I was the one who did the analysis, I will likely get credit for the proposed remedy. Bonus!–not $$$, just brownie points. And I did it with very little manual work on my part: just a couple of database queries, some conditional formatting, and some headings finish it off–oh, and repeating rows for page headers too. Fun stuff!

So, without giving a complete tutorial on conditional formatting, I urge you to check it out in the Excel help documentation. I admit, I’m probably being negligent in my responsibilities by leading you this far only to leave you without detailed instructions, but time is short, and I’ve at least wet your appetite. But, just to provide some degree of assistance, here are a few links to learn more:

  • Conditional Formatting
  • Add, change, or remove conditional formats
  • How to Use Conditional Formatting to Shade Every Other Row
  • Microsoft Office Assistance: Highlight operations performance with conditional formatting
  • How to Create a Conditional Format to Hide Errors
  • Demo: Use conditional formatting to highlight operations deficiencies and opportunities
  • Creating conditional formatting formulas

    • Once you master the conditional formatting technique, you will likely find multiple uses for it that will make your life (if only slightly) easier. Enjoy, and here’s to the 80/20 rule!