Use Microsoft Excel’s "Conditional Formatting"

Just follow these steps: 

• Select cell A1.
• Choose Conditional Formatting from the Format menu. Excel displays the Conditional Formatting dialog box.
• Set Condition 1 so that Cell Value Is equal to =TODAY(). Note that you must, in the right-most box, enter an equal sign followed by the TODAY() function.
• Click on the Format button. Excel displays the Format Cells dialog box.
• Make sure the Patterns tab is selected.
• Choose the red as the  colour that you want to use and close the Format Cells dialog box.
• Click on the Add button.
• Set Condition 2 so that Cell Value Is Between =TODAY()-7 And =TODAY()+7. Note that you must use equal signs in the two right-most boxes, otherwise Excel can't figure out that you are entering a formula.
• Click on the Format button. Excel displays the Format Cells dialog box.
• Make sure the Patterns tab is selected.
• Choose yellow as the colour that you want to use and close the Format Cells dialog box.
• Click on the Add button.
• Set Condition 3 so that Cell Value Is Between =TODAY()-14 And =TODAY()+14. Again, you must include the equal signs to indicate you are entering formulas.
• Click on the Format button. Excel displays the Format Cells dialog box.
• Make sure the Patterns tab is selected.
• Choose the green as the colour that you want to use and close the Format Cells dialog box.
• Click on OK.

One important thing to bear in mind with conditional formatting is that you can have up to three criteria and different formats that will be applied if those criteria are met. Once a criterion has been met, then the formatting is applied and other criteria are not tested. It is therefore important to set out the tests in the correct order. If, in the example above, the criteria had been entered in the reverse order, i.e. test for 14 days, then 7 and then 0, it would have only applied the 14 days format even if the date entered was today. In other words, if the date is today then all three of the tests would have been met so you have to be careful of the order in order to get the result you need.

Hide Error Values

You may wish to hide error values to make your worksheet more presentable. Using a formula, you can hide error values in your spreadsheet quite easily. In the diagram below, you will see a sheet containing some error values.

In column D there is a simple formula which you used to divide the two columns B and C. As a result, all the other rows where there is a zero in column C will display the error value:

  #DIV/0.

By extending the formula, you can determine which values or content are to be shown. The basic structure of such extended formulas is:

=IF(ISERROR(<Formula>);<Output>;<Formula>)

·         With <Formula> you type in the formula in its original form.

·         <Output> determines what is to appear if there is an error value. You can e.g. type in a value, text, another formula or a blank,

Let’s say you want to type in a blank instead of a possible error value. Just customise the formula for the cell D4 as follows:

=IF(ISERROR(B4/C4),"",(B4/C4))

Now to copy this formula down you can select D4, then simply hover your cursor over the bottom right corner of D4 until it changes to a +, then right click and drag down.

However, remember that hiding errors may not always be advantageous. If you had used the ISERROR Function to start with, you may never have discovered the flaw in your formula.

There is also another down side, if your worksheet contains many of these formulas you could reduce calculation speed because each formula is in effect calculated twice.

If this is a problem then you can also hide errors with Conditional Formatting. First you select D4, then click Format Menu > Conditional Formatting, in the Formula is box you type:

 =ISERROR(D4).

Then select a Font format colour to match your background, usually White. Copy D4 and paste into the other cells of the Column.

Note: this article will be added to later