Calculation Issues

Replace Multiple Excel Cell Values Using the Same Formula

Steven ParkerSteven Parker Nov 13, 2026 869 views

Question details

Apply one calculation to multiple existing Excel values and replace the original values with the results.

Replace Multiple Excel Cell Values Using the Same Formula
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.
Before you start

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.

Solution 1Recommended

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.

1
Enter the first formula

If the original prices start in I12, select an empty cell such as J12 and enter =(I12*0.02)/7.08.

2
Fill the formula

Drag the fill handle from J12 down through J20 to calculate results for I12:I20. Adjust the range to match your data.

3
Copy the results

Select J12:J20 and press Ctrl+C, or use the Copy command.

4
Paste values over the originals

Select I12, open Home > Paste > Paste Special, and choose Values. This pastes the calculated numbers rather than the formulas.

5
Check and clear the helper range

Verify the numbers in I12:I20, then select J12:J20 and press Delete to remove the temporary formulas.

Calculate in Temporary Cells and Paste the Results as Values
The original values are replaced: Pasting values overwrites the source numbers. Keep a separate copy if you need to retain them.
Calculate and replace values

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. 1. Open the spreadsheet: Open the Excel workbook in WPS Spreadsheets and identify an empty helper range next to the source values.
  2. 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. 3. Copy the calculated range: Select the helper cells containing the results and choose Copy.
  4. 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.
Apply one formula to a range of prices.Replace source values without retaining formulas.Open and save Microsoft Excel XLSX files.Work with a familiar spreadsheet interface.
microsoft office alternative - wps office

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.