Data Bars – Feedback Please

Hi

I guess it is important to mark zero values with something distinguished as for example a horizontal line in th middle of the cell instead of a bar. This could be set as an option to allow users to display this horizontal line or simply not to display anything at all.

Regards

I must say that I find the data bars slightly distracting and can't really see in what way I would use them, but since the question is raised I'd suggest

Scen 1

||

|||||||

|||||

Scen 2

||||||

|||||

|

|||||||  

Scen 3

||||

|||||

||||

|||||

Scen 4

|||||

 |||||

Scenario 1: Zero blank seems the only logical option.

Scenario 2: Wide spacing - zero to max value

Scenario 3: Close in values - ideally an option to have a reasonable automatic lower bound.

If the bars are applied on a pivot table, which can be expanded, you probably want the lower bound to be zero so it doesn't need to recalculate.

Scenario 4: Opposite color. Since it is possible to have all negative values.

It would also be nice to be able to left or right justify the bars.

I've made some pretty strong statements (juiceanalytics.com/.../on-misrepresenting-data) about Excel 2007's data bars in the past and I'm really happy you're working to fix them.

You need to start with the absolute principle that the bars you show _must_ be proportional to the numbers they represent.

Scenario 1: Show no bar for zero.

Scenario 2: Make the bars proportional to their values.

Scenario 3: Make the bars proportional to their values.

Scenario 4: Have bars go in one direction for negative values and the other direction for positive values. Colorize negative values differently from positive values.

The gradient fill (shading to white!) also needs to come off these bars. A soft palette for the bars works perfectly well, see flickr.com/.../photostream for an example.

Scenarios 1 through 4:  Strict linear proportion.  Base at the minimum value or zero, whichever is less.  Single pixel wide zero axis line spanning cell height.

Selectable option for Scenario 4:  Complementary color.

Rationale:  Keep it simple, with uniform rules that are easy to interpret.  If advanced graphical features are needed, use a graph.  If the data needs to be scaled, scale the data, not the graphic.  There are lies, damned lies, and graphs without zero.

I am looking for a way to show a 3-Dimensional Stacked Bar (column).  Is there a way to do that?

The top parameter (construction site) has six sub-parameters (phases of construction) and three sub-sub-parameters (performance indicators with %red, %yellow, %green for conditions) that need reporting for each phase of construction.  Seems like a 3-D stacked bar would show this information best.  Each construction site being on the x-axis, each of the six phases being on the y-axis and the %red, %yellow and %green stacked to make 100% on the z-axis or columns.

Ideas?

Thank you.

I agree with previous comments that this needs to be simple for the user to achieve through the UI. I don't believe that means making decisions for them which will not suit all scenarios - flexibility and richness through the options dialogue box are key factors for me, as long as the easy and default options are reasonable to start with.

I totally agree the gradient reduces clarity and has to go.

At the moment, the default is to use bars whose lengths vary according to the max and min values found at that point in time. This unfortunately often results in non-proportional representations. Some above have said they feel that proportional is the only way to go (which assumes a zero starting point, I guess).

However, if I am looking at figures for (say) sales across the weeks of a year, or heights of pupils in a class, the minor differences in data points may be insignificant against the baseline value, so a proportional bar with a zero 'axis' is not giving me much help in visually assessing the data.

Maybe a zero axis should be the default if all the values have the same sign, and the option to use a different fixed value or the least value in the data set should still be available through the rules dialogue.

I see no real difference in scenarios 1, 2 and 3 insofaras this all comes down to where the axis is drawn and how you manage proportionality. I agree with others above that using any kind of non-linear bar to try and help with unusual distributions will not be intuitive to the reader.

The only distinction might be that when zero is the baseline value it should have no bar at all; when the baseline is any other number, the least value should show a single pixel. An awkward detail. I would fall on the side of having nothing for the least value rather than the current situation where zero shows up.

If the least value is defined as negative and the max as positive, then clearly zero needs to have a line of the right proportion for (0-min)/(max-min).

For cells which are already formatted as percentages, it might make sense to default the bars to minimum / maximum values of 0 / 100.

As for negative values: If all the data set are negative, I think the axis should be at the right cell boundary with bars growing to the left.

If the dataset is mixed sign, I think bars to the left and right of a  line at zero makes perfect sense, but again the user should be able to choose between "reverse bars for negative values" or not. Think about temperature readings on a Farenheit scale - the zero point is almost arbitrary to any use of this data, a normal bar would be easier to understand and fit more closely to the metaphor of a thermometer reading. I don't think the model should be extended for non-zero axes, that's overly complex for a quick visual feature.

I definitely think the user should be able to choose to have a reverse colour - or not. Probably needs a check box for "different colour for negative values", but then just make the normal colour picker available, don't freak people out with colour-wheel complements and magenta data bars. Backwards compatibility could afford to lose this second colour.

Putting data bars in one bunch of cells based on the contents of another range sounds like adding complexity where it is not needed. Simply have B1=A1...B100=A100, then apply data bars to B1:B100 and tick the box for "data bar only" to suppress the numbers in those cells. The existing functionality supports the effect you want with very little effort.

i would like the ability to change and see the shortest bar for the highest number and the longest bar for the lowest numbers.

additionaly i would like an option to exclude both blank and cells that contaon a "0" zero value.

this is a nice improvement thanks

Thanks everyone for your feedback about Data Bars.  We really appreciate you taking the time to tell us what you think.

To summarize what most of you are saying:

- Zero values should show no bar or a line line.

- The Data bars should be proportional to better reflect the relative values.

- For Scenario 3 it is more important to show them proportionally than to be able to quickly differentiate between the bars that are close in value

- For negative values, bars going in the opposite direction and/or a different color work well to represent the value.

- Gradients should be removed from the data bars.

If you have additional feedback that has not been reflected, pleae add your comments.

Thank you,

Eric Patterson

Excel Program Manager

I'm not sure how many people know about this add-in for Excel, called MicroCharts, but the below link shows some very good ideas that would be great if they were built into upcoming versions of Excel. I am in no way affiliated with the website or add-in, nor have I used it, but it gives some great examples.

www.bonavistasystems.com/.../GuidedTour02.htm

Scenario 1 – data values contain a zero

For zero value do not show a bar

Scenario 2 – data values are spaced far apart

Use an optional logaritmic scale

Scenario 3 – data values are closely spaced

Do not scale limits to min-delta to max+delta and if you do it please let the function to be optional not mandatory

Scenario 4 – data values contain a negative number

Draw a gray line for zero value and draw the bar in opposite direction for neg. values

Regards

Sergio

I'm surprised that nobody so far has suggested that in-cell data bars should have exactly the same axis formatting capabilities as a bar chart. So the Rules Options dialog should have an exact copy of the Chart Axis Options dialog(s), allowing us to specify the scaling, where the axis crosses, whether to show axes, top or bottom, etc. And for data bars, the chart's "plot area" is defined by the cell borders.

Gradients in data bars is just one of many examples of chart junk in Excel. If the visual cue doesn't convey meaning, it's actually detrimental.  What would be most productive is this:

- a strong commitment from the Excel team to fight chart junk. So far we've not seen it in the product.

- A real self-education effort from the Excel team about what chart junk is. Look it up, there are several blogs out there on this topic.

If we have a guarantee that you'll look at it in a systematic way then it makes it more attractive for users to send feedback. A piecemeal approach only goes so far. During the Excel 2007 dev cycle you guys paid lip service to chart clarity but actually chose to ship a lot of extra useless eye candy. 3D cylinders painted with gradients? Puh-lease.

Two suggestions not mentioned:

1) Let the user define the location for the data bars.  Showing the bars in the same cell as the numbers hinders a clear view to both.  Sure you can show the bar without the numbers, but if you want to show both in different columns, you basically have to duplicate the data.

2) A nice-to-have functionality: allow useres to define a secondary Point on the data bar representing a target or goal.  Similar to the Target line on a Bullet Graph.  

A red dot or line will do.

I imagine it would be useful to let the users define the goal via a cell reference or value input.

And if it relates the magnitude of each element of the series to Font.size?

Obviously it is quite complicated ...