Steve has attended:
Excel VBA Intro Intermediate course
Conditional Formatting - Excel 2010
When there is positive and negative numbers in a range on which I wish to apply data bars, if the negative axis settings are set as "None (show negative value bars in same direction as positive), then the largest negative number is not formatted with any data bar.
If the axis is set to "Automatic (display at variable position on negative values) then the smallest positive number is not formatted with any data bar.
From a presentation point of view this looks odd as if its an error.
I would like all cells to have an element of a data bar based on its value whether positive of negative.
RE: Conditional Formatting - Excel 2010
Hi Steve,
Thank you for your question.
There seems to be a bug with the way it formats the negative numbers.
I have devised a work around which involves putting an array formula in a blank cell underneath the range of cells and then hide the row.
I have attached a spreadsheet with the formula. The array formula is in cell E21 which can be hidden.
I hope this helps.
Regards
Simon
Attached files...
ForumAnswerConditionalFormatNegativeValues.xlsxRE: Conditional Formatting - Excel 2010
Many Thanks, But I don't seem to have the attachment.
Regards
Steve
RE: Conditional Formatting - Excel 2010
Hi Steve,
Have you checked at the bottom of my reply?
If not can you send me your email address and I will email it to you asap.
Regards
Simon
RE: Conditional Formatting - Excel 2010
There was an attachment with a zip file, but it contains all xml files. I passed it to my It dept to take a look and they cannot open an excel file from it.
Email address:
Steve.Williams@Rawlinson-hunter.com
Thanks
Steve.
