Data analysis is often more about the relationships between values in a range than it is about the values themselves. For example, in a table of sales where one product sells 600 units and another sells 300, you might be interested only in the fact that the former sold twice as much as the latter, regardless of the actual sales. Excel 2007 comes with a new tool that enables you to quickly and easily perform these kinds of relative analyses: data bars. Data bars are colored, horizontal bars that display “behind” the values in a range. Their key feature is that the length of the data bar that displays in each cell depends on the value in that cell: the larger the value, the longer the data bar. The cell with the highest value has the longest data bar, and the data bars that display in the other cells have lengths that reflect their values. (For example, a cell with a value that is half of the largest value has a data bar that’s half as long as the longest data bar.)
To apply data bars to the selected range, choose Home, Conditional Formatting, Data Bars, and then choose the color you prefer. Figure 1 shows a worksheet of product sales, with data bars applied to the values in the Units column.
Excel configures its default data bars with the longest data bar based on the highest value in the range and the shortest data bar based on the lowest value in the range. However, what if you want to visualize your values based on different criteria? With test scores, for example, you might prefer to see the data bars based on values between 0 and 100 (so for a value of 50, the data bar always fills only half the cell, no matter what the top mark is).
Figure 1 A worksheet with Excel 2007’s new data bars applied to the Units column.
To apply custom data bars, select the range and then choose Home, Conditional Formatting, Data Bars, More Rules to display the New Formatting Rule dialog box. In the Edit the Rule Description group, make sure Data Bar displays in the Format Style list. Notice that there is a Type list for both the Shortest Bar and Longest Bar. The type deterÂmines how Excel applies the data bars. You have five choices:
|
One thing you can’t do directly with data bars is apply multiple colors. This is a handy way to, say, display particularly low values in red or particularly high values in green. You can apply different colored data bars multiple times to a given range, but Excel always displays only the most recent data bars.
You can work around this problem by manipulating some range properties via VBA. Each Range object has an associated FormatConditions collection, which holds all the conditional formatting rules that have been applied to the range. If you’ve applied two rules to the range, for example, then the first one you applied is FormatConditions(1) and the second is FormatConditions(2). Each of these returns a FormatCondition object, which in turn has a Formula property.
Technorati Tags: analize cell values, data bars excel
Popularity: 3% [?]
Recent Comments