Learn how to calculate discount amounts in Excel with easy formulas and practical examples. Discover how to find discount percentages, calculate final selling prices, and apply discounts to multiple products using Excel.
How to Calculate Discount Amount in Excel: Formula and Practical Examples
Calculating discounts is a common task in business, shopping, sales, and everyday financial calculations. Whether you are preparing an invoice, calculating a product's selling price, or checking how much a customer saves during a sale, Microsoft Excel can make the process quick and accurate.
Instead of calculating every discount manually, you can use a simple Excel formula to find the discount amount based on the original price and discount percentage.
For example, suppose a product costs $1,000 and a store offers a 20% discount. You can use Excel to calculate the exact amount the customer saves and the final price after applying the discount.
In this guide, you will learn how to calculate discount amounts in Excel using simple formulas, practical examples, and an easy-to-follow table. You will also learn how to calculate the final selling price and apply discounts to multiple products.
What Is a Discount Amount in Excel?
A discount amount is the money deducted from the original price of a product or service.
For example, if a laptop costs $800 and the seller offers a 15% discount, the customer receives a price reduction of $120.
Here is the calculation:
Original Price: $800
Discount Percentage: 15%
Discount Amount: $120
Final Price: $680
Excel can calculate these values automatically using a basic multiplication formula.
Formula to Calculate Discount Amount in Excel
The easiest way to calculate a discount amount in Excel is to multiply the original price by the discount percentage.
Basic Excel Formula:
=Original Price*Discount PercentageFor example, if the original price is in cell A2 and the discount percentage is in B2, use:
=A2*B2This formula works when the discount percentage is entered as a percentage, such as 10%, 15%, or 25%.
Example:
Suppose you have the following information:
Description | Value |
|---|---|
Original Price | $1,000 |
Discount | 20% |
Discount Amount | $200 |
If cell A2 contains 1000 and cell B2 contains 20%, enter this formula in C2:
=A2*B2Excel will return $200 as the discount amount.
How to Calculate Discount Amount in Excel Step by Step
Follow these simple steps to calculate a discount in your Excel worksheet.
Step 1: Enter the Original Price
Open Microsoft Excel and enter the original product price in cell A2.
For example:
1000Step 2: Enter the Discount Percentage
Enter the discount percentage in cell B2.
For example:
20%Make sure you enter the percentage correctly. If you type 20 instead of 20%, Excel treats it as the number 20 rather than 20 percent.
Step 3: Apply the Discount Formula
Click cell C2 and enter the following formula:
=A2*B2Press Enter.
Step 4: Check the Discount Amount
Excel will display:
200This means the customer receives a $200 discount on the original $1,000 price.
How to Calculate Discount When Percentage Is Entered as a Whole Number
Sometimes, you may enter the discount as a whole number instead of using the percentage symbol.
For example, you might enter 20 instead of 20%.
In this situation, you need to divide the discount percentage by 100 in your formula.
Excel Formula:
=A2*B2/100Suppose:
A2 = 1,500
B2 = 25
The formula becomes:
=A2*B2/100Result: $375
This formula is useful when working with discount data imported from billing systems or spreadsheets where percentages are stored as whole numbers.
How to Calculate Final Price After Discount in Excel
Knowing the discount amount is useful, but you may also want to calculate how much the customer needs to pay after the discount.
To calculate the final price, subtract the discount amount from the original price.
Formula:
=Original Price-Discount AmountIf the original price is in A2 and the discount percentage is in B2, you can calculate the final price directly using:
=A2-(A2*B2)For example:
Description | Amount |
|---|---|
Original Price | $2,000 |
Discount Percentage | 15% |
Discount Amount | $300 |
Final Selling Price | $1,700 |
You can also use a shorter formula:
=A2*(1-B2)Both formulas return the same result.
How to Calculate Discounts for Multiple Products in Excel
If you run a shop or manage product sales, calculating discounts for individual products can take time. Excel makes this easier because you can apply the same formula to multiple rows.
Consider the following example:
Product | Original Price | Discount | Discount Amount | Final Price |
|---|---|---|---|---|
Laptop | $1,000 | 10% | $100 | $900 |
Smartphone | $800 | 15% | $120 | $680 |
Headphones | $200 | 20% | $40 | $160 |
Keyboard | $100 | 5% | $5 | $95 |
Monitor | $500 | 25% | $125 | $375 |
Assume your Excel columns are arranged as follows:
Column A: Product Name
Column B: Original Price
Column C: Discount Percentage
Column D: Discount Amount
Column E: Final Price
Enter the following formula in D2:
=B2*C2Then enter this formula in E2:
=B2-D2Now select both formula cells and drag the fill handle down to apply the formulas to the remaining products.
Excel will automatically adjust the cell references for each row.
How to Calculate Discount Amount Using the SUM Formula
If you have calculated discounts for several products and want to find the total discount given to customers, you can use the SUM function.
For example, suppose your discount amounts are listed in cells D2 through D10.
Use this formula:
=SUM(D2:D10)Excel will add all the discount amounts and display the total discount value.
This is particularly useful for businesses that want to track promotional expenses, customer savings, or total discounts offered during a sales campaign.
Common Mistakes to Avoid When Calculating Discounts in Excel
Even though discount calculations are simple, a few small mistakes can produce incorrect results.
Here are some common issues to watch out for:
Incorrect percentage format: Entering 20 instead of 20% can produce an incorrect result if your formula expects a percentage value.
Wrong cell references: Make sure your formula refers to the original price and discount percentage cells.
Confusing discount amount with final price: The discount amount is the money deducted, while the final price is what the customer pays.
Missing percentage conversion: If percentages are entered as whole numbers, divide them by 100 in the formula.
Incorrect number formatting: Apply Currency or Accounting formatting to display prices clearly.
Checking these details can help you avoid calculation errors in invoices, product lists, and sales reports.
Frequently Asked Questions (FAQs)
1. What is the formula to calculate discount amount in Excel?
Use =A2*B2 when A2 contains the original price and B2 contains the discount percentage.
2. How do I calculate a 20% discount in Excel?
Multiply the original price by 20%. For example, =1000*20% returns 200.
3. How do I calculate the final price after a discount?
Use =A2*(1-B2) to subtract the discount from the original price automatically.
4. Can Excel calculate discounts for multiple products?
Yes. Enter the discount formula in the first row and drag the fill handle down to apply it to other products.
5. How do I calculate a discount if the percentage is entered as 20 instead of 20%?
Use =A2*B2/100 to convert the whole number into a percentage during the calculation.
Final Thoughts
Calculating discount amounts in Excel is simple once you understand the basic multiplication formula. By using =A2*B2, you can quickly find how much money is deducted from an original price.
You can also calculate final selling prices, manage discounts for multiple products, and find total discounts using Excel functions such as SUM.
Whether you are managing a small business, preparing sales reports, or comparing product prices, these formulas can save time and reduce manual calculation errors. Practice them with a few sample products, and you will be able to handle discount calculations in Excel with confidence.



COMMENTS