Digital Workshop Center Trade School

Using Conditional Formatting

[Note: This article was originally published in The Coloradoan on 2/24/12]

A client recently came in looking for help with Excel spreadsheet issues. As the director of a youth program, she was using an Excel spreadsheet to track participants’ enrollment information. She needed the name of an active participant to show in green. If the participant was transitioning out the name was to show in yellow, and if the participant had left the program, the name was highlighted in red.

She had approximately 3,000 participants to work with at any given time, and she manually went through every entry to review the enrollment status and change the color accordingly. My first thought was “what a horrible waste of time.”

Hopefully, we all know that computers save time when doing repetitive tasks, and a program such as Microsoft Excel was built to replace paper spreadsheets and at the same time make users more efficient in our daily lives. Along these lines, a feature of Excel that I love to teach people about is called “Conditional Formatting.”

Conditional formatting is when you want to format cells in your spreadsheet based on one or more conditions. Based on criteria that you as the user can set, Excel can automatically highlight cells, create data bars, use icon sets, and much more. Below is an image showing what this dialog looks like in Excel:

Using Conditional Formatting

In my client’s example, I worked with her to set a conditional formatting statement that read “if the participant was in the youth program between a specific start date and a specific end date, then we will format the cells to background highlight in green. Or else, if they are transitioning out of the program, we will use a different format to background highlight in yellow.” And so on.

Excel does all the dirty work of deciding when the participant falls under which condition, and formats accordingly. All of my client’s manual work is now replaced with literally a couple clicks of the mouse.

The manual process of scrubbing through data certainly works for some situations and may be your only option. However, it also leaves a high risk for human error because human beings are much more prone to mistakes than a computer. So, let the computer and Excel do what they do best, handling your repetitive tasks cleanly and let Excel pick up the slack whenever possible.

Stu Crair is owner of Digital Workshop Center. Reach him at (970) 980-8091.

Want to learn more about Microsoft Excel and what it can do? Register for one of our Excel classes today!