Paste Special with Formulas in Excel

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.

This also works to instantly add, subtract, or multiply numbers.


Contact us for a free consultation

Either call us now on 020 3817 6945 or fill out the form below and one of our team will be in touch.

The contact form requires that you configure reCAPTCHA in the site configuration.

The contact form requires that you select an email template.