Data Bars – Feedback Please

Sc1: Show nothing in that cell, but the rest should be proportionate to a lower bound of 0 and and upper bound of 7

Sc2: Show proportionately with lower bound 0 and upper bound 13.  I assume the alternative is a logarithmic distribution, but I prefer to see almost nothing in the '1' cell because that's probably the out lier that I'm looking for.

Sc3:  Lower bound 6, upper 10.  It won't be distinctive enough if you always set your lower bound to zero.

Sc4:

    IIIII

IIIII

    IIIIIII

    III

That is, move zero out into the cell.  Not the middle, but proportionately where it should go with a lower bound of -5 and upper of 7.

Scenerio #1 - perhaps for 0's can be displayed using the opposite color on the color wheel

||(green)

|(red)

|||||||(green)

|||||(green)

Scenario #2 & #3 - I find the current display for these OK, if you expand the columns enough the differences are easier to see. I guess if real estate is an issue this idea doesn't apply.

Scenerio #4 - perhaps for negative numbers can be displayed using the opposite color on the color wheel

|||||(green)

|||||(red)

|||||||(green)

|||(green)

I agree with *** Kusleika

In Excel 2007, data bars are virtually useless because they are not accurate. People are accustomed to seeing charts in which the bars are proportional to the data values.

Why not just use the same algorithm that's used in a bar chart? In addition, allow the user to specify the upper and lower values.

And while your at it, using theme colors for the data bars would be nice.

One thing that I would love to see is that the data bars and icon set not just is relativt to the highest and lowest value in a range, but could display a data bar or icon based on the previous value to track trends. Like

1

2 :)

2 :|

3 :)

2 :(

// Johan

Was going to reply but read Anthony - he is spot on

don't forget that even in your sample picture the data bars are confusing. 3 is the highest value but the data bar doesn't go all the way to the edge of the cell...? wtf? this is the most surprising thing i've found about data bars and I think detracts from their visual usefulness because even more baffling they are drawn with a gradient!

I find it really difficult to scan and understand which data bars are really reaching the maximum - or to disambiguate scenario 3 where the numbers are close to gether because you've removed the most distinguishing characteristic - the color!

If you dropped the gradient, or reversed it it would be much easier for all of the first 3 scenarios.

Anyway my suggestion here for negative numbers use an anti color to the left of a small whitespace gap: here's the extent of the full range - == whitespace:

|||-|||

  -||| for positive numbers (normal color)

|||- for negative numbers (anti color)

I agree with Anthony on the first three scenarios.

But for scenario 4 I would color the background instead of using a databar because it might be confusing with 0 values.

But if you should use a databar for the negative values i have to agree with Frank

From a formatting perspective, any plans to make it easy to assign the data visualization to another cell?  I.e. instead of the bar being visible in the same cell as the data, making it apply to the adjacent cell/column.

Set 1 - 3 (applies to all results) The ability to have user set a boundary (high / low) would allow flexibility of how large the bar is for smaller data sets of varying range/proximity. One could have a radical difference between the bar length for a small data set (1, 3, 4, 9) or have them relatively close for more of a visual ranking.

Set 4 - negative

i'd like to see the midline offset from the edge of the cell, so that a red small bar could come out the left side.  would not have to be the same scale as the others.

Lack of proportionality with data bars can be confusing, if not downright misleading.  In your simple example for instance, the "1" bar should be 1/2 the size of the "2" bar and the "2" bar should be 2/3 that of the "3" bar.

Other general comments:

1) A zero value should show no bar at all, as is the case with most charts and unlike the current behavior.  

2) Negative values: Could be right-justified in the cell.  Centering is also a possibility but then you have less bar to work with to represent positive and negative values.  Could also be a waste if you have a single negative value among otherwise positive values.

3) There should be an option to set a different color for bars with negative values or values outside the mean values of the data set, to easily visualize outliers.  Hmmm, the ability to sort or filter by bar color would be nice...

For a 0, I'd like to have no data bar.  If you choose a range with positive and negative values, I'd prefer negative values go left and positive vals go right.  This would mean the data bar axis would be in the middle of the cell vs. the left.

General:

The main rules for the default behaviour should be easy to interpret and understand, so don't make things too complex. Many times people search for max/min values so a distinction of these would be nice. I suppose we only discuss default behaviour, because all the options and settings I haven't tested yet.. I mean what I suggest could be there under the cover as it always have (at least before 2007..)

Scen 1 Zero should be nothing.

Scen 2 As is in 2007  

Scen 3 Scale so it is visible they are close ie with 0 at the axis.

Scen 4 I believe in putting the axis to the middle as said above.

Overall:

I agree with one comment above, that the functionality should mimic what is available for axis control in a bar chart (e.g. set lower and upper bounds auto or manual, etc.) Another good idea is to allow the formatting to apply to cells other than the target cells, so that the formatting could be adjacent to the actual numbers if desired.

1) No bar for zero values

2) As is

3) As is

4) Negative values go left, positive go right, zero point adjusts dynamically in the cell

The values displayed in a bar chart are proportional to the length of the bars. Violating this convention leads to misunderstanding and misinterpretation of the data.

If all the bars are positive in value, the minimum should be set to zero (and a zero value should have a zero-length bar). If there are negative and positive values, the minimum should be the largest minimum value and the maximum the largest positive value, zero somewhere in between, with the bars reaching to either side of the zero location. It might be useful to offer a contrasting color for the negative bars.

Scenario I:

Scale 0 to 7, with zero bar showing zero length (berhaps a thin vertical line to indicate the "axis".

Scenario II:

Scale 0 to 13.

Scenario III:

Scale 0 to 10.

Scenario IV:

Scale -5 to 7, with bars moving from interior of cell towards the appropriate end of the cell.

Besides these options please also include a tally mark representation of data as used in statistics in cells i.e. presenting numbers as slashes with the number 5 represented by 4 striken out slashes

i.e /    //    ///    ////    ////(strike out with one stroke) etc.