MrExcel.com - Your One Stop for Excel Tips & Solutions

Paste Special Add Operation

July 16, 2002 - by

I was talking to Steve on the telephone the other day when I came up with the idea for this tip. Steve had a table of monthly sales figures in Excel. Because two product lines had been merged, Steve needed to add all of the sales figures from product D into the sales figures for product C. As I listened over the phone, Steve did what we all do: he inserted a blank Excel row, entered an Excel formula to sum the cells, then used paste-special-values to paste the result over top of the original figures for product C, then deleted the old product D and the temporary blank row. There is a faster way - using Paste Special Add instead of Paste Special Values.

If you have been working with spreadsheets since the days of Lotus 1-2-3, then you know how to /Range Value cells with formulas in order to change the entries from formulas to values. As you transitioned to Excel, the Excel help taught us to use Edit > Paste Special > Values to accomplish the same thing. After grumbling about having to learn a new method, we all quickly started using "Paste Special Values" (if you are a real keyboard nut, you memorized the alt-esv shortcut) and went on with our lives.

For anyone who does not know how to use Paste Special Values: review Join Text in Excel.

Here is this week's tip: Have you ever looked at the Paste Special dialog box and wondered what all of those other options really do?

Paste Special Dialog
Paste Special Dialog

Let's look at the "Operation" section of the PasteSpecial dialog box. With the "Add" option, you can highlight a rectangular range of cells, use Edit > Copy to copy them to the clipboard, then use Edit > Paste Special > Add to add those values to another range of cells. This will make Steve's task much simpler.

Copy Monthly Sales Data
Copy Monthly Sales Data

Let's take a look at Steve's example. He wants to add each month's sales figures from Item D to the corresponding month for Item C. First, Steve would highlight cells B4:M4 and hit Edit > Copy.

Select Paste Special Operation
Select Paste Special Operation

Next he would highlight cell B3, choose Edit > Paste Special and select Add from the Operation section of the Paste Special dialog.

Operation Add Result
Operation Add Result

Click on OK, and Excel will add the cells from the clipboard to the existing values for Item C.

Steve can now delete item D and continue on his way. This method is quicker and less prone to error than inserting a row, entering the formula, changing the formulas to values and pasting over the original figures. It is not a lot quicker, but every bit helps.

Paste special can also be used to apply a single value to a range of cells. For example, if you have 1000 cells that are all negatives and you need to change them to be positive:

  • Find a temporary cell and enter -1 as the value in that cell
  • Copy the temporary cell
  • Highlight your 1000 cell range
  • Use Edit > Paste Special > Multiply to multiply all of those 1000 cells by negative 1.

So, the next time you find yourself about to insert a temporary row in order to perform a calculation, try out these other options found in the Paste Special dialog to simplify your life.