Kadri has attended:
Excel Advanced course
Conditional Formatting, hiding error value
Hi,
I have done conditional formatting in a spreadsheet showing cells with different values in different colours (ie. value between 1 and 5 green, between 6 and 12 yellow, greater than 13 red).
One row (say row 28) needs to highlight differently - not according to the number value in that cell but according to what percentage this value is of the value in row 27? ie. value in row 27 is 2, value in row 28 is 1 = 1/2*100=50%; where less than 10% needs to be green, more than 25% red etc. - therefore this row should highlight in red because 50% > 25%.
What is the best way to set this up?
If I add another row (row 29) to calculate the % and then hide it, can I 1) get rid of the 0/0*100=#DIV/0! so it does not show this error value in case the values are 0 and does not format it in red 2) set up a rule that the colour in row 28 is copied from 29?
Thank you,
Kadri
RE: Conditional Formatting, hiding error value
Hi Kadri
Thank you for your question.
When creating the conditional formatting instead of setting it to work with the contents of the cell to be formatted you can set it to use a formula instead. This formula can work out a percentage of neighboring cells and test the result all in one go (so you wouldn't need the hidden column. If the test passes then the conditional format would apply.
To set this up open the conditional formatting dialog box and use the Formula is setting to enter your formula.
Here is a link to an article on the Microsoft website that discusses this feature further.
http://office.microsoft.com/en-us/excel-help/creating-conditional-formatting-formulas-HA001111661.aspx
Let me know how you get on.
Kind regards,
Andrew
