How to Calculate Discount Percentage in Excel

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.

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)

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.
   
2. Right-click on your sheet from the sidebar.
3. Select Insert > Module.
   
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

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)

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?