Solved Percentage format in Excel

Acer Aspire timelinex as1830t-68u118 not...
October 30, 2014 at 03:26:30
Specs: Windows XP, 4096
Hie everyone,

I'm trying to use the percentage format in Excel spreadsheet but not in the traditional way (% button), where putting e.g. 3,5 into the "percentage formatted" cell I can see as the result 350%.
No, that is not my target.
What I am looking for is such a cell format where when I put e.g. 3,5 the result displayed would be 3,5%.
I have tried to use "Non-standard" cell format where some formula like "_-* # ##0,00 %_-;-* # ##0,00 %_-;_-* "-"?? %_-;_-@_-" was given but in that case I see as the result 0,35% displayed.
Could anybody tell me how to modify that formula in order to have correct displayed value of "3,5%" instead of "0,35%)?
Thank you in advance!

Zennon


See More: Percentage format in Excel

Report •


✔ Best Answer
October 30, 2014 at 09:34:58
Eventually, through multiple testing and checking I've found something like this:
_- # ###,0_;"%" working correctly.

:-)



#1
October 30, 2014 at 04:01:25
I'm not clear on one point.

Part of your post says "....when I put e.g. 3,5 the result displayed would be 3,5%...."

Another part says "...tell me how to modify that formula..."

Is the 3,5 the result of a formula or are you manually entering the value in the cell. If you are using a formula, could you please post it so we know what we are working with?

Thanks.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
October 30, 2014 at 04:36:36
Hi DerbyDad,

So, what I need is: I type e.g. "3,5" --> Enter --> displayed value is "3,5%"
But using the cell formula that I found pressing CTRL+1 (shown in original post), the displayed value is "0,35%", so there must be division by 10 somewhere on the way...
For that reason this formula has to be modified (everything is OK except of the division by 10) but I don't know howto do this.
Hope to have explained my problem...
;-)
Zennon



Report •

#3
October 30, 2014 at 05:30:42
re: "But using the cell formula that I found pressing CTRL+1..."

By this I assume you mean a Custom Format, not a "cell formula".

A cell formula, at least to me, indicates something like =SUM(A1:A10) or
=IF(A1>10,"Yes","No"). What you seem to be discussing is a Custom Cell Format.

OK, so assuming that you are manually entering 3,5 in a cell, I am able to produce 3,5% in three different ways. (Please note that my system uses 3.5, not 3,5 so I am not testing your exact situation. That shouldn't matter, but I want to make sure that we include all factors.)

My three ways to get 3.5% displayed:

1 - Before entering 3.5, I clicked the % button. After entering 3.5 the display was 4%. I clicked the Increase Decimal button to display 3.5%. After that, all values entered show the percentage with a single decimal place.

2 - I selected a cell, pressed Ctrl+1, chose Number...Percentage...Decimal Places: 1. Entering 3.5 displays 3.5%. After that, all values entered show the percentage with a single decimal place.

3 - I selected a cell, pressed Ctrl+1, chose Custom and used 0.0% as my Custom Format. After that, all values entered show the percentage with a single decimal place.

If none of those methods work for you, we will need more detail into exactly what you are doing/trying to do. Keep in mind that we can not see your spreadsheet from where we are sitting, so in order to help us help you, you need to be very specific when explaining what you are doing/trying to do.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
October 30, 2014 at 05:53:25
Perfect, works fine!
Now I can see how simple questions may become intricate in unexperienced heads
;-)))
The only one tiny inconvenience is that in the formula bar you also see the percent symbol (in the solution with the non-standard formatting it doesnt appear).
In other words, in the option with non-standard cell format you work on real numbers that are only accompanied by "%" symbol in the cell
BTW, looking at this above mentioned formula, do you have any idea howto get rid off the "division by 10" in it?

Once again: thanks a lot - as usual, your hints are a big help!
:-)

Zennon


Report •

#5
October 30, 2014 at 06:04:17
re: "BTW, looking at this above mentioned formula..."

Either you didn't understand what I was saying in my earlier response regarding Custom Formats vs. Cell Formulas or I am still not clear on what you are asking.

What "above mentioned formula" are you refering to?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
October 30, 2014 at 06:41:16
Hi DerbyDad,

I meant this format: "_-* # ##0,00 %_-;-* # ##0,00 %_-;_-* "-"?? %_-;_-@_-"

Thanks a lot!

Zennon


Report •

#7
October 30, 2014 at 09:34:58
✔ Best Answer
Eventually, through multiple testing and checking I've found something like this:
_- # ###,0_;"%" working correctly.

:-)


Report •


Ask Question