Computer Training & Application Solutions made Simple
Contact us for a free consultation
  • Click to send us an email. We'd love to hear from you!   Ottawa       613-656-4229
  • Click to send us an email. We'd love to hear from you!   Montreal    514-342-9494

I Didn't Know That! 10 Cool Things About Excel's Conditional Formatting

By Deborah J. Sparks

  1. Use formulas in your criteria. Conditional formatting relies on formulas that can result in "true" or "false", so you can use a variety of formulas and combinations of Excel functions, including IF, AND, OR and VLOOKUP, to name just a few.
  2. Locate duplicates in a range of data by using conditional formatting.
  3. Create a 'pseudo' fourth condition. Conditional formatting is restricted to 3 criteria in all Excel versions prior to 2007. However, you can create a “pseudo” fourth criterion. For example, apply shading in red, green and blue using the standard 3 criteria. Then, with the cells selected, change the default shading to black.
  4. Conditionally format every other row using the MOD and ROW functions.
  5. Conditionally format based on partial text strings. Use functions including IF, LEFT, MID and RIGHT to format cells that contains partial text strings.
  6. Shade entire rows instead of just one cell within the row. It’s all in how you select and which cell is active when you apply the formatting.
  7. More than 3 criteria? Create VBA code OR upgrade to Excel 2007, which no longer has the 3-condition limitation.
  8. Copy conditional formatting to other cells by using the Format Painter.
  9. Locate cells that are conditionally formatted using Go To, Special.
  10. Upgrade to Excel 2007. In addition to the improvements mentioned above, conditional formatting in Excel 2007 offers many built-in conditions, so that functions are no longer necessary. You can also use data bars, icon sets, colour scales and more. In short, if you do a lot of conditional formatting, you may want to upgrade for this feature alone.

More Newsletter Articles

© 2009 DJH Training & Application Solutions Inc.

WOULD YOU LIKE TO USE THIS ARTICLE IN YOUR NEWSLETTER, EZINE OR WEB SITE? You can, as long as you include this complete blurb with it: An entrepreneur at heart, Deborah J. Sparks established DJH Training & Application Solutions Inc. over 20 years ago, realizing her dream of creating a company known for providing high quality computer software training, application development and support and exercising her passion for teaching. To learn more and to register for DJH's free newsletter, Innovations, please visit