Excel Tip: Using Conditional Formatting to Highlight Cells

by

The last few times I have written a tip about an Excel feature that I use in my practice, the articles have been well received. So I thought I'd share another one with you.

Let's suppose you have an Excel spreadsheet with a series of values in a range of cells. Now, say they are both even and odd numbers that you want to highlight with different colors. Perhaps a greenish color for positive numbers and a pinkish one for negative numbers.

We can use an Excel feature called "Conditional Formatting" to accomplish this.  

Here is an example of an Excel spreadsheet we want to highlight:

Step 1 – Select the cells that contain the odd and even values.

Step 2 – Select "Conditional Formatting" from the Home tab Ribbon.

Excel displays the Conditional Formatting menu.

Step 3 – Select "New Rule..." from the Condition Formatting menu (shown above in the red box of the illustration above). Excel displays the New Formatting Rule dialog box (shown below).

Step 4 – Choose "Use a formula to determine which cells to format" in the upper "Select a Rule Type" window of the New Formatting Rule dialog box (shown in the blue highlight of the illustration above).

Step 5 – In the Edit the Rule Description portion of the New Formatting Rule dialog box, enter the formula (shown in the illustration below) for the "Format values where this formula is true" field.

This formula will yield formatting when the cell value is an even number.

Step 6 – Click the "Format" button. Excel will display the Format Cells window (shown below).

Step 7 – Select the Fill tab of the Format Cells window. Next, choose the color you want for the cells with even numbers. We said even values of our example would be greenish, so I've chosen the color shown in the above illustration. Now click OK. Excel will return you to the New Formatting Rule Dialog Box.

Step 8 – Note that the Preview window of the New Formatting Rule Dialog Box will now display the "greenish" color we selected in the previous step (shown below). Now click OK.

[Your formula should always contains the first cell of the range you actually specify based upon your own unique situation. In my example, that cell just happened to be "F3."]

Excel will have highlighted the cells containing even numbers with the greenish color we selected (shown below).

To highlight the cells containing odd numbers with a pinkish color, just follow the same steps as above:

  • When repeating Step 5, substitute with this formula: =isodd(F3) [Your formula should always contains the first cell of the range you actually specify based upon your own unique situation. In my example that cell just happened to be 'F3'.]
  • When repeating Step 7, select a "pinkish" color rather than the "greenish" one.

When you have finished, your spreadsheet should look something like the one below.

Note: Conditional formatting takes precedent over any formatting you applied to a cell, so even if you try to change the cell color via the toolbar, the conditional formatting takes precedent.

Reminder: Your formula should always contains the first cell of the range you actually specify based upon your own unique situation. In my example, that cell just happened to be "F3."

So, I'm sure some of you are saying to yourself, "Murph went through a lot of steps just to colorize a few cells in this spreadsheet. He could have colored them one at a time just as quickly."

That's quite true in this very limited example. But what if had several hundred cells, laid out in a lot more rows and columns, not just 39? You can quickly see that if you're dealing with a big spreadsheet and you want to easily differentiate even from odd numbers, this little Excel feature can go a long way in helping you accomplish the task in a minimum of time.

And if you client says, "Hey I like the numbers, but these colors are horrible, can you change them for me?" You can easily change them to any color scheme your client (or you, for that matter) wants.