Data Bars are the wrong length for data in cell

[Ed Hoover]

Haven't seen this addressed here, so apols. if I missed it somewhere.

I've got some columns with data bars in them. The value of the numbers is
small – they’re all in the range of 1 to 20. The vast majority of the data
bars are fine – their length is proportional to the value in the cell. So the
data bar for a 10 is about half the length of the cell; 20 is the full
length; 1 is but a sliver.

However, in some of the cells where the value is 1, the length of the bar is
about 50% of the cell. None of the other values gives me a wrong bar length,
and not all cells with a value of 1 have a wrong data bar length (we’re
talking about six of them out of 155 or so).

I've cleared the conditional formatting from those cells and then set it up
again, but that doesn't work. I've done the format painter from the cells
with 1s in them that are correct, but that doesn't work, either.

Any thoughts?

Thanks.

 

[Bill Sharpe]

Haven't seen this addressed here, so apols. if I missed it somewhere.

I've got some columns with data bars in them. The value of the numbers is
small – they’re all in the range of 1 to 20. The vast majority of the data
bars are fine – their length is proportional to the value in the cell. So the
data bar for a 10 is about half the length of the cell; 20 is the full
length; 1 is but a sliver.

However, in some of the cells where the value is 1, the length of the bar is
about 50% of the cell. None of the other values gives me a wrong bar length,
and not all cells with a value of 1 have a wrong data bar length (we’re
talking about six of them out of 155 or so).

I've cleared the conditional formatting from those cells and then set it up
again, but that doesn't work. I've done the format painter from the cells
with 1s in them that are correct, but that doesn't work, either.

Any thoughts?

Thanks.

It appears that "data bar" didn't make it into the Excel 2007 help
system. I found an MSDN article on the topic by Googling "data bar in
Excel 2007" that helps.

Try this:
Click Conditional Formatting on the Home tab, then select data bar.
Select "More rules..." Be sure "lowest value" and "highest value" are
selected as the format type. Click OK.

It's a known "bug" (or at best a misleading feature) that a zero value
gets plotted as something other than zero in this format.

Bill

 

[Ed Hoover]

Thank you, Bill, that worked.

For anyone else googling this, my format types were already set as "lowest
value" and "highest value", but I clicked them and re-selected anyway, then
hit OK, and it was fixed.