Forum home » Delegate support and help forum » Microsoft Excel Training and help » Conditional Formatting - Excel 2010
|
|
|
The UK's most regular Training information: See also: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Conditional Formatting - Excel 2010
Resolved · High Priority · Version 2010
Reply Mon 26 Sep 2011, 10:47Delegate Steve said...
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.
For upcoming training course dates see: Pricing & availability
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.xlsxReply Tue 27 Sep 2011, 11:11Delegate Steve said...
RE: 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
Reply Tue 27 Sep 2011, 11:49Delegate Steve said...
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.
|