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

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.

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.

How to Calculate Discount Price in Excel: 4 Quick Ways

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-

• Finally, just hit the Enter button to get the result.

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

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–

Also, you can write the formula in this way-

It will result in the same.

• Later, press the Enter button to finish.

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

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

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.

Steps:

• Insert the following formula in Cell D5–

• Then hit the Enter button.

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

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

• Type the following formula in Cell E5–

• Press the Enter button for the output.

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

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.

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.

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

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

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

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

• Then insert the formula in Cell E5–

• Hit the Enter button.

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

Download Practice Workbook

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

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.

<< Go Back to Discount | Formula List | Learn Excel