Excel: Paste Special Operations: Quick Arithmetic with a Range Cells

Every once in a while, you find yourself in a situation where you need to perform a basic arithmetic function on a series of cells… maybe you need to take a group of cells and increase or decrease their number by 1, or by a percentage.

You could create a helper column, write a formula into it, auto fill down, then copy and paste the values back into their original spots, but that is a lot of steps for simple addition! You might be tempted to change the values by hand; adding one to each cell is not so hard, right? Well, don’t! There is an even faster way.

I have mentioned before that there is more than meets the eye in Paste Special options. Here is another great example.

Exercise File

To follow along, download today’s exercise here: ExercisePasteSpecialAddition


This workbook contains a sheet called Attendance. In this scenario, my supervisor has asked that I add myself and him/her to the staff headcount at the January meetings. So, I would like to quickly add 2 to all the numbers in column B.

Paste Special: Operations

Since we will be using copy and paste for this, we need to have the number we want to add somewhere on the sheet so it can be copied. You can always delete it later: I populated this number in cell F1 for us.

  1. Select F1
  2. Either press Ctrl C on your keyboard or right click, copyF1 Selected, Copy
  3. Select all the cells that need this figure added: so B2 to B28
  4. In the Clipboard group of the Home tab, select the dropdown under Paste
  5. Select Paste SpecialPaste dropdown, Paste Special selected
  6. In the popup screen, under Operation, select Add.Operations section, Add selected
    • By doing this, we are telling Excel that, instead of copying the number 2 to all these cells, we would like to add 2 to the existing contents.
  7. All the numbers in the selected cells have now increased by 2.New list, with numbers increased by 2Once you have finished this task, you could theoretically delete the contents of E1 and F1. We did not create formulas in column B, we just added to the existing number, so we are no longer relying on F1.

    More Arithmetic Options

    Did you spy the other options in the Operations section of Paste Special? You could also:

    • Multiply
    • Divide
    • Subtract

Operations section of Paste Special

Congratulations, Power Users!

Congratulations to our newest Power Users! For the full gallery, and more information about the WSU Microsoft Office Power User Program, please visit: wichita.edu/poweruser

  • Lana Anthis
    Lansa Anthis
  • Cindy Sharp
    Cindy Sharp
  • Susie Steinbach

Susie Steinbach