Function Problems

How to Apply a Selectable Discount to a Price in Excel

Maira Mehtab Sep 21, 2026 868 views

Question details

The user wants to provide a drop-down list with specific discount options (0% and 5%) and apply the selected discount percentage to a price located in another cell.

Product: Excel
Device & OS: not provided
Scenario: Setting up a dynamic pricing sheet where the final price updates automatically based on a user-selected discount percentage.
Observed behavior: The user needs the specific steps to create the drop-down list using Data Validation and the correct formula to link the selection to the price calculation.

Before you start

Ensure that your original price cell contains numeric values and that you have identified an empty cell to host the discount drop-down list.

Solution 1Recommended

Create a Drop-Down List and Apply the Discount Formula

Use the Data Validation tool to create a selectable list of percentages, then use a multiplication formula to calculate the final discounted price.

This method involves two parts: creating the selectable menu so users can pick a discount rate, and applying a mathematical formula that adjusts the original price based on that selection.

Select the discount cell

Click on the cell where you want the drop-down list to appear (for example, C21).

Open Data Validation

Navigate to the Data tab on the top ribbon and click on Data Validation.

Configure the list settings

In the Settings tab of the dialog box, click the 'Allow' drop-down and choose 'List'. In the 'Source' box, type your options separated by commas, such as: 0%,5% (or 0%, 5%). Click OK.

Enter the calculation formula

Select the cell where you want the final calculated price to show. Type the formula =F24*(1-C21), assuming F24 holds the original price and C21 is your new drop-down cell. Press Enter.

Dynamic Calculation: Whenever you change the selection in the drop-down list, the final price will automatically update to reflect the newly chosen discount.

Solution 2

Apply a Fixed Discount Formula

If you do not need the discount to be selectable, apply a fixed percentage directly within your formula.

Efficient Spreadsheet Management

Easily Manage Pricing and Discounts with WPS Spreadsheet

WPS Spreadsheet provides intuitive tools for Data Validation and formula calculations, making it simple to build dynamic pricing templates and financial sheets without the steep learning curve.

1. Open your workbook: Launch WPS Spreadsheet and open your pricing document.
2. Access Data Validation: Select your target cell, navigate to the Data tab, and click Validation.
3. Set up the drop-down list: Choose 'List' under criteria, input your discount percentages separated by commas, and click OK.
4. Apply the calculation: Type your formula (e.g., =F24*(1-C21)) referencing the validated cell to automate the final price calculation.

Seamlessly compatible with Microsoft Excel (.xlsx) formats and formulas.Easily create drop-down lists with the intuitive Data Validation tool.Built-in robust formula engine for complex financial calculations.Lightweight, fast-loading, and completely free to use.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my formula returning a #VALUE! error when selecting a discount?

This happens if the source values in your Data Validation list are interpreted as text rather than numbers. Ensure you enter the values exactly as percentages (e.g., 0%, 5%) or as decimals (e.g., 0, 0.05) in the validation source box.

Can I reference a range of cells for my drop-down list instead of typing them manually?

Yes. In the Data Validation Source box, instead of typing the values manually, you can click the arrow icon and drag to select a range of cells (e.g., =A1:A5) that already contains your desired discount percentages.

How do I calculate just the discount amount rather than the final discounted price?

To calculate only the monetary discount amount, simply multiply the original price by the discount cell directly. For example, use the formula =F24*C21 instead of =F24*(1-C21).

Excel & Data Problems