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.
We’ll want to mark the products that are low in stock (under 15) so a re-order can be done.
1. Start by selecting the rows you want to apply formatting to.
2. Let’s open the Conditional Formatting menu, and open a New Rule
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.
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.
Under Font, select the color white.
Press OK twice. You should now see your cells colored:
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