beprofit logo
ProductExpand more icon
profit calc image
Profit Simulators
Tweak your numbers to lift profits up
action card image

Demo Store

Want to see our
dashboard in action?
ResourcesExpand more icon
CompanyExpand more icon
Pricing
Want to see our dashboard in action?
you can try it here with theDemo Store!

4 Easy Excel Discount Formulas For Your E-Commerce Store

By Rosana Antoni
Kelly Hendrickse - Editor for BeProfit
Edited by Kelly Hendrickse

Updated May 5, 2023.

Using Excel can help you find discount price and rate easily.

Discounts can sometimes make or break a sale. 83% of shoppers say that discount coupons inform their purchasing behavior directly. However, discounts need to be implemented strategically. With monitoring, businesses can calculate optimal discounts, as well as forecast ROI and profits. And Excel can be a powerful and flexible tool to help with this. Excel can streamline and optimize e-commerce operations, allowing businesses to make better-informed decisions and achieve greater success.

1. Discount Price Using Manual Formula

Calculating discount prices using manual formulas in Excel is a simple and efficient way to manage your pricing strategy to help ensure profits.

Let's say you want to offer a 20% discount on a product that originally costs $100.

Here's how you can calculate the discounted price using a manual formula:

1. Enter the original product price ($100) in cell A2.

2. Enter the discount rate as a decimal (0.2) in cell B2.

3. In cell C2, enter the following formula:

Discounted Price = Product Price - (Product Price x Discount Price)

C2=A2-(A2*B2)

4. Excel will calculate the discounted price, which is $80 in this case.

Simple Excel formula can help you find out a discounted price.


You can easily adjust this formula for different products and discount rates.

Monitor Your Sales Performance

With your discounts in place, you can track profit and revenue metrics to find whether your pricing leads to conversions.

  • View data from mobile or desktop devices.
  • Connect multiple shops for an integrated view of your business's revenue metrics.
  • Create and export customized data reports.

With BeProfit, you can monitor your conversion rates and profits, which can help you spot when product discounts are working for your business.

BeProfit: Profit Analysis Dashboard

4.8/5(320 reviews)




2. Manual Formula to Calculate Discount Rate

To calculate a discount rate in Excel, you would typically need the optimal price you're hoping to sell the product for first. For example:

1. Enter the original product price ($100) in cell A2.

2. Put in the target discounted price ($50) in cell B2.

3. In cell C2, enter the following formula:

Discount Rate = (Product Price - Target Discounted Price) / Product Price

C2=(A2-B2)/A2

4. Excel will calculate the discount percentage required to achieve the target discounted price. In this example, this would be 50%.

Using your targeted discounted price can help calculate the discount rate.


Make sure you format cell C2 as a percentage. You can change this from the "Home" tab in the "Number" section, and select "Percentage" from the drop-down box.

If you'd like to decrease the decimal points so your percentage is rounded to a whole number, you can click on "Decrease Decimal" from the same section.

» Find out how to calculate revenue in Excel using simple formula templates



3. Using Array Formula to Calculate Discount Price

Array formulas in Excel can help you quickly calculate discounted prices for large sets of data.

For example, you have a dataset with 10 products and you want to calculate the discounted price for each product based on its original price and discount rate.

Here's how you can use an array formula:

1. Enter the original product prices in column A.

2. Enter the discount rates as decimals in column B.

4. In column C, enter the following formula:

Discounted Price = Product Price - (Product Price x Discount Rate)

C2=A2-(A2*B2)

5. To apply the formula to the other rows, press Ctrl+Shift+Enter. Excel will automatically calculate the discounted price for each product based on its original price and discount rate.

Excel can calculate discount prices for various product prices with one formula.


» Discover the 3 best Excel formulas for calculating markup vs margin



4. Find the Discount Rate With the What-If Analysis

Excel's What-If Analysis function is a bit more involved. But it can help you determine the required discount rate to achieve your desired discounted price.

For instance, let's say you have a product with an original price of $100 and a current discount price of $50, and you want to offer it at a final discounted price of $75.

1. Begin by entering the original price of the product ($100) in cell A2, the current discount rate (50%) in B2, and use the manual formula from the first method to get the current discounted price in cell C2.

Remember to use a formula in the Discounted Price column.

This method can help you test out different discounts.


2. Next, navigate to the Data tab on the Excel ribbon and click on "What-If Analysis."

Tip: Make sure the cell selected before you click on "What-If Analysis" is C2 (the "Discounted Price" cell)

3. From the drop-down menu, choose "Goal Seek."

You can find this function on the Data tab.


5. In the "Goal Seek" dialog box, you will see that "Set cell" is auto-filled with C2. Now:

  • Fill in "75" in the "To value" field since this is your desired discounted price.
  • Click into the "By changing cell" field, then click on cell B2 to select that value.
Your desired price will go into the "To value" field.


6. After clicking OK, Excel will automatically compute the required discount rate to achieve the target discounted price of $75. In this case, a 25% discount rate should be used.

To achieve a desired price, this function shows you what your rate should be.


Employing the What-If Analysis function can be a great tool to test different pricing strategies and discounts before implementing them, so you can help boost sales and ensure your final amounts will still convert into revenue and profits.

» Need help? See how to increase e-commerce store revenue with customer discounts

Deduce Your E-Commerce Store's Discounts With Ease

By using Excel to deduce your e-commerce store's discounts, you can take the guesswork out of pricing. This can help you increase profit margins, boost customer satisfaction with competitive pricing, and potentially improve AOV.

But monitoring your pricing effectiveness is also vital. You can use platforms like BeProfit for seamless profit and revenue tracking, so you can have a clear overview of the impact and performance of your discounts and pricing.

BeProfit: Profit Analysis Dashboard

4.8/5(320 reviews)