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

✔ Best Answer

Eventually, through multiple testing and checking I've found something like this:

_- # ###,0_;"%" working correctly.:-)

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.

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

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 CellFormat.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

exactsituation. 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.

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

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.

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

Thanks a lot!

Zennon

Eventually, through multiple testing and checking I've found something like this:

_- # ###,0_;"%" working correctly.:-)

Ask Your Question

Weekly Poll

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History