Microsoft Excel

PingBack from http://www.artofbam.com/wordpress/?p=4285

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 Dick 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.  

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

|||||

 ||||| <-- diffrent color, from right to left

|||||||

|||

Non-linear scales are just to confusing IMO and just jumbles the data.

Scott Ruble has a post on on the Excel team blog today, asking for feedback on the databars functionality

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.

Scott Ruble has a post on on the Excel team blog today, asking for feedback on the databars functionality

I've made some pretty strong statements (http://juiceanalytics.com/writing/2006/09/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 http://flickr.com/photos/postneo/405239750/in/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 <cell background colour> 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.

http://www.bonavistasystems.com/Media/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 ...

If you format the cells with the data at 90 degrees orientation then have the data bars flow with this orientation. In other words, have the data bars go up and down instead of from left to right. This will allow applications to run without adding any charts to the spreadsheet. This will also allow applications to run more easily without any vba code. Remember, the best program is no program.

I completely agree with Olivier's perspective on chart junk, but he is asking too much of Microsoft.  If enough customers want eye candy then sell them eye candy.  If some people do not know how to draw without using every single crayon in the box, it is not the crayons' fault.

I'm currently trying to use conditional formatting to compare two columns to show for each row which of the two values is greater - so for September did the company have more income or expenditure and so on for the rest of the year. I can only have this by selecting the rule set for each row individually. Even AutoFill - Fill Formatting Only - turns it into a comparison between all and its not possible to use F4 or Redo to reapply the conditional rule. What I really want it to do is compare the two columns - but shade the cell relative to the whole table. So if the values are

500   100

800   300

500   900

800   1000

then the 1000 should be a deeper shade than the 900.

It&#39;s been quite a year for those who have been blogging about the Open XML file formats. Here&#39;s