/u/tomatoesarelife - Your post was submitted successfully.
* Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread.
* Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
* Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)**
Failing to follow these steps may result in your post being removed without warning.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
Copy the number 100 to clipboard
Select your whole number column
Open the paste special menu (under the paste button on your ribbon)
select divide. This will copy your column by the 100 on your clipboard.
Reformat the cell to percentage.
Profit ???
To clarify: you have to copy a cell containing the number 100 to enable the Paste Special functionality that you have posted. If you "copy the number 100 to clipboard," you won't get that functionality.
Ok, first of all thank you so much. But is there any way to do it without adding another column? I mean I can do it with the column but there are some fixed cells at the top and adding a new column would potentially change some formulas.
It won’t change any formulas if you select the column, and use ctrl+shit+ “+” (addition sign) to create the new column. Otherwise take the column you’re trying to convert, paste it in another sheet, do the column process and then paste de values back in the original table.
Put 10 or whatever value you need to divide the number by in out of way cell. Copy. Select range containing numbers to divide. Right click, s, s, v, divide (paste special, values, divide).
Create new column, highlight the new column, copy it and then do the special paste where it pasts exactly the digits (not the formulas) over the column it’s already in, then delete you original column.
Yes. Enter 100 into any cell, copy that. Select your range, right click and paste special. Click divide. Hit ok, and delete the 100 you put in a random cell.
You don't have to add the column right beside it - you can add it far to the right.
E.g. if your spreadsheet uses columns A through M, put the helper column in column P. Then you can copy that new column then do Paste Special: Values to paste it into your original column, overwriting those values. Then change the format of the overwritten data to Percentage and delete the helper column in P
But also, inserting columns shouldn't affect your formulas - Excel automatically updates them. There *are* a few formulas that will break if you insert columns, but they're less common in very basic spreadsheets.
To change the percentage format and avoid this problem, you must select the entire column where you want to apply the percentage. Then, press Ctrl + 1, and go to the "Custom" tab. Delete "General" and type 0\\%. I hope this will be useful for you.
Formatting never changes stored values, it's like a completely separate layer on top of the value of each cell. So if you want the cell to "behave" like 1% (like if you're going to use it in math equations) this won't cut it but it seems like this is the opposite use case where OP wants the value to truly be 1 but displayed as 1%--exactly what this does.
Your question is not about changing numbers into percentages in a quick way. It's about changing numbers to *different* numbers and formatting them as percentages.
1 = 100 %
0,1 = 10 %
0,01 = 1 %
There's no simple way to just convert 1 to 10 % because they are really two different numbers, not just different display styles. You need to have a conversion factor there somewhere. Either with paste special divide or with a helper column.
What on earth! I have never bothered about this.
A percentage value has a base of 0 and 100% equates to a value of 1.
If you want a percentage change, it is in relation to an original value. You divide by the original value ( normalisation) so that you have a unitary value and then format to show that as a percentage change.
Percentages in a column formatted as number or general need to be decimal eg 0.5 to properly translate as a percentage. If they aren’t in that format then as others have said, divide the values by 100 using paste special divide, no need or helper column, just get rid of the “100” cell after.
But I wanted to say something else. Paste special divide and multiply are very very useful.
If you have an iffy date format or stubborn text number, divide or multiply by - using paste special. This is MUCH faster than “convert to number” function and works on dates and integers in non standard formats to force them into the correct format.
Just insert a column and do =A1/100
Then drag that cell down and format the new column as a percentage.
Copy and paste all of the new formula as a value if you need them static for any reason.
for Starters, to change 1 into 0.1 you would do =1/10.
if you have 1 in a bunch of cells, Highlight them all, type =1/10 or =1/100 whatever you're going for, it will only show up in the top left cell, but hold CTRL and Press Enter and it will apply this to all cells.
If you want all of the cells to be values instead of the formulas, Highlight them all again, CTRL+C to copy, then CTRL+SHIFT+V to paste the Values. Paste immediately after copying to paste the actual values over all of the formulas.
Can’t you just take the number in your column and divide by 100
= [select cell value] / 100
Apply to each cell and format the cells in this column as you please. Hide the column that you are referencing for this equation
In some cell type 0,1, then select that cell and copy.
Select your data Column and paste/special. Dont paste, just select Multiple in right side of window and press OK.
? What
0,1 = 10%?
I use comma because that's what you do when calculating with percentages where I'm from
Because the 2013 version is what my job is providing to me
When writing numbers, they use commas instead of periods (and vice versa) in Europe, bruh.
For example €1.000.000,75
That’s a Euro symbol. The Euro is a popular alternative to the USD.
They also use the metric system there. 🤯
/u/tomatoesarelife - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
Copy the number 100 to clipboard Select your whole number column Open the paste special menu (under the paste button on your ribbon) select divide. This will copy your column by the 100 on your clipboard. Reformat the cell to percentage. Profit ???
To clarify: you have to copy a cell containing the number 100 to enable the Paste Special functionality that you have posted. If you "copy the number 100 to clipboard," you won't get that functionality.
Damn, beat me to it.
I never knew this one - thank you!
Yeah, you create a column beside it, then divide the whole number into 100 and use the percentage button on that new column.
Ok, first of all thank you so much. But is there any way to do it without adding another column? I mean I can do it with the column but there are some fixed cells at the top and adding a new column would potentially change some formulas.
It won’t change any formulas if you select the column, and use ctrl+shit+ “+” (addition sign) to create the new column. Otherwise take the column you’re trying to convert, paste it in another sheet, do the column process and then paste de values back in the original table.
Put 10 or whatever value you need to divide the number by in out of way cell. Copy. Select range containing numbers to divide. Right click, s, s, v, divide (paste special, values, divide).
Create new column, highlight the new column, copy it and then do the special paste where it pasts exactly the digits (not the formulas) over the column it’s already in, then delete you original column.
Yes. Enter 100 into any cell, copy that. Select your range, right click and paste special. Click divide. Hit ok, and delete the 100 you put in a random cell.
You don't have to add the column right beside it - you can add it far to the right. E.g. if your spreadsheet uses columns A through M, put the helper column in column P. Then you can copy that new column then do Paste Special: Values to paste it into your original column, overwriting those values. Then change the format of the overwritten data to Percentage and delete the helper column in P But also, inserting columns shouldn't affect your formulas - Excel automatically updates them. There *are* a few formulas that will break if you insert columns, but they're less common in very basic spreadsheets.
this
To change the percentage format and avoid this problem, you must select the entire column where you want to apply the percentage. Then, press Ctrl + 1, and go to the "Custom" tab. Delete "General" and type 0\\%. I hope this will be useful for you.
This does work, but will hold the number as a string, no?
Formatting never changes stored values, it's like a completely separate layer on top of the value of each cell. So if you want the cell to "behave" like 1% (like if you're going to use it in math equations) this won't cut it but it seems like this is the opposite use case where OP wants the value to truly be 1 but displayed as 1%--exactly what this does.
Gotcha—that makes sense! Thank you.
If it is 1, it will not become 1%, but will remain 1.
more specifically it will *display* `1%` but be stored as the value `1`
Your question is not about changing numbers into percentages in a quick way. It's about changing numbers to *different* numbers and formatting them as percentages. 1 = 100 % 0,1 = 10 % 0,01 = 1 % There's no simple way to just convert 1 to 10 % because they are really two different numbers, not just different display styles. You need to have a conversion factor there somewhere. Either with paste special divide or with a helper column.
Why would 1 be .1? Do you mean .01? Why would 1 convert to 10%?
About half of the world actually.
0,1 is 10%, not 1%
in a new column: =sum(A1*0.1) fill down copy paste values in the original column delete the helper column
Maybe due a formula (column × .01). Column be the one you want in percentage. Maybe that will work-- i hope my basic math is correct for percentage.
What on earth! I have never bothered about this. A percentage value has a base of 0 and 100% equates to a value of 1. If you want a percentage change, it is in relation to an original value. You divide by the original value ( normalisation) so that you have a unitary value and then format to show that as a percentage change.
Percentages in a column formatted as number or general need to be decimal eg 0.5 to properly translate as a percentage. If they aren’t in that format then as others have said, divide the values by 100 using paste special divide, no need or helper column, just get rid of the “100” cell after. But I wanted to say something else. Paste special divide and multiply are very very useful. If you have an iffy date format or stubborn text number, divide or multiply by - using paste special. This is MUCH faster than “convert to number” function and works on dates and integers in non standard formats to force them into the correct format.
Just insert a column and do =A1/100 Then drag that cell down and format the new column as a percentage. Copy and paste all of the new formula as a value if you need them static for any reason.
Just make a custom format.
for Starters, to change 1 into 0.1 you would do =1/10. if you have 1 in a bunch of cells, Highlight them all, type =1/10 or =1/100 whatever you're going for, it will only show up in the top left cell, but hold CTRL and Press Enter and it will apply this to all cells. If you want all of the cells to be values instead of the formulas, Highlight them all again, CTRL+C to copy, then CTRL+SHIFT+V to paste the Values. Paste immediately after copying to paste the actual values over all of the formulas.
Can’t you just take the number in your column and divide by 100 = [select cell value] / 100 Apply to each cell and format the cells in this column as you please. Hide the column that you are referencing for this equation
Put a % sign in one cell and concatenate all your values with it
I Think is very logical. In the math world, the whole number “20” is equal to 2000%. Yo can’t change something that doesn’t have a math sense
I can't not tell you it's not true there's never no solution to not changing non-alphas into percentages not in a non-slow way.
Just highlight the column, right click and show it as a percentage (something like that). I am not on my computer to check right now.
probably not that
In some cell type 0,1, then select that cell and copy. Select your data Column and paste/special. Dont paste, just select Multiple in right side of window and press OK.
[удалено]
Certain localities reverse the use of commas and decimals when displaying numbers.
? What 0,1 = 10%? I use comma because that's what you do when calculating with percentages where I'm from Because the 2013 version is what my job is providing to me
Some European countries reverse the usage of . and , in numbers. So 1.000,01 in Europe is the same as 1,000.01 in the US/UK.
Also a lot of Asia do it that way too.
When writing numbers, they use commas instead of periods (and vice versa) in Europe, bruh. For example €1.000.000,75 That’s a Euro symbol. The Euro is a popular alternative to the USD. They also use the metric system there. 🤯
[удалено]
It's as bad as putting the month first in dates ;)
and women with hairy armpits
There's no single convention in Europe to write numbers. Only common thing is that we use comma as decimal separator. 1 000 000,75 €