How to Calculate Discount Percentage Formula 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.

Worksheet of Calculate Discount Percentage Formula

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.

Discount Price - Calculate Discount Percentage Formula

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)

Discount Price using 1 - Calculate Discount Percentage Formula

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.

AutoFill Discount Price - Calculate Discount Percentage Formula

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.

Discount Percentage - Calculate Discount Percentage Formula

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.

Percentage Formation -Calculate Discount Percentage Formula

Now let’s use another formula.

1 – (Discount Price / Original Price)

Discount Percentage using 1 - Calculate Discount Percentage Formula

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.

AutoFill Discount Percentage - Calculate Discount Percentage Formula

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

Original from Discount - Calculate Discount Percentage Formula

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.

AutoFill Original from Discount - Calculate Discount Percentage Formula

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

Decimal Decrease-Increase - Calculate Discount Percentage Formula

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

Decimal Decrease - Calculate Discount Percentage Formula

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

More Numbers Formats - Calculate Discount Percentage Formula

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.

Set Decimal places at More Number Formats box - Calculate Discount Percentage Formula

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.

Calculator - Calculate Discount Percentage Formula

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

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo