Conditional Formatting changes number format

[bigmyk2k]

I am trying to conditionally format the fill of the cells in a table based on the value in them.
The values are percentages based on another cells relative value to a reference cell (eg. Cell C1 shows 120%, because B1 is 1.2 and A1 is 1.0).
I want numbers above 100% to be grades of red based on how far above 100%, and grades of green based on how far below 100%.
I can get the shading just fine, but it changes the number format to currency (120% becomes $1.20).
No matter what I try I can't change the format back to percentage.
To be clear, I am trying every number format change I can think of, and every combination of formatting settings.
Thoughts?

 

[Joe4]

It sounds like that you have a Conditional Formatting rule that is applying that number formatting.
Note that a single CF rule can both change colors AND change number formatting. So perhaps you inadvertantly set the number formatting on one of your rules (or you have an extra rule in there that shouldn't be there).
You need to find it and alter or remove it.

The only other thing I can think of that would not allow you to change it back is if you have some automated VBA code (event procedure code).

 

[bigmyk2k]

It sounds like that you have a Conditional Formatting rule that is applying that number formatting.
Note that a single CF rule can both change colors AND change number formatting. So perhaps you inadvertantly set the number formatting on one of your rules (or you have an extra rule in there that shouldn't be there).

As far as I can tell, there is no Font formatting with this CF.

The only other thing I can think of that would not allow you to change it back is if you have some automated VBA code (event procedure code).

Also, pretty certain that there is no VBA in this workbook. Other thoughts?

 

[Joe4]

Its not Font Formatting, it is Number Formatting.
Check the Number tab on each of your Conditional Formatting rules.

 

[bigmyk2k]

Where is the Number tab?
The dropdown that shows "Number" has the three other options of Percent, Formula, and Percentile.
None of those options change the number format, and also don't let me set the center of the range at 100%.

 

[Joe4]

Which version of Excel are you using?
How are you setting up the Conditional Formatting?
I prefer to use the Formula option, as it is easier to me to follow and see what is going on.

What I might recommend to you is to clear ALL Conditional Formatting rules, and start over. Try adding them back, one at a time, and test after each one. Then, if there is an issue, you should be able to identify which rule is causing issues.

 

[bigmyk2k]

Which version of Excel are you using?
How are you setting up the Conditional Formatting?
I prefer to use the Formula option, as it is easier to me to follow and see what is going on.

I am using Excel within Office 365, build 8528.2147.
I am putting in the Conditional Formatting by using the CF dropdown on the Home tab, selecting Color Scales.

What I might recommend to you is to clear ALL Conditional Formatting rules, and start over. Try adding them back, one at a time, and test after each one. Then, if there is an issue, you should be able to identify which rule is causing issues.

I have previously done this several times, and this is the only conditional formatting rule in the entire workbook.

 

[Joe4]

If you remove ALL Conditional Formatting, are you able to manually change the formatting of those cells?
If so, then the Conditional Formatting is the issue.

 

[bigmyk2k]

Yes, on both counts.

Also, with the CF in place, the formatting for the cells still shows as it should, in the "Number" dialogue box, but still displays incorrectly in the cells.

If I copy the conditional fomatting to another column, sometimes it shows up as percent, sometimes as currency, and every time I paste it into another column, the frequency of the latter changes (increases) in the previous columns, if it wasn't already the whole column. If I go to another sheet in the workbook, and click back to the original sheet, every cell with the formatting shows as currency, and remains that way, unless I undo all previous steps to remove it.

Pasted here (formatting correct):

And, then pasted again:

Clearly, the CF is the problem. What I am trying to get help with, is what with CF is the problem, and how do I fix it.

 

[Joe4]

Looks like it is a bug!
See: https://social.technet.microsoft.co...ditional-formatting-in-excel-2013?forum=excel

Note that the workaround they suggested using the Formula option which I said I prefer...