Ask Your Question

How can i change the displayed value of a cell

asked 2016-10-25 10:03:53 +0200

Joysn gravatar image

I have a table with positive integer values, negative integer values and empty cells. I want to use conditional formatting, positive values should have a different template than negative values. But, negative values should be shown as positive values as well, to that every value in the table appears as a positive number. Only the format template (using conditional formatting) should be calculated out of the sign, negative or positive.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2016-10-25 11:06:31 +0200

pierre-yves samyn gravatar image


If you only want to customize the font color you do not need conditional formatting. You can use a custom format. Number format codes can consist of up to three sections separated by a semicolon (;). For example:

  1. In a number format code with two sections, the first section applies to positive values and zero, and the second section applies to negative values.
  2. In a number format code with three sections, the first section applies to positive values, the second section to negative values, and the third section to the value zero.
  3. You can also assign conditions to the three sections, so that the format is only applied if a condition is met.

image description

If you need a conditonal formatting (e.g. customize background) you just have to create styles applying different number formats.


edit flag offensive delete link more


thanks for the nice hint!

Joysn gravatar imageJoysn ( 2016-10-25 14:37:51 +0200 )edit

answered 2016-10-25 11:10:42 +0200

karolus gravatar image

You dont need explizit conditional formatting

Select the Cellrange, →Right-click→Format Cells→Numberformat…→Formatcode→ 0;0

or better create a new Cellstyle with the settings above →F11→Cellstyles→Rightclick→new and apply that style

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-10-25 10:03:53 +0200

Seen: 304 times

Last updated: Oct 25 '16