Update Multiple Excel Formula in One Step

Update Multiple Excel Formula in One Step

Posted: 10 Jun 2010 09:03 PM PDT

You've probably used the Excel Paste Special command to multiply cells by a specific percentage, or to add the same amount to a group of cells.

For example, in the screenshot below, you could copy cell D2, and use the Paste Special, Add, command to add that amount to cells B2:B5.

After using the Paste Special command, each value in B2:B5 is increased by $1.50, as expected.

Paste Special For Formula Cells

Excel Paste Special works well with values, and you can also use it to paste and modify formulas. In the worksheet shown below, the formulas in row 6 calculate an adjusted rate. A new factor -- Discount -- has been added to the workbook, and it needs to be included in the adjusted rates.

Each formula in row 6 is slightly different, so you can't just change one formula and copy it across. Fortunately, you can use the Paste Special command to modify all the formulas at once.

However, pasting formulas is a little trickier than pasting values. For example, if you copy cell B2, and use it to multiply in the Paste Special command, the results might not be what you need.

Instead of pasting a reference to cell B2, the Paste Special command pastes the formula that's in cell B2 (green highlighting below). Because the pasted formula has a relative reference, the results in row 6 aren't correct.

To solve the problem, you can create a temporary formula, in cell D2, with an absolute reference to cell B2, and use that for the Paste Special.

Then, copy cell D2, and use Paste Special, Multiply, on cells B6:E6. Now the modified formulas are multiplied by cell B2 (green highlighting), and the results in row 6 are correct.

After using the Paste Special command, you can clear the contents of cell D2, where you entered the temporary formula.