Update Multiple Excel Formula in One Step

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.

RibbonPasteSpecial

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.

PasteSpecialAdd

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

PasteSpecialAdd02 

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.

PasteSpecialMult01

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.

PasteSpecialMult02 

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.

PasteSpecialMult03

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.

PasteSpecialMult04

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.

PasteSpecialMult05 

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

Watch the Video

To see the Excel Paste Special Multiply steps, you can watch this short video tutorial.

____________

You may also like...

1 Response

  1. andrea costello says:

    Need to update thousands of formulas in our excel files when upgrading from 2003 to 2013. Is there a method to search and replace that would work to covert .xls formulas to .xlsx all at once? The worksheets are interconnected.

Leave a Reply to andrea costello Cancel reply

Your email address will not be published. Required fields are marked *