Conditional formatting with Data Bars in Microsoft Excel 2007
Following on from the article discussing simple conditional formatting in Excel, this blog shows you how to add Data Bars to your lists for that extra bit of pazazz!

Posted by Michael Allsop on 19 October 2011

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Using Data Bars in Microsoft Excel 2007

One of the new features in the latest (2007 and 2010) versions of Microsoft Excel is the opportunity to add graphics to cells by applying conditional formatting.

Building on my earlier blog looking at basic conditional formatting, this short blog will focus on the new data bar options.

Our starting point will be the same as in the above article, a short list of numerical data, an extract of which is shown below:

Extract from an Excel spreadsheet

'Traditional' conditional formatting would be to apply a red, yellow or green fill to the background of the cells. However, we can now use:

So, let's create some data bars in the background of our data:

Data Bars applied to numerical data

By default, Excel picks up on the lowest and highest values within the range and makes these the shortest and longest bars. So, depending on what highest and lowest values you have, the relative representation of other values will change.

If you'd rather have the shortest and longest bars set on a pre-defined scale, such as on a column chart, then follow the steps below:

  • Make sure you've still got all the affected cells selected
  • Click on Conditional Formatting -> Manage Rules...
  • Double click on your Data Bar rule, and make the changes below:
The Edit Rule dialog box

In my example, I will set the Longest Bar to 50.

Once you have made the changes, click on OK on the Edit Rule... dialog box, then click OK or Apply on the Conditional Formatting dialog box and your edited rule should now be in place!

 

This blog has 0 threads Add post