How to Calculate Discount Price in Excel (4 Quick Methods)

Get FREE Advanced Excel Exercises with Solutions!

While calculating discount prices, the benefit of using Excel is- we can use it as a calculator too and it saves huge time for a large dataset if you know some tricky methods. This article will show you 4 quick methods to calculate the discount price in Excel with proper examples.


Download Practice Workbook

You can download the free Excel workbook from here and practice on your own.


What Is a Discount Formula?

In every method, we’ll use the basic mathematical formula for finding discount prices.

The formula is-

Discount Price = Old Price * (1 – Discount Rate)

The formula says that we’ll have to subtract the discount value from the old price that we’ll obtain by multiplying the discount rate with the old price.


4 Ways to Calculate Discount Price in Excel

Let’s get introduced to our dataset first, it represents some cloth items’ old prices and discount rates.


1. Using Mathematical Formula to Calculate Discount Price

In our very first method, we’ll find the discount price by giving input the values in the above mathematical formula directly.

Steps:

  • Activate Cell E5.
  • Then write down the following formula in it-
=135-135*10%
  • Finally, just hit the Enter button to get the result.

Using Mathematical Formula to Calculate Discount Price in Excel

  • As we are not using cell reference so won’t be able to use the Fill Handle tool. Follow the same manual procedure to calculate the other discount prices.

And then you will get all the output like the image below. The problem with this method is- if you change any value, you will have to update the formula manually.

Read More: How to Calculate Bond Price in Excel (4 Simple Ways)


2. Calculate Discount Price Using Manual Formula

Now, we’ll use the mathematical formula with cell references. So, whenever we’ll change any value, the formula will automatically update it.

Steps:

  • Type the following formula in Cell E5–
=C5*(1-D5)

Also, you can write the formula in this way-

=C5-C5*D5

It will result in the same.

  • Later, press the Enter button to finish.

Using Manual Formula in Excel to Calculate Discount Price

  • Next, drag down the Fill Handle icon to copy the same formula for the other cells.

Using Manual Formula in Excel to Calculate Discount Price

Now, you see, we have gotten all the new prices.


Similar Readings


3. Using Array Formula to Calculate Discount Price

In the two previous methods, the discount rates were different for every item. But if we have the same discount rate for all products then we can use an array formula to calculate the discount prices easily. And the advantage is- it can be used as a discount calculator. Here, I placed the discount rate in Cell E11.

Using Array Formula in Excel to Calculate Discount Price

Steps:

  • Insert the following formula in Cell D5–
=C5:C9*E11
  • Then hit the Enter button.

Using Array Formula in Excel to Calculate Discount Price

No need to use the Fill Handle tool because we used the array formula, so all cells will be filled up automatically.

Using Array Formula in Excel to Calculate Discount Price

At this moment, we’ll just subtract the discount from the old price.

  • Type the following formula in Cell E5–
=C5-D5
  • Press the Enter button for the output.

Using Array Formula in Excel to Calculate Discount Price

  • Finally, just use the Fill Handle tool to copy the formula for the rest of the cells.

Using Array Formula in Excel to Calculate Discount Price

Soon after you will get all the new prices after a 25% discount.

  • Now we can use it as a calculator, just insert the new discount rate in Cell E11 and you will get all the discount prices in a while. Look at the output, I inserted 50% instead of 25%.

💬 Note: I showed the method using Excel 365. As we used an array formula so you may need to press Ctrl + Shift + Enter instead of pressing Enter to get the output in the earlier versions of Excel.

Read More: How to Calculate Price Increase Percentage in Excel (3 Easy Ways)


4. Applying Excel Paste Special to Calculate Discount Price

The previous operation can be done by using the Paste Special tool too. But the difference is- it won’t work as a calculator because the Paste Special doesn’t use cell references.

Steps:

  • First, copy all the old prices in the Discount column.

Applying Excel Paste Special to Calculate Discount Price

  • After that, copy the discount rate from Cell E11.

Applying Excel Paste Special to Calculate Discount Price

  • Next, select all the previously copied cells and right-click your mouse.
  • Then select Paste Special from the Context menu.

Applying Excel Paste Special to Calculate Discount Price

  • After appearing the Paste Special dialog box, mark Multiply from the Operation section.
  • Then press OK.

Applying Excel Paste Special to Calculate Discount Price

  • See, it copied the percentage format too. To change it to the Currency format, click on the drop-down icon from the Number section of the Home ribbon and select Currency from the list.

Applying Excel Paste Special to Calculate Discount Price

  • Then insert the formula in Cell E5–
=C5-D5
  • Hit the Enter button.

Applying Excel Paste Special to Calculate Discount Price

  • Lastly, use the Fill Handle tool to copy the formula for the other cells.

Now have a look, you got the same output as the previous method.


Discount Price Calculator

In the practice workbook, I have added a basic discount price calculator where you will give input any old price and any discount rate and the calculator will give you the corresponding discount price.


Conclusion

I hope the procedures described above will be good enough to calculate the discount price in Excel. Feel free to ask any question in the comment section and please give me feedback. Visit ExcelDemy to explore more.


Related Articles

Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Hello! I am Md. Sourov Hossain Mithun. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is an amazing software to learn or work. Here, I will post Excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo