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:

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

• Data Bars - which create a mini chart behind the data
• Color Scales - shade from 'hot' to 'cold'
• Icon Sets - pre-formatted traffic light icons or other representations of high to low values

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

• As with any formatting, select all the cells that you want to include within the conditional formatting rule. Then, on the Home tab, click on Conditional Formatting, hover over Data Bars and pick your preferred colour.
• The result should be like the image on the left. However, there are some adjustments that we may wish to make to this new conditional formatting rule...

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:

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!

Comments on this blog

This blog currently has no comments.