Excel Developer Tips - Paste Special with Formulas
Have you even wanted to change a series of numbers into a percentage only for excel to inflate the value by 100 when you click the ‘%’ button or change the number format? (eg. Changing the format of a cell containing the value 83 to a percentage changes it to 8300% !!)
What you first need to do is to divide each value by 100. This can easily be achieved by using Paste Special.
First enter 100 into an empty cell and copy it.
Select the range you want to fix, right-click and select ‘Paste-Special’.
Click the "Divide" radio button in the Operations section and ‘OK’ to confirm and the highlighted values will all be divided by 100 to give the correct value.
Now, when you change the number format the correct percentage will be shown.