98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Conditional Formatting - Excel 2010
Conditional Formatting - Excel 2010
Resolved · High Priority · Version 2010
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.
Tue 27 Sep 2011, 11:06Trainer Simon said...
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.xlsxTue 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
Tue 27 Sep 2011, 11:27Trainer Simon said...
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
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.
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. |
Excel tip:Calculate age or serviceThe DATEDIF() function in Excel calculates the number of days, months, or years between two dates. So, this function makes it easy to calculate a person's age. To try this tip: |
