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

Questions & Answers

    © 2013 Robbie C Wilson

    Comments

      0 of 8192 characters used
      Post Comment

      No comments yet.

      working

      This website uses cookies

      As a user in the EEA, your approval is needed on a few things. To provide a better website experience, turbofuture.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

      For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://turbofuture.com/privacy-policy#gdpr

      Show Details
      Necessary
      HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
      LoginThis is necessary to sign in to the HubPages Service.
      Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
      AkismetThis is used to detect comment spam. (Privacy Policy)
      HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
      HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
      Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
      CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
      Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
      Features
      Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
      Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
      Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
      Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
      Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
      VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
      PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
      Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
      MavenThis supports the Maven widget and search functionality. (Privacy Policy)
      Marketing
      Google AdSenseThis is an ad network. (Privacy Policy)
      Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
      Index ExchangeThis is an ad network. (Privacy Policy)
      SovrnThis is an ad network. (Privacy Policy)
      Facebook AdsThis is an ad network. (Privacy Policy)
      Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
      AppNexusThis is an ad network. (Privacy Policy)
      OpenxThis is an ad network. (Privacy Policy)
      Rubicon ProjectThis is an ad network. (Privacy Policy)
      TripleLiftThis is an ad network. (Privacy Policy)
      Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
      Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
      Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
      Statistics
      Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
      ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
      Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)