Use Excel’s Paste Special to perform calculations when pasting

Last week I was working with an Excel spreadsheet that had several columns of numbers.  As it happened, I needed to increase all the numbers (and there were lots of them) by 1.  So  in the screen shot to the right, 24-Aug should be 4056 and 25-Aug should be 4068, etc.  Changing the number in each cell manually would have been tedious and time-consuming.

So what to do?   Use Excel’s Paste Special feature.  While Paste Special is more commonly used for Paste Values or Paste Link you can also use it to add the same number to a group of cells.

To add 1 to a group of cells:

  1. Enter 1 in a blank cell
  2. Copy the cell containing 1
  3. Select the cells containing numbers that you want to increase by 1
  4. Right-click on the selected cells
  5. Select Paste Special
  6. In the Paste Special dialog box under Operation, select Add
  7. Click on OK

 

As you will see from the Paste Special dialog box above, you can also use this feature to multiply, subtract and divide a group of cells by a number.  For instance, if you had prices in a series of cells you could use this feature to increase or decrease them all by the same percentage.

Any questions, please let me know.