Conditional Formatting in Excel 2010: Working with the New and Improved Icon Sets and Data Bars
Hi and welcome to my latest hub on Excel. Today, I look at a favourite feature of mine in Excel, Conditional Formatting. It allows you to format your data based on the value of the cells or as the result of a formula. There are three different ways you can visually enhance your data using Conditional Formatting. These are:
- Icon sets, such as arrows, shapes, flags and a number of other icons
- You can format cells based on their values or the results of formulas
- Using Data bars you can show how the data changes over the range
I have illustrated them all below:
Excel 2010 has taken one of the coolest additions to Excel 2007, the Icon Sets and both improved and expanded them. As this is one of my favourite features in Excel and one that I use frequently, we will start there.
Improvements to Conditional Formatting Using Icon Sets in Excel 2010
Microsoft, when designing and building Excel 2010 have made a number of improvements to the Icon Sets in Conditional Formatting.
The First Improvement is the Expansion of the Number of Icon Sets Available.
While not revolutionary in itself, it is always nice to have more choice. You can see from the figure below, the Icon Sets available in Excel 2007 are on the left and those that are included in Excel 2010 are shown on the left.
The Second Improvement is the Ability to Suppress the Contents of the Formatted Cells Automatically.
To best demonstrate this, we need to take a step back and examine how to use Icon Sets in Excel.
The first component is a formula that generates a number based on whether the previous value is lower (0), the same (1) or higher (2). I then use those results for the rule which governs which of the icons to use.
The formula I use contains nested IF statements (for example, =IF(I5>I4,2,IF(I5=I4,1,IF(I5<I4,0))) )
To learn more about how to configure and use the powerful IF statement in formulas, as well as the operators, AND, OR and NOT I have a hub that explains how to use this formidable function in greater depth which can be found here:
The second component is a Conditional Formatting rule. To create a rule:
- Select the cells containing the results of the formula
- Choose the Conditional Formatting button which is in the Styles Group on the Home tab
- Hit New Rule
- Select Format all cells based on their values
- Choose your Icon Set from the drop down list and configure the values as shown below:
If you would like to learn even more about using Conditional Formatting in Excel 2007 and Excel 2010, I have a hub that goes into both in far more detail, which can be found here:
The results of the rule and the formula working together can be seen below:
You can see that the results of our formula can be seen next to the arrow. This is clearly not what we want and will also confuse the readers of your spreadsheets. To suppress everything except from the icon sets from appearing in these cells:
- Select the Conditional Formatting button once more and then select Manage Rules
- Pick one of your rules and click Edit Rule
- In the middle of the Edit Formatting Rule dialogue box check the Show Icon Only box
- Click OK twice to return to Excel
You will notice that the numbers are now suppressed! To quickly do this to your remaining cells containing Icon Sets:
- Select a cell with the number hidden
- Using the Format Painter in the Clipboard group on the Home tab, change all of your other cells with Icon Sets so that they also hide the results of the formulas
The Third and I Think the Coolest Improvement to Icon Sets is the Ability to Mix and Match Icons from Different Sets.
To illustrate this:
- Click on the Conditional Formatting button once more
- Pick Manage Rules
- Edit one of the rules that you have just created by selecting it and pressing Edit Rule
As well as being able to choose the Icon Style as you could in Excel 2007, you can also select an individual Icon for each of the different states you want to illustrate (in our case, higher, lower and no change)
For example, I am not a fan of the orange sideways arrow from the Three Arrows (coloured) Icon set. So I decide to swap it out, and I select a yellow dash from the Three Triangles Icon set.
This not only gives you far more freedom, it also vastly increases the combinations available to you and also more importantly allows you to choose an icon that best illustrates what is happening to your data. Users of your spreadsheet know instinctively exactly what the three icons mean and this makes them very powerful devices indeed.
Improvements to Conditional Formatting Using Data Bars in Excel 2010
In addition to the changes made to Icon Sets, Microsoft has also made a number of changes to Data Bars to make them not only more functional and powerful than those in Excel 2007, but also more flexible.
The First and Most Significant Improvement Is the Way That Excel 2010 Displays Negative Numbers in Data Bars.
To illustrate this, I have taken the average temperatures for three Canadian cities and added Data Bars:
You can see in the data for Montreal and Jasper, the negative values are placed to the left of a zero axis and they are coloured red. This is a massive improvement over Excel 2007. The data is now so much clearer and tells the reader far more about the range of temperatures as those negative numbers pop off the page in Excel 2010.
To add Data Bars to a range of data in Excel 2010 and Excel 2007
- Select your data
- Select the Conditional Formatting button and the Data Bars
- Rather than selecting one of the default bar sets, use More Rules as it gives you far more freedom and choice
- Click the Negative Value and Axis button
Here, you can also change the Fill color, the Border color (provided your positive values use a border and also change Axis Settings such as position and colour.
Which brings us to the last improvement in Data Bars in Excel 2010.
The Second Improvement is the Ability to Change More Aspects of Data Bars Than in Excel 2007.
The freedom to modify the fill type, border and axis position and colour allow you much more scope than was present in Excel 2007 to present your Data Bars in a far more impactful and useful way.
You can see my fully configured Data Bars below:
In Excel 2010, Microsoft has taken Conditional Formatting which was already very good in Excel 2007 and enhanced it, so that it becomes even more potent. The improvements they have made include:
- Increasing the number of Icon Sets available
- Automatic suppression of a cells content is now available (particularly useful with Icon Sets)
- The ability to mix and match icons from different Icon Sets
- Negative numbers when using Data Bars now appear in a different colour
- Data Bars use an axis to separate negative and positive numbers
- Borders and fill types are now configurable when using Data Bars
The improvements that most excite me are the ability to mix and match icons and also the ability to have negative and positive numbers differently coloured while using Data Bars.
All of these improvements allow you to make data pop from the page even more than in Excel 2007 and also to tell users of your spreadsheets even more about the data they are looking at. I hope that you have enjoyed reading this hub as much as I have enjoyed writing it. Please feel free to leave any comments you may 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.
© 2013 Robbie C Wilson