Change Price list by fixed percentage in Excel

Posted on 2011-10-28
Last Modified: 2012-05-12
Hi Experts,

I'm trying to update my price list in Excel and add 2.5% to each value in the price list. The price list contains 3 sheets and has some fields that are just text, which should be ignored. I have attached the price list.

Please help!  -2011-.xls
Question by:oxi1
    LVL 14

    Expert Comment

    Here is the help

    Let's say you have a list of values in A1:A100 and you need to increase these values all by 15%.

       1. Enter the number 1.15 into any blank cell and then Copy it
       2. Now select the range A1:A100 and go to Edit>Paste Special
       3. Choose Values from under Paste and then Multiply under Operation and click OK.

    All value will now have increased by 15%

    Author Comment

    I have tried this but nothing happens when I do it. Could it be that Excel 2011 for Mac operates differently?
    LVL 20

    Accepted Solution

    Let's say you want to fix column H on Sheet2. Follow these steps:

    - In I1 create this formula: =IF(H1="","",IF(ISNUMBER(H1),H1*1.025,H1))
    - Copy H1 and paste it in down all the rows to the last one that contains any data in H (in your example that would be down to I35)
    - Select the whole column I and copy it
    - Select the whole column H and go to Edit->Paste Special. Select "values" and press OK.
      If you have merged cells, you will first need to split them, make the copy-paste and merge them again.
    - Delete column I

    Attached is the file with column H on Sheet2 fixed this way

    Author Comment

    @TheAvenger: Thanks for your solution. I'm having trouble following your steps:
    I have created the formula in I1
    - Copying H1 would copy "Price" and paste it all down H?

    Sorry if I'm not getting what you meant, but could you please elaborate?

    Thanks a lot.
    LVL 20

    Expert Comment

    Just select I1 (sorry, it's I1, not H1) this was a typo, select H2-H35 and paste. This will copy the formula down

    Author Closing Comment

    Great way to solve this, thanks a lot!

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now