format number custom want some values in red

[Guest]

Hi.
I have a spreadsheet showing percents. Some are over 100%, some are less
than 100%. Say for example, 125% and 65%.
Can you please tell me how I can make a custom number format to make the
percentages less than 100% show up as red? So 125% would remain black but
65% would turn red, for example.

This is a bit urgent. Your help is greatly appreciated.

 

[Guest]

A conditional format may be neater but if you want a Custom number format try
this:-

[Red][<100]General"%";[Black]General"%"

Mike

 

[Pete_UK]

How about conditional formatting? Select your cells (assume A2:A20),
then click Format | Conditional Formatting and in the pop-up panel
select Formula Is instead of Cell Value Is. Enter this formula:

=AND(A2<1,A2>0)

(or just:

=A2<1

if you want negative values to be shown in red as well),

then click on the Format button, click Color and choose Red, then OK
your way out.

The format will apply to all cells that were highlighted, and you can
use the Format Painter icon to apply it to other cells.

Hope this helps.

Pete

 

[Guest]

Hi Mike,
It almost worked. But the custom formula changes my numbers. It turns all
of the numbers red and reverts back to decimal form. For example 125%
becomes 1.25,
65% becomes .65.

I'd really like 125% in black and 65% in red. Thanks very much for your
help. Is there anything else you think I could try?

PLs let me know.
Thanks,
Chris
--
Thanks!

A conditional format may be neater but if you want a Custom number format try
this:-

[Red][<100]General"%";[Black]General"%"

Mike

Hi.
I have a spreadsheet showing percents. Some are over 100%, some are less
than 100%. Say for example, 125% and 65%.
Can you please tell me how I can make a custom number format to make the
percentages less than 100% show up as red? So 125% would remain black but
65% would turn red, for example.

This is a bit urgent. Your help is greatly appreciated.

 

[Guest]

Thanks Mike and Pete.
Your suggestions were helpful. I was not aware of conditional formatting.
That did the trick. Thanks for the instructions.

Have a great day!
Chris

 

[Pete_UK]

You're welcome. Thanks for feeding back.

Pete

Thanks Mike and Pete.
Your suggestions were helpful. I was not aware of conditional formatting.
That did the trick. Thanks for the instructions.

Have a great day!
Chris
--
Thanks!








- Show quoted text -

 

[David Biddulph]

I think Mike may have been confused. Try changing his custom formatting to
[Red][<1]0%;[Black]0%
--
David Biddulph

Hi Mike,
It almost worked. But the custom formula changes my numbers. It turns
all
of the numbers red and reverts back to decimal form. For example 125%
becomes 1.25,
65% becomes .65.

I'd really like 125% in black and 65% in red. Thanks very much for your
help. Is there anything else you think I could try?

PLs let me know.
Thanks,
Chris
--
Thanks!

A conditional format may be neater but if you want a Custom number format
try
this:-

[Red][<100]General"%";[Black]General"%"

Mike

Hi.
I have a spreadsheet showing percents. Some are over 100%, some are
less
than 100%. Say for example, 125% and 65%.
Can you please tell me how I can make a custom number format to make
the
percentages less than 100% show up as red? So 125% would remain black
but
65% would turn red, for example.

This is a bit urgent. Your help is greatly appreciated.