Fix Incorrect Percentages with this Paste-Special Trick
Sometimes we get values in our Excel sheets in such a way that the % sign is omitted. So instead of the value being 23%, it is 23. Now, you can very easily correct this by editing the cell and adding a % sign at the end. But what if you have 100s of rows of data. You can’t do this to every cell. (You can not just format the cells to % format either, excel shows 23 as 2300% then). There must be some simple and intuitive solution for this … umm.
Of course there is..
To clean up incorrect percentages just follow these 4 steps.
- Type 100 in an empty cell. Now copy the cell by pressing CTRL+C
- Select cells with incorrect percentages, and press ALT+E S (alternatively right click and select Paste Special)
- Now, Select “Divide” from operation area. See it aside.
- That is all. We have divided all the values in incorrect percentage cells by 100. Make sure the cells are formatted in % style to show 0.23 as 23%.
Bonus: You can remove % signs by multiplying cell values with 100.
Bonus*Bonus: You can convert a bunch of hours to days by dividing them with 24, minutes to hours by dividing with 60 etc.
Bonus Bonus: 15 more ways to use Excel’s paste special. Do you know all of them?
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
Thank you and see you around.
Leave a Reply
|« Pivot Table Tricks to Make You a Star||Excel Links of the Week – now even more downloads edition »|