Calculate Discount Percentage Using Formula in Excel

A lot of businessmen or students who keep journals about their expenditures or earnings sometimes require the use of Percentages in their calculations. If you don’t know how to show your findings in percentage mood we can make your life easier. In this article, we will discuss how you can calculate discount percentage using formula in Excel. We also discussed the mathematical formula for your understanding.

Contents hide

📁 Download Excel File

Learn to Calculate Discount Percentage Using Formula in 2 Different Ways in Excel

1. From Price Difference

2. From Price Ratio

Calculate Discount Percentage Using VBA in Excel

📝 Takeaway from This Article

Conclusion

📁 Download Excel File

You can download this workbook to practice for yourself.

Calculate Discount Percentage with Formula.xlsm

Learn to Calculate Discount Percentage Using Formula in 2 Different Ways in Excel

In this article, we have discussed 3 simple methods to calculate discount percentage in Excel. We have a dataset of some products with their hypothetical listed prices and selling prices to help you guide through the process. We will calculate the discounts on these products with simple methods and in a very short time.

Now let’s get onto calculating our discount percentage in Excel. We’ll use the price difference to calculate the discount given.

Formula:

Method 1

1. From Price Difference

Before we take a look at our formula, let’s modify our cell where we want to display the discounts as percentage values. To change our cells into percentage format, just do as I do.

At first, Select the cells you want to change into percentage format. We are selecting E6:E10.
Next, under Home Tab, go to the Number group where you will see Percent Style Button.
Click on it and the selected cells will show the Percentage value.

You can also press Ctrl+Shift +“%” shortcut to do the same thing.

Now, we will put this formula in our Formula Bar.

⬇️⬇️ STEPS ⬇️⬇️

First, we select our display cell which is E6.
Second, In the Formula Bar, we used the above-mentioned formula like this.

=(C6-D6)/C6

Press Enter and your discount percentage will show.

Use Fill Handle to copy the formula to the rest of the Rows. Notice we didn’t multiply the results by 100. That is because we already modified the cells to show percentage values so Excel automatically multiplies the results by 100.

If you want to see percentages with decimal values instead of integer and rounded versions, just select the cells.
Then Right-click on your mouse and select Format cells from the context menu or press Ctrl+1.

In the Format Cells dialogue, select Percentage from the Number box and then specify the number of decimal places you want in the Decimal places We are selecting 2.

Finally, click OK to apply the changes.

Method 2

2. From Price Ratio

Another way we can replicate the results is by using the price ratio. Here’s how it works.

Let’s see how we can apply the formula in Excel.

⬇️⬇️ STEPS ⬇️⬇️

Select our display cell E6.
Copy the following formula in the Formula Bar.

=(1-(D6/C6))

Now, hit Enter to see the result.

As usual, drag or use Fill Handle to copy the formula.

Calculate Discount Percentage Using VBA in Excel

We can also automate the process with a simple VBA script. But we need to enable the Developer tab in our Ribbon. Find here how to access the Developer tab.

Let’s write some code to calculate the discount percentage with the VBA script.

⬇️⬇️ STEPS ⬇️⬇️

First, we select the Developer Tab and access Visual Basic at the left corner of the Ribbon.

Clicking on Visual Basic will show a window(Microsoft Visual Basic for Applications) where we will write our code. Click Insert and then Module.

Copy and paste the following code into the module.

Sub calculating_discount()
Dim value As Long
With ThisWorkbook.Worksheets("VBA")
value = .Cells(Rows.Count, "C").End(xlUp).Row
.Range("E6:E" & value) = "=1-(D6/C6)"
End With
End Sub

Now, we can press F5 or click on Run and click Run Sub/Userform to get every one of our discounts at the same time.

Compared to previous methods, you don’t have to drag or use the Fill handle to copy the formula.

In the Rows. Count, we wrote column C because that’s where our Listed Prices are and in the Range property we are giving Three things to consider. First, starting Row, second, the address to print our results, and finally calculate as long as it gets the Listed Prices. After that, we mentioned the formula to calculate discount percentages.

📝 Takeaway from This Article

📌 Showed how we can change our value format from Ribbon.

📌 Explained Two mathematical formulas for readers to understand.

📌 Showed VBA script to automate the process.

Conclusion

I hope you enjoyed today’s article and learned to calculate discount percentages with ease. If you have any queries, feel free to comment below. I will try to answer them as soon as possible. Keep using Excel and please visit Excelden.com for more Tutorials. Thank You.