Software

Use Excel's conditional formatting to find errors

Formats distinguish values in a spreadsheet. You might add a currency character, align numeric values to the right, or change the font type or size for heading rows. The possibilities are endless and most of us go through a phase of formatting everything. Then, we get bored with the process and apply as few formats as possible — which is probably the best way to go.

What you might not realize is that you can use conditional formats to find errors, and it's a pretty slick trick. Conditional formats do exactly what the name implies: Excel applies a format only when a specific condition is met. You might already be using this feature to distinguish values from one another, but you can put it to work to find errors, both literal and rule violations. Use the feature as you normally would, but be open to changing the way you view formats.

The worksheet below shows a simple example. I've applied a conditional format to expose an overtime error. This spreadsheet assumes that every employee works a 40-hour week over five days. Thanks to a conditional format that highlights any value over 40, you can see right away that an employee is scheduled to work overtime or that someone has entered the wrong number of hours per day.

february2008blog6figar.jpg

february2008blog6figbr.jpg

Now, in truth, the format doesn't catch a literal error. It's merely pointing out that a value is violating a business rule — Gettle is scheduled to work more than 40 hours.

At this point, you know which employee's schedule is problematic. Now you have to determine which day violates the 40-hour workweek rule. In such a small spreadsheet, you could quickly find the problem by checking the number of hours Gettle is scheduled to work each day. But what about a spreadsheet with thousands of rows and columns? Even if the spreadsheet is simple, wouldn't it be nice if the mistake just jumped out at you?

One way to find the problem is to employ a basic spreadsheet strategy: Total the number of hours worked each day. Scheduling five employees a day requires seven employees to operate your shop eight hours a day, seven days a week. The total man-hours per day should be 40. Applying the same conditional format to the man-hour totals makes it easy to pinpoint the day that's in violation: Gettle is scheduled to work Saturday, after already putting in a 40-hour week. Now, this overtime might be filling a valid need, but it still violates a rule. How you handle the overtime is strictly up to management.

february2008blog6figcr.jpg

You might think you're done, but you can go a step further. What happens if someone is scheduled to work a few hours of overtime on a specific day? For instance, you might schedule someone to work 10 hours instead of 8. You might expect the man-hour totals to catch that error, but that's not necessarily true. If someone else works fewer than eight hours the same day, the man-hour total could equal 40 or even less than 40. As you can see below, Gettle is scheduled to work 10 hours on Thursday and Jones is scheduled to work six. The man-hour total is still 40.

february2008blog6figdr.jpg

Overcome this little slip through the cracks by setting a conditional format to highlight any daily value that's less than or greater than eight.

february2008blog6figer.jpg

In this way, you can quickly find the employee(s) and correct the scheduling error. The daily hours for these two employees could be typos or a legitimate need. The key is finding the discrepancies quickly so you can fix the schedule or approve the overtime.

february2008blog7figfr.jpg

This technique is an unusual, yet clever way to find errors and violations that might otherwise go undetected.

You can find the Conditional Formatting options on the Format menu. In Excel 2007, Conditional Formatting is in the Styles group on the Home tab.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks