Applying Conditional Formatting

Applying Conditional Formatting

Conditional Formatting is formatting applied when certain conditions are met – thus the name.

For example, if you want to be alerted to low levels of stock on a given product, you can set a formatting rule to help you spot things you need to reorder.

Here, in our Inventory sheet, in the Quantity in Stock column, I can use a Conditional Formatting preset, found through the Conditional Formatting button on the Home tab – which applies a light red fill with dark red text to flag products dipping below 50 items in stock. I can set the numeric threshold and choose from multiple presets for the way the formatting will appear when the conditions are met.

I can also choose the Custom Format command from that same menu to apply whatever formatting I think will catch my eye, given the rest of the sheet’s existing formatting – creating a totally custom response to changing numbers on my worksheet.

You can also ask Excel to rank or otherwise distinguish your numeric values in a range of cells by applying shading, solid color fills, or inserting a shape at the left end of the cells in question.

Here, I’ll apply each one of those options to the same range of cells on my Inventory sheet. Any one of them will make me take note of numbers that are too high, too low, or just right.

On my Inventory Sheet, using the gradient and then solid color fills make my Over/Under numbers – indicating items we’re really low on or have way too much of – really stand out. So things we’re about to run out of appear in a dramatic red and things we need to sell to get rid of excess inventory appear in green.

Applying Icon Sets also creates a very helpful visual.

You can apply these preset graphics to sales numbers, test results, ratings, rankings – any numbers that you want to stand out.

Switching to my Widget Product list, we can see that you can also apply Conditional Formatting for text such as a product or person’s name wherever it appears in a selected range of cells. Here, I’ll use the Text That Contains option, and choose to highlight all the “Clear” widgets.

In addition to the presets for greater-than and less-than options and for top and bottom-ranked numeric items, you can set customized rules, where you choose the specific condition and apply very specific formatting in response.

I’ll use this to format the vendor costs – choosing to format the cells that contain values Less than $3 in green – so I know at a glance that these may be the most profitable widgets for my business.

Note that as your worksheet content evolves over time, the formatting applied through Conditional Formatting will be continuously reapplied, based on the cell value and your rules.

For example, when I switch back to my Inventory sheet and update the quantity-on-hand numbers for a previously low-stock item, the light red fill and the red text goes away, allowing me to focus on more urgent data in the worksheet.