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.

4 comments
Tink!
Tink!

what if you would like to do this type of thing, but only when a cell changes? I have a user that would start editing a sheet, walk away, and then forget if he made changes or not. To alleviate this, I created a macro using the Workbook_SheetChange event to make the text change color in the cell he just changed. The only problem with this is you can't use UNDO. It would be great if there was a way to utilize this Conditional Formatting option but ON Change instead of based on numeric comparisons.

mharris672
mharris672

Conditional Formatting is great, but it seems that you must set the condition for each cell independently (at least in Excel 2003). I'd like to set the condition in the top cell, then copy & paste it into the next 100 cells below. After doing so, I've still got to go to each cell and adjust the condition. Also, if you insert or delete rows, the action may disrupt your string of conditional formatting. Is this fixed in Excel 2007? Thanks. M.H.

ThumbsUp2
ThumbsUp2

Instead of setting the conditional formatting for one cell and copying that cell down, highlight all cells that you want to apply the conditional formatting to before you set the formatting. That way, if you have to insert a row/column, the conditional formatting is automatically applied.

dueck21
dueck21

I've been using conditional formatting off and on for a few years now, and I've encountered a problem. For some reason the entire spreadsheet's formula for conditional formatting (two of them - one to find duplicates in the first column, and one to find empty spaces in the first column) is working except for about 10 sheets in the middle of the document. I've deleted current formatting and copied from another sheet that works, but nothing seems to fix those worksheets. Does anyone have any ideas..... And to make a statement to the above post, That is a little harder when you are copying the format to around 50 worksheets in a workbook.... Format painting generally works a bit better and quicker...