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.

1. Type 100 in an empty cell. Now copy the cell by pressing CTRL+C
2. Select cells with incorrect percentages, and press ALT+E S (alternatively right click and select Paste Special)
3. Now, Select “Divide” from operation area. See it aside.
4. 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?