| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
format number custom want some values in red
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. -- Thanks! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
format number custom want some values in red
A conditional format may be neater but if you want a Custom number format try
this:- [Red][<100]General"%";[Black]General"%" Mike "learningaccess" wrote: 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. -- Thanks! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
format number custom want some values in red
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,A20) (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 On Oct 9, 2:02 pm, learningaccess wrote: 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. -- Thanks! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
format number custom want some values in red
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! "Mike H" wrote: A conditional format may be neater but if you want a Custom number format try this:- [Red][<100]General"%";[Black]General"%" Mike "learningaccess" wrote: 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. -- Thanks! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
format number custom want some values in red
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! "Pete_UK" wrote: 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,A20) (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 On Oct 9, 2:02 pm, learningaccess wrote: 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. -- Thanks! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
format number custom want some values in red
You're welcome. Thanks for feeding back.
Pete On Oct 9, 3:16 pm, learningaccess wrote: 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! "Pete_UK" wrote: 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,A20) (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 On Oct 9, 2:02 pm, learningaccess wrote: 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. -- Thanks!- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
format number custom want some values in red
I think Mike may have been confused. Try changing his custom formatting to
[Red][<1]0%;[Black]0% -- David Biddulph "learningaccess" wrote in message ... 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! "Mike H" wrote: A conditional format may be neater but if you want a Custom number format try this:- [Red][<100]General"%";[Black]General"%" Mike "learningaccess" wrote: 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. -- Thanks! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| custom format for boolean values | Excel Discussion (Misc queries) | |||
| Moving custom number format to NUMBER | Setting up and Configuration of Excel | |||
| how do I add phone number format as a permanent custom format? | Excel Discussion (Misc queries) | |||
| Format a cell with a custom number format | Excel Worksheet Functions | |||
| Custom number format always defaults last number to 0. | Excel Discussion (Misc queries) | |||
Linear Mode
