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. Today, you’ll see how to update multiple Excel formula cells in one step.
This screen shot shows a cell that has been copied, and is ready for the Paste Special command.
Copy Number From Worksheet Cell
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.
Watch the Video
To see the Excel Paste Special Multiply steps, watch this short video tutorial.
____________
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.