When your worksheet has a range of cells with a series of values, you may choose to use a different format to show the difference between the odd and even values. The method to do this is using the Conditional Formatting feature.
Follow the steps below to learn how:
- Select the cells that contain odd and even values.
- On the Home tab of your Ribbon, in the Styles Group, click the Conditional Formatting option.to display a a list of options.
- Click Manage Rules to display the Conditional Formatting Rules manager dialog box.
- Click New Rule to display the New Formatting Rule dialog box.
- At the top of the dialog box, in the Select a Rule Type area, select Use a Formula to Determine Which Cells to Format.
- In the Format Values Where This Formula Is True, key in: =MOD(A1,2)=1. This formula will return True if the cell contains an odd value.
- Click Format to display the Format Cells dialog box.
- Using the options in the dialog box, select a format that you would like for those cells that contain an odd value.
- Click OK to close the Format dialog box and then the formatting you specified above should appear in the preview area for your rule.
- Click OK. The New Formatting Rule dialog box will disappear and Excel will again display the Conditional Formatting Rule dialog box. You will see the rule you just created listed there.
- Repeat the steps above for your second condition but instead, key in: =MOD(A1,2=0. This formula returns True if your cells contains an even value. Click the Up and Down arrows to move the rules you have created to the order in which they should be executed.
- Click Ok and Excel will apply the condition formatting to the cells you selected above.
Once this conditional formatting has been applied, if your cell is odd it will be one color and even will display another color. If your cell contains text, the cell will have no color. The conditional formatting will override any formatting of your cell, so even if you tried to change the cell color using the tools on your Ribbon, the conditional formatting will take precedence.
There is also another function you can use to in your formula. To determine if a cell is odd, you could use this:
To determine an even value, you would use: