Cell PhonesComputersConsumer ElectronicsGraphic Design & Video EditingHome Theater & AudioIndustrial TechnologyInternet

Guide to the Improvements to Conditional Formatting Icon Sets and Data Bars in Excel 2010

Updated on August 8, 2016

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:

Conditional Formatting in Excel 2010, using Icon Sets.
Conditional Formatting in Excel 2010, using Icon Sets. | Source
Conditional Formatting in Excel 2010, using a formula to format cells based on their value.
Conditional Formatting in Excel 2010, using a formula to format cells based on their value. | Source
Negative and Positive values illustrated using Conditional Formatting with Data Bars in Excel 2010.
Negative and Positive values illustrated using Conditional Formatting with Data Bars in Excel 2010. | Source

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.

Conditional Formatting Icon Sets available in Excel 2007 (left) and Excel 2010 (right)
Conditional Formatting Icon Sets available in Excel 2007 (left) and Excel 2010 (right) | Source

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:

http://hubpages.com/hub/Using-the-IF-and-IFERROR-functions-as-well-as-the-logical-functions-AND-OR-and-NOT-in-Excel-2007-and-Excel-2010

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:

Configuring a Conditional Formatting rule using Icon Sets in Excel 2010.
Configuring a Conditional Formatting rule using Icon Sets in Excel 2010. | Source

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:

http://robbiecwilson.hubpages.com/hub/Conditional-Formatting-in-Excel-2007-and-Excel-2010-spreadsheets-using-Formulas-and-Icon-Sets

The results of the rule and the formula working together can be seen below:

An example of Conditional Formatting using Icon Sets in Excel 2010.
An example of Conditional Formatting using Icon Sets in Excel 2010. | Source

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

Select the Show Icon Only box to hide numbers in the cells used to display Icon Sets in Excel 2010.
Select the Show Icon Only box to hide numbers in the cells used to display Icon Sets in Excel 2010. | Source
An example of cells containing Icon Sets with numbers hidden in Excel 2010.
An example of cells containing Icon Sets with numbers hidden in Excel 2010. | Source

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.

In Excel 2010, you can now mix and match Icon Sets in Conditional Formatting.
In Excel 2010, you can now mix and match Icon Sets in Conditional Formatting. | Source

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:

Comparison of how Excel 2007 (left) and Excel 2010 (right) display negative numbers using Conditional Formatting Data Bars.
Comparison of how Excel 2007 (left) and Excel 2010 (right) display negative numbers using Conditional Formatting Data Bars. | Source

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:

Conditional Formatting Data Bars configured to take advantage of all the configurable options available in Excel 2010.
Conditional Formatting Data Bars configured to take advantage of all the configurable options available in Excel 2010. | Source

Conclusion

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.

Which of the improvements to Conditional Formatting in Excel 2010 do you like the most?

See results

© 2013 Robbie C Wilson

Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.

    Click to Rate This Article