Shading Based on Odds and Evens

Let’s assume you have values in a range of cells, and you want to use a different format to distinguish the odd numbers from the even numbers. You can use the Conditional Formatting feature in Excel to accomplish this. Follow these steps:

1. Select the cells with odd and even values.
2. Select the Home tab of the ribbon and click on the Conditional Formatting option in the Styles group. Excel displays a palette of options related to conditional formatting.
3. Click New Rule, and Excel will display the New Formatting Rule dialogue box.
4. In the Select a Rule Type area, select Use a Formula to Determine Which Cells to Format.
5. In the Format Values Where This Formula Is True box, enter the following formula: =MOD(A1,2)=1. This formula returns a True value if the cell contains an odd value. (Replace A1 with the first cell in the selected range)
6. Select Format to open up the Format Cells dialogue box.
7. Select a format that you want to apply to cells that contain an odd value.
8. Click OK to close the Format Cells dialogue box. The formatting you selected in step 7 should now appear in the preview area for the rule.
9. Click OK. Excel applies the conditional formatting to the cells you selected in step 1.
10. Repeat steps 1 through 9 for the second condition, but use the formula =MOD(A1,2)=0. This formula returns True if the cell contains an even value.
11. Click OK. Excel applies the conditional formatting to the cells you selected in step 1.

Once this conditional formatting is applied, odd cells will be one colour and even another colour. If the cell contains text, the conditional formatting will not apply. Conditional formatting takes precedence over any other styles you apply to a cell.

Alan Salmon