Ask Your Question

Number format not applying to conditionally formatted cells [closed]

asked 2018-10-21 16:58:03 +0100

Anfasa gravatar image


I have a spreadsheet in which I want to display a number as "+12.345%" if it's below 2 (below +100%), and as "x1.234" if it's above 2. My cells have the default style, and manually I specified that makes them displayed as "+12.345%".

Screenshot of the cells and their formatting as +12.345%

I then have conditional formatting settings so that if the cells' values are above 2, it changes color and format. Now notice that this new style is correctly applied when a value is above 2, since it changes the color and background. But the format remains the same.

Screenshot of the conditional formatting settings. Cell is above 2 and has changed color, but not format

As you can see, in the style's settings, it should actually change the format:

Style settings, where the cell format is indicated as x1.234

Moreover, if I manually apply the style onto the cells, it does change the format:

Style has been applied manually to the cell, it has changed color and also format

So what's causing this and how can I fix it so that conditional formatting will actually change the cells' format?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by erAck
close date 2018-10-23 14:54:00.979230

1 Answer

Sort by » oldest newest most voted

answered 2018-10-22 20:05:02 +0100

erAck gravatar image

You applied a hard attribute (here number format) that takes precedence over and thus overrides the corresponding conditional format's attribute in every case. Format the cell with the Default language's General number format instead and use a conditional format with two conditions, <=2 to apply a style with "+"0.000% number format and >2 to apply a style with "x"0.000 number format.

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2018-10-21 16:58:03 +0100

Seen: 344 times

Last updated: Oct 22 '18