Use Paste Special Add in Excel

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?

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.

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.

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

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