Cell PhonesComputersConsumer ElectronicsGraphic Design & Video EditingHome Theater & AudioIndustrial TechnologyInternet

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

Updated on September 21, 2016

Introduction

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 Excel 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).

In my second article on conditional formatting, we will be using it in conjunction with check boxes and combo boxes to create to do lists. This article can be found here.

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) and finally
  • Combo boxes (bottom right)

Examples of conditional formatting, created using Excel 2007 or Excel 2010.
Examples of conditional formatting, created using Excel 2007 or Excel 2010. | Source
Examples of the Icon Sets available in Excel 2007 and Excel 2010.
Examples of the Icon Sets available in Excel 2007 and Excel 2010. | Source

Conditional Formatting using Icon Sets in Excel 2007 and Excel 2010

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 and 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.

Football (soccer) league table created using the MATCH and IF functions in Excel 2007 or Excel 2010.
Football (soccer) league table created using the MATCH and IF functions in Excel 2007 or Excel 2010. | Source

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!

How to hide a cells contents in Excel 2007 and Excel 2010.
How to hide a cells contents in Excel 2007 and Excel 2010. | Source

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)

Configuring conditional formatting using Icon Sets in Excel 2007 or Excel 2010.
Configuring conditional formatting using Icon Sets in Excel 2007 or Excel 2010. | Source

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

Conditional Formatting Rules Manager showing the rule just created using Icon Sets in Excel 2007 or Excel 2010.
Conditional Formatting Rules Manager showing the rule just created using Icon Sets in Excel 2007 or Excel 2010. | Source

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

Football (soccer) league table using an Icon Set to show a team's position compared to its position last week created in Excel 2007 or Excel 2010.
Football (soccer) league table using an Icon Set to show a team's position compared to its position last week created in Excel 2007 or Excel 2010. | Source

Conditional Formatting Using Simple Formulas in Excel 2007 and Excel 2010

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) and
  • 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

Rules created with conditional formatting using formulas in Excel 2007 or Excel 2010.
Rules created with conditional formatting using formulas in Excel 2007 or Excel 2010. | Source

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:

Ensure that your conditional formatting rules are ordered correctly otherwise Excel 2007 or Excel 2010 may display inconsistent results.
Ensure that your conditional formatting rules are ordered correctly otherwise Excel 2007 or Excel 2010 may display inconsistent results. | Source

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.

The Conditional Formatting rules ordered correctly, shown using Excel 2007 or Excel 2010.
The Conditional Formatting rules ordered correctly, shown using Excel 2007 or Excel 2010. | Source

Using MAX and MIN and Conditional Formatting to Illustrate the Smallest and Largest Values in a Range in Excel 2007 and Excel 2010

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:

Is C8 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

How to use the MAX function in a formula to create a conditional formatting rule in Excel 2007 or Excel 2010.
How to use the MAX function in a formula to create a conditional formatting rule in Excel 2007 or Excel 2010. | Source

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).

Conditional Formatting Rules Manager showing the rules (in the correct order) created using the MIN and MAX function in a formula in Excel 2007 or Excel 2010.
Conditional Formatting Rules Manager showing the rules (in the correct order) created using the MIN and MAX function in a formula in Excel 2007 or Excel 2010. | Source
Results of the above formulas shown illustrating the best and worst days in Excel 2007 or Excel 2010.
Results of the above formulas shown illustrating the best and worst days in Excel 2007 or Excel 2010. | Source

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 ten 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.

Comments

    0 of 8192 characters used
    Post Comment

    • profile image

      Andrew 4 months ago

      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

    • profile image
      Author

      Robbie C Wilson 3 years ago

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

    • Zubair Ahmed profile image

      Zubair Ahmed 3 years ago

      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

    Click to Rate This Article