Inside The Web
    Facebook Twitter Instagram
    Inside The Web
    • Office
      • Excel
      • Ms Word
      • OneDrive
      • Google Docs
      • Google Sheets
      • Google Drive
    • Email
      • Gmail
      • Outlook
      • Emal Etiquette
    • Office Etiquette
    • Productivity
    Inside The Web
    Home»Office»Excel»How to Calculate Discount Percentage in Excel

    How to Calculate Discount Percentage in Excel

    Asmi DhakalBy Asmi DhakalMarch 31, 2023 Excel 3 Mins Read

    You can use arithmetic operators to perform simple calculations like calculating the discount percentage in Excel. Additionally, if you’re into using functions, you can create your own function using the Visual Basic Editor. To create your own function, ensure you have the developer tab enabled from the Excel Options.

    Create a Formula to Calculate the Discount Percentage

    You will need to use the asterisk {*}, parentheses {()}, and the hyphen {-} operators to construct a formula. The asterisk operator is for multiplication, parentheses prioritize an operation in the calculation, and the hyphen operator performs subtraction.

    Let’s put these operators together and create a formula. The formula would be in the following format:

    1-((discounted price)/(initial cost))

    When you divide the discounted price by the initial cost, you will get the fraction you actually paid from the initial cost. Subtracting it from 1 will give you the fraction you were exempted from paying.

    After using this formula, you will have to convert your cell from General to Percentage. You can make this conversion from the Number section in the Home ribbon, or simply use Ctrl + Shift + % Excel shortcut.

    Data range

    Now to put this formula into application. In this table, we have five values. In column A, there is the cost before the discount, and in column B, is the discount price. In cell C2, we entered the following formula:

    =1-(B2/A2)
    Calculate Discount Percentage

    This resulted in 0.15. To change it into a percentage, we used the Ctrl + Shift + % shortcut. Then, copy the formula in the rest of the cells, we used Flash Fill. Make sure you haven’t used absolute referencing in the formula as it will cause Excel to not change the references in the filled cells.

    Create a Custom Function to Calculate Percentage

    Constructing the formula we made in the method above will be tedious if you work in sales. This is because you’ll have to enter the formula each time you make such calculations. Unfortunately, Excel doesn’t currently have a dedicated function to calculate the discount percentage. 

    However, we can create our own function using the Visual Basic Editor. We have divided this method into two steps, one is creating the function, and the second, applying the new function on our spreadsheet.

    Create Function

    We will be creating a custom function named “Calc_Disc_Prtg” to calculate the discount percentage. Follow these steps to create your own function:

    1. Head to the Developer tab and select Visual Basic.
      Visual Basic Developer tab
    2. Right-click on your sheet from the sidebar.
    3. Select Insert > Module.
      Insert Module in VBA
    4. On the window on your right, enter the following code:
    Function Calc_Disc_Prtg(I_cost As Currency, D_cost As Currency) As Double
    Result = (1 - (D_cost / I_cost))
    Calc_Disc_Prtg = Result
    End Function
    Code to calculate percentage in Excel VBA

    Use Custom Function

    Now that we’ve created our function, we can use them on our grid. Insert Calc_Disc_Prtg like you would use any other function in Excel. Here are the arguments used by our custom function:

    =Calc_Disc_Prtg(initial cost, cost after discount)

    Here is how we applied our new function. In cell C2, we entered the following formula:

    =Calc_Disc_Prtg(A2,B2)
    Custom Formula in Excel

    We use Ctrl + Shift + % to convert our decimal value to a percentage. Then, we used Flash Fill to copy this formula to the other cells. Easy right?

    Formula
    Asmi Dhakal

      Asmi is a content writer specializing in MS Office. She loves writing Excel-related content to assist other users to learn their way around the program. She started using Excel when she first entered the industry as a marketing intern. Soon, she realized that learning the often-overlooked spreadsheet program was no piece of cake. In her articles, she attempts to brief out complex topics to help beginner to intermediate-level Excel users. Asmi uses Excel for everything, including creating a grocery list to managing her personal finance. She’s a bit dramatic when it comes to the Flash Fill feature; so much so that could write a eulogy out of it. When she’s not working, you’ll find her learning a new recipe, or cleaning up her mess (out of the failed recipe).

      Related Posts

      Excel By Nisha Gurung

      How to Compare Two Columns in Excel Using VLOOKUP

      Excel By Nisha Gurung

      How to Share Excel Files with Multiple Users

      Excel By Nisha Gurung

      How to Filter Based on a List in Excel

      Add A Comment
      Table of ContentsToggle Table of ContentToggle
      • Create a Formula to Calculate the Discount Percentage
      • Create a Custom Function to Calculate Percentage
        • Create Function
        • Use Custom Function
      • Home
      • About Us
      • Privacy Policy
      © 2025 Inside The Web

      Type above and press Enter to search. Press Esc to cancel.