Search 75,909 tutors

Conditional Formatting in Excel - "Do that to the same cell range one more time" - with apologies to Captain and Tennille

In Excel, different Conditional Formatting settings in Excel may be applied over the same range of cells.

I created an Electoral College spreadsheet to illustrate this feature to my clients. In one column with the heading “Red or Blue”, either the word “Red” or the word “Blue” is entered depending on whether the state voted Democratic or Republican during the 2012 Presidential Election. I next used Conditional Formatting to fill the cell with a blue-colored background if “Blue” was typed and a red-colored background if “Red” was typed.

To apply the blue or red fill color, I highlighted the range of cells in the column, and selected Conditional Formatting on the Home toolbar. On the popup menu, I selected “Highlight Cells Rules” then “Text that Contains…”. On the next popup form, I typed the word “Blue” in the first box on the left, and chose “Custom Format” from the pull-down list of format options. I selected a medium-shade of blue from the “Fill” tab of the Format Cells popup form. I clicked “OK” twice to close the two Conditional Formatting popup forms. The blue-colored fill was applied within the cells containing the word, “Blue”.

Now, repeat the same steps as above by selecting the same range of cells but typing “Red” where “Blue” was typed before, and choosing a red-color for the fill. When the steps are completed, cells containing the word, “Red” are now filled with a red background.

Try typing the word, “Blue” in a cell containing the word, “Red” and hit the Enter key; the cell’s fill color changes to blue. Retype the word, “Red” in the cell, hit Enter and the fill color changes back to red. Nifty, huh?

Of course, there are other ways to set the fill color based on the value typed in cell, but this is one of the more straightforward solutions.

(For fun, highlight the same cell range a third time, and choose a green-colored fill if the word “Alien” is entered in the cell.)