• Excel Tips - Paste Special with Formulas

  • 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’.

    Paste Special with Formulas 1

    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.

    Paste Special with Formulas 2

    Now, when you change the number format the correct percentage will be shown.

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

     

    Read about our Excel Development Projects

    A leading Auctioneer came to xlteq to help streamline and automate their global headcount reporting in Excel. This included real-time clarification of total headcount coupled with detailed location status functionality.

    Find our more 

    A Danish financial services company asked our Excel Consultants for support with preparing their client investment portfolios. This task now takes less than a minute per customer and is delivered in a format selected by the client then presented as a visually effective Investment Portfolio Dashboard.

    Find out more