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.
📁 Download Excel File
You can download this workbook to practice for yourself.
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.
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.
- 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.
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.
- Now, hit Enter to see the result.
- As usual, drag or use Fill Handle to copy the formula.
📕 Read More: 3 Quick Ways to Add 20 Percent to a Price in Excel
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.
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.
- How to Calculate Cumulative Percentage in Excel
- Calculate Percentage Change with Negative Numbers in Excel
- 2 Examples to Convert Percentage to Basis Points in Excel
- Calculate Percentage in Excel Based on Conditional Formatting
- 5 Easy Methods to Add 10 Percent to a Number in Excel
- 2 Ways to Calculate Bacterial Growth Rate in Excel
- Calculate Percentage in Excel Using Absolute Cell Reference