# Conditional Formatting in Excel 2007 and 2010 Spreadsheets Using Formulas and Icon Sets

*Robbie mostly writes about Skyrim but also occasionally sheds light on the oddities of Microsoft applications such as Excel and Outlook.*

Conditional formatting enables you to visually display differences in your data to allow you to analyse it more easily to illustrate exceptions and highlight trends. It is also invaluable for presenting your data; it is especially useful for creating Red Amber Green (known as RAG) documents from your data.

You can apply a variety of different formats using conditional formatting in Excel 2007 and 2010 including:

- Gradient colours or colour scales
- Data bars
- Icon Sets

In today’s article, we will be working through a number of examples of how to use conditional formatting.

- First we will use an icon set to illustrate whether a football (soccer) club has gone up or down or stayed the same in a league table compared to last week.
- Finally, we will use a formula to highlight cells based on their values. For this example, we will be colour coding web visit and page view data based on a key (Green for greater than 100, Orange for greater than 150 and Yellow for greater than 250).

You can see a number of examples of how to use conditional formatting below using:

- Icon Sets (top left)
- Formulas (top right)
- Check boxes (bottom left)
- Combo boxes (bottom right)

## Conditional Formatting Using Icon Sets

In our first example, we will use conditional formatting on a football (soccer) league table to indicate whether a team has done better, worse or stayed the same compared to their league position in last weeks table.

The league table is created using the **MATCH** function. To find out exactly how I created it, my article can be found here.

To determine if a team has gone up, down or stayed the same compared to last week, I used **MATCH **to find the teams position in this weeks table, (Row Q) and last weeks table (Row R). I then used **IF** statements to give a team a 3 if the went up, 2 if they stayed the same and a 1 if they went down in the table.

To learn more about how to use** IF** statements in Excel, I have written an excellent article that explains the IF function in far greater detail which can be found here.

Now onto the conditional formatting itself. I copied the contents of S3 to S26 to A3:A26. I hid them using a neat trick you can use in Excel to hide the contents of a cell. To accomplish this:

**Right click**on a cell and choose**Format Cells**- Select
**Custom** - In the
**Type field**enter ;;; - Click
**OK**

The contents of the cell will disappear from view!

To apply condition formatting:

- Select A3:A26
- Click the
**Conditional Formatting**button in the**Styles**group on the**Home**tab - Select
**New Rule** - Choose
**Format all cells based on their values** - Under
**Format Style**, pick**Icon Sets** - Configure each Icon as in the figure below
- Lastly, the
**Icon Style**should be**3 Arrows (Colored)**

Before clicking **OK **to apply the formatting, your dialogue box should look like mine below.

Excel will automatically format the selected cells and it will look similar to my table below.

## Conditional Formatting Using Simple Formulas

The ability to use formulas in conjunction with conditional formatting allows you a great amount of flexibility in your formatting. In my example, I want cells that contain:

- A number greater than 100 but less than 149 to be filled green.
- If Excel finds a number greater than 150 but less than 249, the cell is to be filled orange.
- Any cells containing a number greater than 250 to be filled yellow.

The first step is to create our key that explains the colour scheme to users of our spreadsheet. To achieve this:

- Enter in the text in cells B1 to B3 and fill a cell for each row in the appropriate colour in C1 to C3.
- In each of the filled cells, add the lower boundary of the to it, so for green enter 100, orange 150 and yellow 250.
- Hide the contents of each of those cells using the ;;; method outlined above to complete the key.

Next, we move onto the conditional formatting.

**Select**the range of cells you want to format in my example C5 to D40- Click on the
**Conditional Formatting**button - Choose
**New Rule** - From the list, click on
**Use a formula to determine which cells to format** - We will do the formula for yellow first, but you can do the formulas in any order for the three colours
- The formula is =C6>=$C$3

What this asks Excel to do is:

- Compare C6 (the first cell in our range) with C3 (the cell that contains 250 which is the bottom of our third band).
- If it is greater than 250, format the cell according to the rule we set.

**Note:** the $ signs in the formula are essential or Excel will not consistently use C3 for the comparison and the conditional formatting will not work.

Now we apply the formatting for this rule.

- Click on
**Format** - Select the
**Fill**tab and choose the colour (in this case yellow) - Click OK twice to get back to
**Conditional Formatting Rules Manager**

Repeat both sets of instructions above (to create the formula and also select the formatting of your choice) until you have something similar to mine below.

The final step is to ensure that the order of the rules is correct.

To ensure that Excel does not apply the rules incorrectly, you need to have the **Yellow** rule top, then **Orange** then **Green**.

This is because 173, for example, is greater than 100 and 150, so if you have the rules in the wrong order (for example green above orange), 173 will become green. The below figure illustrates this, as you can see there are no orange filled cells in the range.

**Note:** Use the blue up and down arrows in the conditional formatting Rules Manager dialogue box to order your rules correctly.

With the rules correctly ordered the conditional formatting works perfectly and as expected.

## Using MAX and MIN and Conditional Formatting to Illustrate the Smallest and Largest Values in a Range

As well as using simple greater than and less than formulas for conditional formatting, we can also use functions. In this example, we will be using **MIN** and **MAX** to find the smallest and largest values in our range.

As we did above, we need to create a new rule based on a formula.

We create a new rule using **MAX **to find the largest number and also one using **MIN** to find the smallest number.

The formula we will use is:

=MAX($C$8:$D$42)=C8

What this is asking Excel is if C8 is the biggest value in the range C8 to D42. It will then check all the cells in the range one by one to find the largest and it will be formatted with red fill.

**MIN** works in a very similar way, this time looking for the smallest value, with the formula being:

=MIN($C$8:$D$42)=C8

**Note:** Ensure that the rules are ordered correctly with the **MIN** and **MAX** rules at the top to ensure they are not overwritten (as the **MAX** value will also be above 250 so would otherwise be filled yellow).

If you want to learn more about both the **MIN** and **MAX** functions, I have an article that covers them both in greater depth as well as introducing the **SMALL, LARGE INDEX** and **MATCH** functions. I use this suite of functions to create dynamic top 10 lists and league tables. This article can be found here.

## Conclusion

Conditional formatting in Excel is an excellent way to allow your data to visually tell the user of your spreadsheets a story. In my examples today, I illustrated how football (soccer) teams performed this week compared to this week and banded web site visit data to illustrate how the website performed on that day. Additionally, you can use it to:

- Illustrate a trend
- To point out exceptions

I hope that you have found this article useful and informative and that you are now looking to use it in your spreadsheets to make them even more useful than they were before to the end user. Please feel free to leave any comments you have below.

*This article is accurate and true to the best of the author’s knowledge. Content is for informational or entertainment purposes only and does not substitute for personal counsel or professional advice in business, financial, legal, or technical matters.*

## Comments

**Andrew** on November 06, 2016:

Is it possible to post the actual spreadsheet in your examples instead of pictures? That way, people like me can copy and paste the datasheet into Excel for practice without having to create the data sheet again. Thx

**Robbie C Wilson (author)** on June 07, 2013:

Many thanks for your kind words Zubair. I am glad that you have found my hub useful and informative.

**Zubair Ahmed** on June 07, 2013:

Thank you for sharing this info. These features are really useful I quite recently started using them and find it so good when you are trying to format figures.

I'll have to follow you to develop my skills on using Excel features.

Ta