Use a formula to trigger Excel's Conditional Formatting feature

If you maintain inventory in an Excel worksheet, you probably need to know when stock runs low so you can reorder. Fortunately, you can let Excel warn you when an item is running low by applying a conditional format. You’ll need at least two values: the current inventory and the reorder level.The simple worksheet (below) tracks the current inventory for three items and each item has a reorder amount. There are at least three ways Excel can alert you when inventory is running low for each item:

• Highlight Current Inventory when it is less than the Reorder Level.
• Highlight Item when Current Inventory is less than the Reorder Level.
• Highlight the entire row when Current Inventory is less than the Reorder Level.

To highlight Current Inventory, do the following:

1. Select cell B2 and choose Conditional Formatting from the Format menu. When applying this to your own worksheet, select the first value in the column (not the column’s label text).
2. In the resulting dialog box, choose Formula Is from the first control’s dropdown list.
3. Next, enter the following formula, =B2<=C2. In other words, when the value in B2 is less than or equal to the value in C2, apply the format.
4. Click the Format button and select red from the Patterns tab, and click OK.

   

5. Click OK to close the Conditional Formatting dialog box.

   

6. With cell B2 still selected, click Format Painter.
7. Select cells B3..B4 to apply the conditional format to the remaining items.

When the current inventory dips below (or is equal to) the reorder amount, Excel highlights that cell. With a quick glance, you can determine which items to order.

To highlight Item instead of Current Inventory, simply select cell A2 in step 1. You can highlight the entire row by selecting the entire row (A2..C2) in step 1. In step 3, enter the formula =$B2<=$C2. Then, in step 9, be sure to select the entire rows (A3..C4) when copying the conditional format.