This confirms the formula is correct and you can use that formula to create the conditional formatting rule. As you can now see, you get the correct TRUE or FALSE value depending on the value in the corresponding cell.As you drag the formula across, the column part of the cell address will change accordingly – from Column C to D to E to F. Then, drag the highlighted range of cells across four columns to test to see if the cells in Columns C to E have a value greater than 5.Then, use the handle to drag that formula down to Row 11, noting that the row part of the cell address (i.e., 3) will change to the next number as you drag the formula down through the rows so B3 becomes B4, then B5 and so on.Starting with a simple formula, you can see that B3 has a value of 5 and is therefore NOT greater than 5.

Start with the first cell in the range – in this case B3, and you can then test each row and column of the range – down to E11.In the example above, you wish to write a formula to see if each cell in the worksheet is greater than 5 and if it is greater than 5, you wish to color the background of the cell. Prior to creating the conditional formatting rule, you can create the formula in a free space in the workbook and make sure the formula works correctly. When applying a custom formula to conditional formatting in Excel, it is important to make sure that the formula actually returns the correct TRUE or FALSE value in your worksheet for the conditional formatting to work correctly. This tutorial will demonstrate how to test formulas before applying them to Conditional Formatting in Excel.

Format date cells in excel not working