Formula to Calculate Discount Percentage in Excel

Microsoft Excel is a powerful tool for basic and complicated calculations. With the help of it, you can easily calculate the percentage value, like discount percentage. In today’s session I’m going to show you how to calculate the discount percentage in Excel. The formula, I’m going to use, will work on all the versions of Microsoft Excel. For conducting this session I’m using Excel 2019, you can use your preferable version. Now without further any due let’s begin today’s session.

But, first things first, let’s know about the Excel sheet first, which will be used as a base for our examples for today’s session.

There are three separate tables on today’s sheet. Each of the tables has four columns. Since usually, discount refers to some consumable items, each table has a common column, Product. For the table at the top left we are going to calculate the selling price of each product after giving discounts.

For the table at the top right, we will count the actual summer price from discount percentage and selling price. Then For the bottom one, we will see how to compute the discount percentage.

Practice Workbook

For your convenience, I have shared the Excel sheet. You can download it from the link below.

Calculation of the Discounted Price

At first, we will calculate the discounted price from the original price and a given percentage value. The formula for this can be written as follows

Original Price – (Original Price * Discount Percentage)

Let’s write the formula in Excel.

We subtracted the multiplied value of Fall Price and Fall Discount from Fall price of the first product (Asus ZenBook). From there we got the discounted price (Fall Selling Price). Our calculations have been done using Cell Reference.

Now, we can write the formula in a different way. Let’s see that formula.

Original Price * (1-Discount Percentage)

Having doubt on how this formula works? No worries I’ll tell you, when you are going to count a discounted price, means price will be decreased, your updated price will be (100% – Given percentage ) of the actual price. 1 is the decimal equivalent of 100%.

So, when you are subtracting a percentage value from 1, it will subtract the decimal equivalent of that percentage from 1.

Now feel free to use any of this formula to calculate the rest of the value from this table, or simply exercise the AutoFill  feature of Excel.

When you have to calculate for a large data set, write the suitable formula once and then use Excel AutoFill.

Calculation of the Discount Percentage

For the purpose of discount percentage calculation, we can use the formula as given below.

(Original Price – Discount Price)/Original Price

Let’s write the formula on Excel.

We subtracted the Fall Festival Price from Fall Price and divided the result by Fall Price. And that gave us the Festival Discount.

In case you didn’t see the value in percentage format, then explore the Number section on the Home tab and Select Percentage.

Now let’s use another formula.

1 – (Discount Price / Original Price)

Wonder how it works! Okay let’s debug, we know that 1 is the decimal equivalent of 100%. Now, when we are dividing two prices, it gives us a decimal value. Every decimal value has an equivalent percentage value. So, it seems, we are subtracting two percentage values in lieu of decimal values.

Now, you can use either of the formulas to calculate the rest of the product from the table.

In this example, we have only three products for each table, but in a real scenario you may encounter a large dataset. For calculation in a large dataset, adapt a formula first and exercise AutoFill.

Calculation of the Original Price from Discounted Price

Now in some scenario, you may need to calculate the actual price of a product from a given discounted price and discount percentage. In this section, we will see how to do that.

Let’s see the formula first.

1/(1-Discount Percentage)* Discount Price

Here we divided 1 by the subtracted value between 1 and Summer Discount, then multiplied with Summer Selling Price. That gave the actual Summer Price.

The concept of using 1 is similar to that discussed earlier. 1 is the decimal equivalent of 100%. Subtracting percentage value from 1 means (100% – Percentage value) and we are dividing 1 by this result means, dividing 100% by the result of (100% – Percentage value).

Now feel free to write the formula for the rest of the products or use AutoFill.

Useful Tips

Before wrapping this up, I’m going to give you a few tips that may be useful throughout your journey.

You saw the summer price we have calculated, came in decimal values (511.36). But in the real world, you hardly find any product prices like this. To round this up, go to the Number section of the Home tab, you will find these options (Increase Decimal, Decrease Decimal).

Click to the Decrease Decimal since we need to decrease it.

I clicked twice on the option. If you want to increase decimal, use Increase Decimal.

You can change decimal in another way also. On the Number section, click on the list icon then go to More Numbers Formats

Now choose how many decimal points you want. I set it 0 from 2, you may want more or less, feel free to do that.

Conclusion

That’s all for today’s session. I have tried listing a couple of ways to calculate discount percentage in Excel. Hope you will find this helpful. Feel free to comment if anything seems hard to understand. Let me know which of the options you like most and why. You can also write your own way to do the task.

Bonus

You can use today’s practice workbook as a calculator.

At 1 Provide Actual Price and Discount Percentage, it will calculate the Discounted Price.

At 2 Provide Actual Price and Discounted Price, it will calculate Discount Percentage.

At 3 Provide Discount Percentage and Discounted Price, it will calculate Actual Price.

Further Readings

• Percentage Difference between Two Percentages Excel (2 Easy Ways)
• How to Calculate Sales Growth Percentage in Excel
• Calculate Year over Year Percentage Change in Excel (Advanced Technique)
• How to Calculate Percentage of Sales in Excel
• Calculate Average Percentage in Excel [Free Template+Calculator]
• How to Calculate Variance Percentage in Excel
• How to find percentage between two numbers in Excel
• How do you Calculate Percentage Increase or Decrease in Excel
• Excel formula to calculate percentage of grand total (4 Easy Ways)
• Calculate Excel percentage difference between two numbers (using formula)