Simple Conditional Formatting (Excel 2010)

Conditional formatting in Excel has many useful purposes. I normally use it to alert or warn me if a certain condition is met (or otherwise). Let me get started on how I use this feature in a spreadsheet that I have developed to monitor the status of a data migration project.

Take a look at this snapshot:

conditional1

In cell G3, I have set the rules to change the format of the cell based on the value that was read from other cells. But in order to simplify matters in this tutorial, the cell will change based on the value that is entered.

Here are the conditions that I have come up with:

Condition A

If the value is more than 71%, then do this:

  • Cell fill color = Green
  • Font color = Black
  • Font type = Bold

Condition B

If the value is between 41% and 70%, then do this:

  • Cell fill color = Orange
  • Font color = Black
  • Font type = Bold

Condition C

If the value is less than 40%, then do this:

  • Cell fill color = Red
  • Font color = White
  • Font type = Bold

Now that I have the conditions in place, I need to set the rules for the cell to act accordingly.

1.  Select the cell for the rules to be applied to. Well, in this tutorial, I have selected cell G3.

2.  On the Home tab, in the Styles group, click the Conditional Formatting button. From the submenu that appears, select New Rule.
conditional2

3.  The New Formatting Rule dialog box appears. Under Select a Rule Type, click Format only cells that contain.
conditional3

You will notice that the Edit the Rule Description area changes according to your selection.

4.  To set the rules for Condition A:

a.  Select greater than from the second drop-down list box.

b.  Type =71% in the text box.

c.  Click the Format button.

conditional4

d.  The Format Cells dialog box appears. In the Font style box under the Font tab, select Bold. Note that by default, the font color is black, so you don’t have to select the font color in this case.
conditional5

e.  Click the Fill tab and choose green from the Background Color palette. Click the OK button.
conditional6

f.  You will return to the New Formatting Rule dialog box and you can see how your formatting will look like in the Preview section. Click the OK button.
conditional7

5.  You will return to your spreadsheet. Now let’s test to see if it works as intended. Type 88% into cell G3 and press the Enter button.
conditional8

Voila! The cell turns green and the value is in bolded font.

6.  To proceed with Condition B, make sure that cell G3 is still selected. Repeat Step 2 and Step 3 above.

7.  To set the rules for Condition B:

a.  Select between from the second drop-down list box.

b.  Type =41% and =70% in the first and second text box respectively.

c.  Click the Format button.

conditional9

d.  The Format Cells dialog box appears. In the Font style box under the Font tab, select Bold. Note that by default, the font color is black, so you don’t have to select the font color in this case.
conditional5

e.  Click the Fill tab and choose orange from the Background Color palette. Click the OK button.
conditional10

f.  You will return to the New Formatting Rule dialog box and you can see how your formatting will look like in the Preview section. Click the OK button.
conditional11

8.  You will return to your spreadsheet. Now let’s test to see if it works as intended. Type 56% into cell G3 and press the Enter button.
conditional12

Voila! The cell turns orange and the value is in bolded font.

9.  To proceed with Condition C, make sure that cell G3 is still selected. Repeat Step 2 and Step 3 above.

10.  To set the rules for Condition C:

a.  Select less than from the second drop-down list box.

b.  Type =40% in the text box.

c.  Click the Format button.

conditional13

d.  The Format Cells dialog box appears. In the Font style box under the Font tab, select Bold. Then, select white from the Color palette.
conditional14

e.  Click the Fill tab and choose red from the Background Color palette. Click the OK button.
conditional15

f.  You will return to the New Formatting Rule dialog box and you can see how your formatting will look like in the Preview section. Click the OK button.
conditional16

11.  You will return to your spreadsheet. Now let’s test to see if it works as intended. Type 32% into cell G3 and press the Enter button.
conditional17

Voila! The cell turns red and the value is in white, bolded font.

Try this tutorial with other simple conditions that you can think of. Enjoy!

Leave a Reply

Your email address will not be published. Required fields are marked *