How to Increase Multiple Excel Prices Using a Percentage Input
Question details
The user wants to dynamically increase a range of price cells by a specific percentage entered into a single input cell.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Updating an inventory or price list based on a variable markup or inflation percentage.
- Observed behavior
- The user needs a method where changing the percentage in one cell instantly recalculates all selected price cells, or a method to bulk-update prices permanently.
Decide whether you need the original prices to remain visible (use formulas in a new column or table) or if you want to permanently overwrite the existing prices directly (use Paste Special).
Use Formulas with Absolute References for Dynamic Updates
Create a secondary column or table that calculates the new price, updating automatically whenever the input percentage changes.
This method is ideal if you want to maintain a record of your original prices while seeing the updated values in a new column. It allows the prices to adjust dynamically anytime you change the percentage in the input box.
Click on the cell in a new column or table where you want the updated price to appear.
Type the formula to calculate the increase: =OriginalPriceCell + (OriginalPriceCell * $InputCell). For example, if your original price is in E8 and your percentage is in R3, enter: =E8+(E8*$R$3).
Press Enter to calculate the new price.
Click and drag the fill handle (the small square at the bottom right corner of the cell) down the column to apply the formula to the rest of your price list.
Use Paste Special to Bulk Multiply and Overwrite Existing Prices
If you do not need to keep the old prices and want a one-time permanent increase, use the Paste Special feature to overwrite the data.
Effortlessly Update Bulk Prices Using WPS Spreadsheet
WPS Spreadsheet offers fully compatible formula functions and bulk editing tools like Paste Special to help you manage pricing structures quickly and efficiently.
- 1. Open your price list: Launch WPS Spreadsheet and open the document containing your prices.
- 2. Set up an input cell: Designate an input cell for your percentage markup (e.g., cell R3).
- 3. Apply the calculation: Use the formula =E8+(E8*$R$3) in an adjacent column to calculate the updated price.
- 4. Drag to fill: Drag the fill handle downward to apply the price increase across your entire inventory list.
Frequently Asked Questions
How do I decrease prices by a percentage instead?
To decrease prices dynamically, change your formula to subtract the percentage amount: =E8-(E8*$R$3). If using Paste Special, enter a decimal representing the remaining value (e.g., 0.90 for a 10% discount) and use the 'Multiply' operation.
Why is my formula returning an error or incorrect value when I copy it down the column?
You likely forgot to use absolute cell references for your percentage input cell. Make sure the input cell reference in your formula includes dollar signs (like $R$3). This locks the reference so it doesn't shift down when you drag the formula to other rows.
Can I use a macro to update specific highlighted cells automatically?
Yes, you can write a VBA macro that loops through the currently selected cells and multiplies their values by the input cell. This is particularly useful for complex scenarios where only random or specific cells need updating permanently without relying on helper columns.
