Paste Special Add Operation
July 16, 2002 - by Bill Jelen
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](/img/excel-tips/mec000526a.gif)
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](/img/excel-tips/mec000526b.gif)
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](/img/excel-tips/mec000526c.gif)
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](/img/excel-tips/mec000526d.gif)
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.