Conditional Formatting in Excel 2007 and Excel 2010 Spreadsheets Using Formulas and Icon Sets
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)
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.
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 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
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 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:
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
MIN works in a very similar way, this time looking for the smallest value, with the formula being
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 ten lists and league tables. This article can be found here.
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.
More by this Author
- 0Use the Rank and Percentile Tool from the Analysis ToolPak in Excel 2007 and Excel 2010 to create ranking tables
The Excel 2007 / 2010 Rank and Percentile tool allows you to create ranking tables. In this hub we use Rank and Percentile with MATCH, INDEX and IF functions to create a fully automated ranking table.
In Excel 2010, Conditional Formatting Icon Sets and Data Bars have been greatly improved. Icons Sets can now be mixed and matched and Data Bars show negative values on an axis in a different colour.
If your data range contains many blank cells, it can be hard to correctly create a chart. Use the IF function to convert blank cells to #N/A which Excel ignores or change how Excel deals with blanks.