Replace Multiple Excel Cell Values Using the Same Formula
Question details
Apply one calculation to multiple existing Excel values and replace the original values with the results.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user has a range of prices and wants to apply the same calculation to each price without leaving the results in a separate range.
- Observed behavior
- Copying calculated cells back to the original range does not produce the intended values when the formulas are pasted.
Identify the source range and an empty range of the same size for the formulas. For example, if the prices are in I12:I20, make sure J12:J20 is empty.
Calculate in Temporary Cells and Paste the Results as Values
Use a helper range to calculate each result, then replace the original numbers without copying the formulas.
A cell cannot hold both its original value and a formula referring to that same value. Using a separate range avoids a circular reference.
If the original prices start in I12, select an empty cell such as J12 and enter =(I12*0.02)/7.08.
Drag the fill handle from J12 down through J20 to calculate results for I12:I20. Adjust the range to match your data.
Select J12:J20 and press Ctrl+C, or use the Copy command.
Select I12, open Home > Paste > Paste Special, and choose Values. This pastes the calculated numbers rather than the formulas.
Verify the numbers in I12:I20, then select J12:J20 and press Delete to remove the temporary formulas.

Replace Calculated Values in WPS Spreadsheets
Use a temporary formula range in WPS Spreadsheets, then paste the results as values over the original cells.
- 1. Open the spreadsheet: Open the Excel workbook in WPS Spreadsheets and identify an empty helper range next to the source values.
- 2. Enter and fill the formula: For values starting in I12, enter =(I12*0.02)/7.08 in J12, then drag the fill handle down to cover the source range.
- 3. Copy the calculated range: Select the helper cells containing the results and choose Copy.
- 4. Paste values over the source: Select the first original cell, then use Home > Paste > Paste Special and choose Values. Check the results before deleting the helper formulas.

Frequently Asked Questions
Why can't I enter a formula that refers to the original value in the same cell?
A cell can contain a value or a formula, not both. Entering a formula in I12 that refers to I12 creates a circular reference instead of preserving the old value.
Why should I paste values instead of formulas?
Paste Special > Values inserts the calculated numbers without the formulas. An ordinary paste can change cell references or create circular references when pasted over the source.
How do I apply the formula to every price in a column?
Enter the formula in an empty cell beside the first price, then drag its fill handle down alongside the remaining prices. Excel adjusts the relative cell reference for each row.
Can I delete the temporary formulas after pasting?
Yes. After using Paste Special > Values and checking the original range, select the helper cells and press Delete. The pasted numbers remain in the original range.




