Conditional Formatting for Process Improvement.
What is it, how it works, and why you should care.
© Copyright. Content on InventoryOps.com is copyright-protected and is not available for republication.
From the InventoryOps Glossary: Conditional formatting—formatting of data on reports or computer programs that changes based upon specific criteria. Examples of conditional formatting would include printing and displaying negative quantities in red, only printing the unit of measure if it is not "eaches," or printing an item number in reverse text if there are special handling instructions associated with the item. Conditional formatting is used to help focus attention on data elements when the importance of the data element may be different from one record to the next.
Conditional formatting involves changing the presentation of data based on specific conditions. While commonly associated with functionality in Microsoft Excel and other spreadsheet programs, conditional formatting is far more than a spreadsheet tool. In fact, as an inventory management and warehouse operations consultant, I regularly provide process-improvement recommendations that include conditional formatting; and rarely does this involve a spreadsheet program (more on this later).
That said, I will start off with some spreadsheet examples just to help demonstrate the basic concept of conditional formatting. And for the first example, I will cover the most common use of conditional formatting. And many of you may be surprised to find out you've been using conditional formatting for years without ever knowing it. That's because this particular application of conditional formatting is part of an automatic Excel format function. Have you figured it out?
Yes, I'm talking about the formatting of negative numbers or negative currency values. Whenever you bring up the formatting options for numbers or currency in Excel, you are presented with several built-in formats for negative values. You can automatically display negative values with a minus sign, within parentheses, in the color RED, or a combination of these. This is all conditional formatting because you are changing the presentation of the data based on a condition. In this case, the "condition" is a value that is less than zero.
Note: this is not Excel's Conditional Formatting functionality, but rather it is a built-in Formatting Element that demonstrates the concept.
These negative-value preset formats in Excel actually tell us a lot about conditional formatting. First, the fact that there are several preset formats for negative values tells us something. The minus sign is most easily understood, but may not exactly jump out at you on the page. Using parentheses makes the negative values stand out more, and using the color RED makes the negative values stand out even more. RED has become the standard for on-screen displays or reports printed on color printers, but parentheses are still the most versatile since they do not require color displays or color printing to be effective. So you can see how the method of displaying the information will affect formatting choices.
The reason these negative-value formatting options are so common is that, in business, negative values are often considered a bad thing. Executives and top management need to know about these "bad things" but typically don't want to make an effort to actually read through the details of the financial reports provided to them. And since RED is the color of the Devil's skin, and therefore inherently represents things that are bad, it would only seem natural to choose that color to highlight "bad things" on the financial reports so management can quickly find someone to fire in order to avoid being blamed for these bad things. In fact, if you've ever heard the phrase "seeing red" used to describe someone who is very angry, it actually originated from the use of RED in conditional formatting of negative values on financial statements.
Well, that last paragraph certainly took a strange turn. And if you're gullible enough to believe all that, you probably shouldn't be reading stuff on the internet.
Back to reality, using conditional formatting for negative values on financial reports is an excellent example of where conditional formatting makes sense. For those using these financial reports to make business decisions, not noticing a negative value on the report could easily result in a failure to take a necessary action.
So, you may ask, where would I use conditional formatting other than Excel? Basically, any place where information (data) is presented and you. In warehouse operations, this includes bar code labels, any warehouse or production documents used, or screens on portable devices.
The key to effectively using conditional formatting is to use it sparingly. It's similar to all those "Are you sure?" popup windows that are supposed to prevent you from doing something stupid. When a computer program is giving you popup windows for everything, users quickly get in the habit of just blowing by them without thinking about why they are getting a warning. Similarly, with conditional formatting, you need to save it for things that are very important. For example, in many e-commerce order-picking operations, the most common pick quantity is one (1). Not only is it the most common pick quantity in these operations, but it's highly unusual to get a pick quantity other than one. In a situation like this, order pickers are likely to just pick one, even if the quantity is for multiple units, resulting in frequent picking errors. This is a case where you want to make a pick quantity that is not one, jump out at the order picker. On paper picking documents or labels used for picking, you could dramatically increase the font size if the quantity is greater than one; on the screen of a handheld portable computer used for picking, you could make the quantity RED and a larger font if it is greater than one, or you could make it flash if greater than one.
If you use those same formatting techniques for other conditions, it lessens the effect on the user. Again, these are situations where you want the information to "Jump Out" at the user. If you are in an operation that has a lot of quantity one picks, but not to the level I just described, I wouldn't do this. If picking quantities greater than one is a frequent situation, your pickers will probably be fine with it because paying attention to the quantity to pick is important for every pick.
Other things you could use this on in the warehouse would include hazardous materials, unusually heavy items, special units of measure, items that are sold in sets, items requiring special equipment to move, non-conveyable items, or orders that have special packing instructions or other unique requirements. I often see operations where they print special shipping instructions on the order, but over the years, almost all orders have gotten something added to the shipping instructions, and most of them are just ignored because a lot of unimportant stuff has ended up in that area. This one is a little more complicated than just conditional formatting, I would still use special formatting to make that information stand out, but you really need to clean up all that junk that ends up in the area on the shipping documents.
Determine the Capabilities of the Tool You Will Use.
Some programs have limited functionality when it comes to conditional formatting. For example, Excel doesn't allow you to change the Font or Font Size. I have no idea why Excel has this limitation, but it's a good example of why you need to first see what you can do before you make your plans. So Excel does let you change the color of the font, you can Bold it, and you can change the background color. You can still accomplish quite a bit within those constraints.
An Example of Conditional Formatting in Excel
Here is an example of conditional formatting in Excel. It shows 2 versions of a pick list; the first is just standard formatting and the second has some conditional formatting. I wanted to make the quantity stand out if the quantity was not 1. In this case, I ended up using a Green Fill for the entire record (not just the quantity). In addition, I wanted to make the unit of measure stand out if it were not EA, but instead of changing the formatting for anything that was not EA, I changed the format for anything that was EA to White, so it basically did not show on the screen or on paper.
Here is what the conditional formatting rules look like. I use Excel a lot, but their Conditional Formatting set up is not all that intuitive to me. Whenever I use it, I end up doing a lot of trial and error until I get something that works.
More Articles by Dave Piasecki.
© Copyright. Content on InventoryOps.com is copyright-protected and is not available for republication.
Dave Piasecki, is owner/operator of Inventory Operations Consulting LLC, a consulting firm providing services related to inventory management, material handling, and warehouse operations. He has over 25 years experience in operations management and can be reached through his website (https://www.inventoryops.com), where he maintains additional relevant information.
