format

[bojan0810]

Hi all...

For example I have this numbers

12.00
13.3333
45.21
15.00

Is there any way to format numbers by 2 decimals places but if numbers are "whole" then leave zero decimals.

I only came up with solution like this

12.
13.33
45.21
15.

Is there any way to remove that "dot" on whole numbers?

So it looks like this:
12
13.33
45.21
15

But just with formating cells not with VBA

thanks

 

[Claus Busch]

Hi,

Am Tue, 24 Jun 2014 05:37:02 -0700 (PDT) schrieb (e-mail address removed):

12.00
13.3333
45.21
15.00

Is there any way to format numbers by 2 decimals places but if numbers are "whole" then leave zero decimals.

not without VBA. You can make a helper column with
=ROUND(A1,2) and copy down and format the helper column "General"


Regards
Claus B.

 

[bojan0810]

thanks claus.

And I thought so that is impossible without vba or another column.

Not sure why microsoft didnt made that option. Or maybe they dont know lol

 

[Claus Busch]

Hi again,

Am Tue, 24 Jun 2014 06:03:22 -0700 (PDT) schrieb (e-mail address removed):

And I thought so that is impossible without vba or another column.

if you have a Excel version 2007 or later you can do it with Conditional
Formatting.
Your values in column A => Conditional Formatting => New Rule => Formula
to determine..... => Formula: =MOD(A1,1)<>0=> Format => Numbers
=Decimal with 2 digits
Same procedure again
Formula: =MOD(A1,1)=0 => Format => Numbers => General


Regards
Claus B.

 

[bojan0810]

ohhh. that worked perfectly.

I totally forgot that conditional formatting can be used for number formatting and stuff. As I am usually just highlighting cells with conditional formatting. I totally forgot about this one...

Thanks m8, this really helped alot

 

[bojan0810]

also when we are on conditional formatting.

Is there any way to match color of other cell. Fill color i mean.

For example. One cell is changing color based on values, that isnt important.

Important is that other cells needs to have color of that changing cell.

Is that possible with conditional formatting? Not with macro

 

[Claus Busch]

Hi,

Am Tue, 24 Jun 2014 14:04:19 -0700 (PDT) schrieb (e-mail address removed):

Important is that other cells needs to have color of that changing cell.

Is that possible with conditional formatting? Not with macro

if G1 should be colored if A1 > 10 then select G1 => Conditional
Formatting => New Rule => Use Formula .... => Formula; =A1>10 and
choose your format


Regards
Claus B.

 

[bojan0810]

Dana srijeda, 25. lipnja 2014. 07:22:37 UTC+2, korisnik Claus Busch napisao je:

Hi,



Am Tue, 24 Jun 2014 14:04:19 -0700 (PDT) schrieb (e-mail address removed):






if G1 should be colored if A1 > 10 then select G1 => Conditional

Formatting => New Rule => Use Formula .... => Formula; =A1>10 and

choose your format





Regards

Claus B.

--

Vista Ultimate / Windows7

Office 2007 Ultimate / 2010 Professional

thanks. I knew that before just wanted to see if there is any way to make like...

Match color of another cell without vba. I know it is possible with vba.

For example if you color one cell red, it should color all other "conected" with same color.

thank you

 

[Claus Busch]

Hi,

Am Tue, 24 Jun 2014 23:01:25 -0700 (PDT) schrieb (e-mail address removed):

For example if you color one cell red, it should color all other "conected" with same color.

If you want to color a range you have to select this range and write the
colomn reference in the cell absolut
If A1:G1 should be colored if A1 > 10 then
Select A1:G1 => Conditional Formatting => Use Formula....=> Formula:
=$A1 > 10

If you will color columns you have to set the row reference absolut
To color A1:A10 if A1 < 10
Formula: =A$1>10


Regards
Claus B.

 

[bojan0810]

Dana srijeda, 25. lipnja 2014. 08:08:37 UTC+2, korisnik Claus Busch napisao je:

Hi,



Am Tue, 24 Jun 2014 23:01:25 -0700 (PDT) schrieb (e-mail address removed):






If you want to color a range you have to select this range and write the

colomn reference in the cell absolut

If A1:G1 should be colored if A1 > 10 then

Select A1:G1 => Conditional Formatting => Use Formula....=> Formula:

=$A1 > 10



If you will color columns you have to set the row reference absolut

To color A1:A10 if A1 < 10

Formula: =A$1>10





Regards

Claus B.

--

Vista Ultimate / Windows7

Office 2007 Ultimate / 2010 Professional

thanks, I used your formula just instead of >10 i put 0, so if someone adds something to excel it will color that cell

thank you!