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.

📁 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.

dataset calculate discount percentage formula

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


price difference formula calculate discount percentage 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.

percentage ribbon calculate discount percentage formula

  • 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.


price difference formula in Bar calculate discount percentage formula

  • Press Enter and your discount percentage will show.

price difference result calculate discount percentage formula

  • 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.

format cells calculate discount percentage formula

  • 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.

decimal places calculate discount percentage formula

  • Finally, click OK to apply the changes.

decimal resulr calculate discount percentage formula

📕 Read More: 3 Ways to Add Percentage to Price in Excel with Formula

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.


price ratio formula calculate discount percentage formula

  • 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.

developer module calculate discount percentage formula

  • 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 for more Tutorials. Thank You.

Related Articles

(Visited 121 times, 1 visits today)
Hassan Shuvo

Hassan Shuvo

Hello, I am Mehedi Hassan Shuvo.I am an Engineering graduate from Bangladesh University of Engineering and Technology. I love reading Thriller books, watching Anime, and playing Cricket. I also love learning about new software. Excel is one of my favorite ones by far. My efforts will be worthwhile if I can utilize my expertise to assist anyone. If you find any faults in my writing please let me know.I will try to correct them as I am learning everyday.

We will be happy to hear your thoughts

Leave a reply