Microsoft Excel

I'd like the ability to select which way the bars go, even if all I use is left-to-right. Or to specify positive-right and negative-left.

Given that the numbers are right justified in both circumstances, then I believe it would be sensible to keep the Data Bars starting from the left. [I don't use RTL].

If you did change them, as indicated in the second option, then the view is totally different.

Another spanner-in-the-works comment.  I use LTR but would want to be able to have data bars both ways or, as someone previously said, to be able to have +/- bars (i.e. with 0 somewhere in the centre of the cell).  This latter would, of course, require two sets of colour options ...  Have data bars opened a whole new can of worms?!

Seriously - this is a good feature and I think it can be improved.  For me, as a LTR user, the right aligned bars are the most intuitive for me (as opposed to your default) because the bar emphasises the digit.  Given that numbers are right-aligned as well in RTL, then I would have thought that default would then naturally carry to them.

On a random further note, is there an option for a bottom-to-top (BTT?) mode?  It would be very useful for certain graphing systems and seeing as you've already written the LTR/RTL code it can't be that much of a grind ... can it?  [naive perhaps of me to suggests such a thing ...]

I'd stick by the adage 'If your having to have a long debate about which way it should be make it configurable'

I'd like to be able to select if the bars start left or right or in the middle (with two colors for positive and negative values - this would be a great function!) [I don't use RTL].

(I'm a RTL user.)

I have a previous negative opinion on this conditional formatting, even in the LTR view. Choosing a third color to fit a front- and back-ground scheme is really hard. This mid-option must be visible on the cell color, and at same time not interrupt the text color.

For this, I assume, the LTR bar design is good enough – because of the color fading in it, in contrary to the RTL coloring problem. Note that this RTL coloring problem may appear even on LTR sheet (if the column is best fitted) as well.

It seems that the Office team chose to ignore this problem, and let the coloring problems to the user handle. That may be a right decision, and under this decision, the coloring problem should not be an issue even in the RTL conditional format.

The second issue is the independent question of any bars direction for Hebrew and Arabic eves. I can tell that the native right-to-left reader see any information in the same direction. That's why the "correct" RTL bars should be as proposed on the right side of the picture.

I can support this behavior with the default RTL x-axis graphs in Excel, which increase from the right to the left.

[I am not an RTL user]

I have no true opinion either way, except that if the darkest part of the databar overlaps with the text, it makes the text really hard to read.  I think ittays was saying something similar, that the user has to struggle to find a colour combination that makes the text legible.  This is of course true even in the LTR case when the databar approaches 100%

I guess I'll throw in another vote for customizable and let the user decide what works best in his situation.

Thanks for all the great feedback!  Although having the ability to select which way the bars go would be a great option, we are just too late in the development cycle to implement that.  It would require a change to our file formats and we’ve locked down our new file formats consequently, we’re not taking any changes that require us to save new types of information at this point.  So given that selection of the direction of the data bars is not an option, which orientation for RTL users would be most appropriate?

Hi David,

It is strange to note that someone in Microsoft is seeking feedback for something which should have been configurable by default for all the users, whereas for other much more important issues they never heed to the cries of the users.

Regards
Kanwal

Hi David,

I concur with those who are suggesting that something like a left align/right align toggle would be nice.

Cheers,
Shane Devenshire

<<I'd like the ability to select which way the bars go, even if all I use is left-to-right. Or to specify positive-right and negative-left.<<

<<I use LTR but would want to be able to have data bars both ways or, as someone previously said, to be able to have +/- bars (i.e. with 0 somewhere in the centre of the cell).  This latter would, of course, require two sets of colour options ...<<

<<I'd like to be able to select if the bars start left or right or in the middle (with two colors for positive and negative values - this would be a great function!)<<

Somewhat off topic but I wanted to comment on the above posts. The idea of different cell alignments for +/- bars or having bars go in different directions from the center sounded interesting so I decided to simulate these conditions.  In the end I concluded that either color scales icon sets would be better for visualizing data with +/- values...However, you be the judge.

Here are a few things I did:

1) The data I used was in K8:K23. The range included a few negative values interspersed among the positive values.
2) In another column, I entered the following in a cell: = REPT("|",ABS(K8/400)) and then copied the formula down 15 cells (the scaling factor K8/400 was used because of the values in K8:K23). I used J8:J23 for the formula range. "|" is the vertical bar.
3) I chose a font face that made the vertical bars merge into a single solid bar. Using live preview in the font picker, Britannic Bold was the first font I encountered that made the bars merge.
4) I formatted the range J8:J23 with a green font color.
5) With the range J8:J23 selected, I used the conditional formatting formula =K8<0 and selected a red font for the true condition.
6) Next, I experimented with bar placement. Since there's no alignment option for conditional formatting, I right aligned the negative values manually.
7) Next, I padded the vertical bar part of the formulas with spaces (different number of spaces for positive and negative values) to simulate the bars starting from the center and pointing in different directions.
8) For each bar placement (all left, negative right/positive left & positive/negative in center, I superimposed a picture link of K8:K23 to see how the bars looked with numbers (right aligned for the experiment).
9) The only case in which the bars looked good to me (with or without the superimposed data) was all bars on the left (the side doesn't really matter so long as they're all on the same side).

Further, I tried the following with Data Bars:

1) Selected K8:K23 and chose a green data bar.
2) For the same range, I created another data bar condition with red formatting. For the red condition, I used the formula =MAX($K$8:$K$23) for the "shortest" bar and =MIN($K$8:$K$23) for the "longest" bar. The formula was used so that larger negative values show with longer bars than smaller negative values.
3) With the range selected, I entered the following in the immediate window: Selection.FormatConditions(1).Formula = "=If(K8<0,true,false)". (This technique was described by Dave in a blog entry). Now the negative values appear with red bars (with larger negative values showing longer bars) and the positive values appear with green bars.

The technique I used with Data Bars has a major flaw, however. The negative bars show up with a different scale than the positive bars because the shortest and longest bars are based on different values in each case. One way around the problem would be to use another range, enter the formula =ABS(K8) and copy the formula down. Then use two identical Data bar conditions; each using a different bar color and with the option to display the bars only checked. Then apply Selection.FormatConditions(1).Formula = "=If(K8<0,true,false)" to the new range. A picture link of the original range can be used if you need to superimpose the data with the bars.

<<Given that the numbers are right justified in both circumstances, then I believe it would be sensible to keep the Data Bars starting from the left<<

<<I have no true opinion either way, except that if the darkest part of the databar overlaps with the text, it makes the text really hard to read.<<

I have to admit - I find this one a bit puzzling. My thinking up to now was that one reason for fading bars was to minimize obscuring the data on the right, but apparently, this is not the case.