Excel has a strong and often missed feature, called Conditional Formatting, which allows you to format a cell depending on a rule [it’s value]. This can be very useful for worksheets like Product Quantities and Dates. Let me show you what I mean.

Lets start off with a simple chart of products quantities in the warehouse.

conditional formatting

We’ll want to mark the products that are low in stock (under 15) so a re-order can be done.

Let’s Start…

1. Start by selecting the rows you want to apply formatting to.

shade rows

2. Let’s open the Conditional Formatting menu, and open a New Rule

format based on cell value

3. Here, we define our formatting rules. We’ll define cells that their value is less than 15. Copy the following form –  Do not click “OK” yet.

format if cell error

4. Now after we’ve defined our formatting rule, we need to choose the formatting options to use on those cells:

– Click the “Format” button in the previous form.

Under the Fill tab, select red.

format if

Under Font, select the color white.

alternate row shading

Press OK twice. You should now see your cells colored:

duplicate shading

As we can see, this is very simply and highly effective. There are many custom rules already defined if you don’t want to use “Custom” rules.
Make your errors really stand out with this Conditional Formatting trick.

1. Select the entire sheet

2. On the main menu click Format then Conditional Formatting…

3. Change “Cell Value Is” to “Formula Is”

4. In the box to the right of “Fomula Is” type =ISERROR(A1)

5. Click the Format button…

6. Select the Format tab and pick a color, hit OK, hit OK

Leave a Comment