Insert and Modify Formulas

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.