BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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:
'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:
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!