I'm having a problem formatting a column so that it shows a percentage The way I need

[Eaglepi]

I have a worksheet that I'm keeping track of the cost-of-living increases for Social Security retirement. One of the columns that I have is for each year and what increase is given. Typically the amounts are given as follows

3.0
1.7
2.8

I don't think it will ever get double digits but even if it does I would like it to display as I indicated above.

Currently, I've selected percentage for the format of the column and for example 2.8 shows up like 280% and that's using one decimal point.

I'm not a very experienced user in Excel I just know the very basics so if something is a bit complicated an explanation would be appreciated, I also like to do things without getting into the VBA.

Thanks in advance

[XLent]

You could perhaps apply a custom number format to your range of cells along lines of: 0.00"%"

the use of quotations around the % means XL will see it as a literal string, rather than a percentage - so 3.0 will display as 3.0 % as opposed to 300 %

[Eaglepi]

I tried as you suggested to set the column to accustom and I selected 0.0% Everything is still showing up like 2.8 is 280%
so I'm obviously doing something wrong?

[XLent]

Per the example, format should be 0.00"%"

i.e. the % should be encased in quotation marks.

[Eaglepi]

Keep in mind I'm not very experienced in Excel, but something still is in right I selected custom I typed in "0.00%" then the whole column goes to 0.00% ???

[XLent]

Right, so just the % ... copy and paste below into your custom format window:

0.00"%"

[Eaglepi]

I have done that and it changes every number that I have in the column all the way down to 0.00%

[XLent]

I suspect you will need to post a sample file -- attached illustrates my suggestion, using the values you outline in your initial post.

[Eaglepi]

I guess I really don't understand why an example would be needed something this simple. Pick any column, and that column I have entered things like the following,
3.0
2.1
2.8

Since I type them in I'm sure Excel is seeing them as text not a number, but I convert them to a number then there not a percentage? I have no special formatting nothing. I'm trying to figure out how to format the cell so the above percentages will show up as actual percentages like 2.8%

[XLent]

Eaglepi, please refer to the previously attached which illustrates proposed format given values stipulated in your first post.

If, when applying the same to your file, this does not work for you - you will need to post an example in which you have applied the same.

[Eaglepi]

Eaglepi, please refer to the previously attached which illustrates proposed format given values stipulated in your first post.

If, when applying the same to your file, this does not work for you - you will need to post an example in which you have applied the same.

I downloaded your example I opened it it looks fine but when I enable editing to check the format it doesn't say what format it is. Here is my example that you requested

[XLent]

Follow these steps with your example file:

1. highlight column A (click on "A")
2. right click and select Format Cells
3. click on Custom option
4. replace General with 0.00"%"
5. click OK

[Eaglepi]

Follow these steps with your example file:

1. highlight column A (click on "A")
2. right click and select Format Cells
3. click on Custom option
4. replace General with 0.00"%"
5. click OK

Okay I see where I might've gone wrong, given you the example was probably the incorrect way to do it. This is exactly how it looks on my worksheet and as you can see they don't show up as 2.8 and that's what I'm trying to accomplish.

[Norie]

Eaglepi

Are the values actually percentages?

[XLent]

Right, ok, so assuming you want 800% to be 8.0, 640% to be 6.4 etc ...

highlight column B, right click "Format Cells", select Number, enter 1 for Decimals, click OK.

If that's not what you're trying to do I can only apologise.

If you want 800% to be 8.0% use the previous format provided.

[Eaglepi]

Right, ok, so assuming you want 800% to be 8.0, 640% to be 6.4 etc ...

highlight column B, right click "Format Cells", select Number, enter 1 for Decimals, click OK.

If that's not what you're trying to do I can only apologise.

If you want 800% to be 8.0% use the previous format provided.

Perfect. I apologize to the previous repliers I may have misled you by saying percentages because the actual numbers are a percentage. However for my purpose I don't need them to be a percentage I guess the easiest way is just to keep him in the number format so they show up the way I want. Ideally I would like them to show up 8.0% but nothing I do from the prior post will give me that result. So unless anybody else has any more suggestions I guess I can just be okay with the percentages showing up is just a number.

[Entregan]

When all else fails...

Contenating can be your friend.
Turn the cell value in Column B back to general
Follow this formula as shown in Column D

=NUMBERVALUE(B1&"%")

This takes the value as you provided,
and simply adds the % to it.
Then format that Column (click the corresponding letter) and click the % button under the Number pane (Or simply select 'Percentage' from the dropdown

[Eaglepi]

When all else fails...

Contenating can be your friend.
Turn the cell value in Column B back to general
Follow this formula as shown in Column D

=NUMBERVALUE(B1&"%")

This takes the value as you provided,
and simply adds the % to it.
Then format that Column (click the corresponding letter) and click the % button under the Number pane (Or simply select 'Percentage' from the dropdown

I'm not sure if you saw my original post but I said that I know very little when it comes to Excel, just the basics. I do not know what the word Contenating means pertaining to Excel So unfortunately it can't be my friend. I took your formula And pasted it in the first cell of the column that I have the numbers in. I get the "NAME?" in the cell. So maybe if you don't mind being a little bit back to basics and give me a step to step I would be happy to try it but I don't understand your reply and obviously I couldn't get your formula to work. I do appreciate your effort trying to help

[XLent]

Attached is your file, with the format proposed back in post #2 -- i.e. displays 8.0% etc so, again, I don't quite follow why you feel this isn't working?

[Eaglepi]

Attached is your file, with the format proposed back in post #2 -- i.e. displays 8.0% etc so, again, I don't quite follow why you feel this isn't working?

Let me try to help you follow, as I stated in the following post after #2 when I followed your directions it did not get me the same thing that you just showed me in the attachment. Maybe I'm entering the custom Number format incorrectly, however I've done it three times exactly how you told me to do it.

Why does it work for you and not me? I'd the first one to like to find out. When I open your download and I click on the column and go to format cells it doesn't show me anything that you've entered custom. So I can even see from your example what you've done. I'm not saying that what you're doing is not working for you I'm just saying I can't get it to work for me and when I look at your example, I would expect to open it click on format cells and see what it is said that. When I do that it shows me nothing. Maybe if you would be kind enough to redo it again and take a screenshot of the format sell section after you enter the custom numbers that would help me see exactly what you've done.

Thanks for your help sorry to frustrate you

[Norie]

Eaglepi

How exactly do you want these values to appear?

If they are percentages you can enter them as such, i.e. the number followed by %.

They will then be treated as percentages in any calculations you do.

If you have already entered them as 5.6 etc. then you could convert them to percentages by dividing by 100.

[Eaglepi]

Eaglepi

How exactly do you want these values to appear?

If they are percentages you can enter them as such, i.e. the number followed by %.

They will then be treated as percentages in any calculations you do.

If you have already entered them as 5.6 etc. then you could convert them to percentages by dividing by 100.

All the numbers already listed and there's quite a few. All of them are like the one you shown above 5.6, when you say dividing by hundred I'm not sure if you're talking about doing something in Excel or simply doing a division of 100.

Don't get me wrong I'm not criticizing anybody who's trying to help me but I think I just don't know Excel well enough because whenever I try to ask what I think is a simple question it turns into a long thread. On this one I think I'll just say thanks for everyone who helped or tried to help I will just leave the numbers as they are. Even though they are a percentage I don't need the percentage sign next to the number for me to know what it means. It's just what been nice.

Thank you very much to everyone who tried to help me I'm sure it was my inexperience is the reason I couldn't get what I wanted but at this point I'm just going to thank everyone again and close this market solved

[Entregan]

Concatenating* Silly keyboard. The file I uploaded was yours modified.
By entering B1 & %

a1=1975
b1=8
c1

=B1&"%

"

By doing that now
C1=8%


It takes the contents of Cell B1 and adds the % sign to the end of it.
Using NumberValue isn't strictly necessary.

Sorry for the confusion. You might check the file I attached. It's already done there.
Concatenating is the long way of using the '&' sign

=CONCATENATE(B1,"%")

[Eaglepi]

Concatenating* Silly keyboard. The file I uploaded was yours modified.
By entering B1 & %

a1=1975
b1=8
c1

=B1&"%

"

By doing that now
C1=8%


It takes the contents of Cell B1 and adds the % sign to the end of it.
Using NumberValue isn't strictly necessary.

Sorry for the confusion. You might check the file I attached. It's already done there.
Concatenating is the long way of using the '&' sign

=CONCATENATE(B1,"%")

Thanks again but your description of what to do just gets me deeper into what I'm not familiar with like I said I appreciate all the help I will just keep the numbers without the %'s long as I know they are at Percentage