Excel 2010 Data Bars Not the Correct Length

Question

Excel 2010 Data Bars Not the Correct Length

Anonymous

I'm having some problems with data bars that I hope someone can explain to me. This may get a bit long, so please bear with me...

I have a worksheet with a column containing percentages that represent the level of completion of some tasks. They begin as all zeroes and I select the range of cells and apply Conditional Formatting-->Data Bars-->Gradient Fill, Green Bars. Everything is good so far, with no bars showing in the percentage cells. Now, if I change one of the cells to 10%, the data bar appears, but instead of filling up 10% of the space for the cell, it fills up 100%. Hmm, "Strange," I think to myself. So I change another 0% to 50% and the 50% cell's data bar fills it completely and the 10% cell data bar shrinks a bit, but is still clearly more than 10% of its cell width. So, they appear to interact. I enter 100% into a cell, and then the 10% and 50% cells now look correct and the 100% cell is completely filled. Just for the heck of it, I change another cell to 200% and it becomes full and all the other data bars adjust accordingly.

I investigate a bit further and find that the data bars' default setting for minimum and maximum values is set to Automatic, so the above behavior begins to make some sense. So, I change the rule's default from Automatic to Percent, which sets minimum to 0 and maximum to 100. So, I think, that will correct the behavior and I'll get what I want; 10% will have a 10% bar and 50% will have a 50% bar as expected, and the values will no longer be dependent upon one another. But no, this is not what happens. The behavior remains the same as above.

Next, I try setting the rule to Number and enter a minimum value of 0 and a maximum value of 1. Now, I get the behavior that I wanted all along. It's not the way I probably would have designed it, but at least I now know how it works.

Again, I go back to my workbook and select another sheet where all values are 0% except for one, which is 70%. I apply the Conditional Formatting to create the data bars, and without changing any settings (everything is still at the default of Automatic), the 70% bar fills only 70% of the cell width. What??? Why are data bars behaving differently on this worksheet?

Lastly, I check another worksheet that already has data bars applied and it looks generally okay (it has some cells with a maximum of 100% in them), but I notice that two adjacent cells--one above the other--both are at 95%, but one fills the entire cell width and the other fills about 95% as it should. I monkeyed around with various settings to try to correct the 95% cell that was 100% filled, but could find nothing that would correct the issue. I finally cleared all conditional formatting from the sheet and reapplied the data bars and that corrected the situation.

So, in summary, I'm seeing inconsistent behavior with data bars; sometimes they work as expected where 70% amongst other values of 0% shows up as 70% filled and sometimes shows up as 100% filled unless there is at least one other value that is set to 100%. Also, in the third and final case, it appears that they can somehow get corrupted such that almost anything can happen. Is what I'm seeing normal with data bars? Which behavior should they have: the initial behavior where they're dependent on the maximum value or the second behavior where the data bars act independently?

I'd appreciate any input those of you with more experience may be able to provide!

P.S. I am running this on a Dell laptop with Windows 7 Enterprise and SP2 as well as Office 2010 with SP2, so I should be up-to-date with all patches, etc.

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

6 additional answers

Answer 1

I had the same problem and tried many different approaches to make it work as it should. Without success!! When you select percent, even when you select 0 and 100 as the min and max, the highest value of the series still shows a full bar. There are some workarounds.

Add a dummy cell to the range, enter 100% as the value and the rest of the data bars should be at the correct length. Downside is you have a dummy cell in your table. (Unless you hide it in a discrete location)
Better solution is to edit the conditional formatting rule, change the minimum type to number and value to 0 (zero), then change the maximum type also to number with a value of 1. That should fix the issue.

Answer 2

I had the same problem and may have found a workaround.

Before you select the data you want to add databars to, enter 0% and 100% in any random cells, and include these in your range. You can then add the databars without needing to the use 'more rules' section.

This worked for me as Excel always fills up the data bar with min and max values, therefore if you enter 0% and 100%, these will automatically be your highest and lowest values.

Answer 3

I had the same problem and the solution (as I found from JeffreySparrow's post) is to set the conditional formatting to "Number" and use 0 as the minimum and 1 as the maximum.

It turns out this is NOT a bug and works AS INTENDED (so I believe).

The reason 100% doesn't work is that the 'Percent' category is Percent of data range NOT the fixed NUMBER value. If you want to use a fixed value, use the 'Number' category. You may also then be confused with the 'Percentile' category thinking that should be using the % of the range, but no, Percentile is RANK based so it is based on the x percent of entries when in order. For example take the following:

data: 0%, 1%, 2%, 3%, 4%, 5%, 6%, 7%, 99%, 150%

setting min to 'Percent' = 5 would set all value less than 7.5% to the min (0.05* range(1.5)=0.075 or 7.5%)

setting min to 'Percentile' = 10 would only set the 1st value (0%) to the min (10% of 10 data points = 1 data point)

setting min to 'Percent' = 50 would set all values less than 75% (all but 99% and 150%) to the min

setting min to 'Percentile' = 50 would set the 1st FIVE (5) values to the min

I hope that helps.

Answer 4

Anonymous

Looks like this is still an issue in Excel 2013...

Microsoft, is this a known issue now? Is there a patch for this?

0 comments

Answer 5

Can you please upload a file and provide the link of the uploaded file?

This would help us to analyze the issue...

Okay, here's the file:

Data Bars Worksheet

Currently, the "M31 Schedule" and "M31 Status" worksheets are displaying the data bars the way I want them to; however, if you enter a percentage greater than 100% then all bars are shortened accordingly.

Note that the percentages for completeness should be entered by hand into the cells on the status sheets. The cells on the schedule sheets just reflect what is entered on the related status sheet.

As for the "M11" sheets, the schedule sheet looks okay, but the status sheet is only showing completely filled data bars instead of proportionately filled bars.

This appears to have something to do with how the range is selected for the conditional formatting, but I haven't been able to decipher just how it works as of yet. It sort of appears that if you select a range of cells, that they all become dependent upon each other, but if you then select a single cell and add conditional formatting, it doesn't seem to act like it is a standalone cell, i.e. filling a 50% cell half way.

Perhaps it's just me not understanding how data bars are supposed to work, but, so far, they don't generally behave the way that I would expect them to.

Share via

Excel 2010 Data Bars Not the Correct Length

6 additional answers