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
Scenario
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.
- Select F1
- Either press Ctrl C on your keyboard or right click, copy
- Select all the cells that need this figure added: so B2 to B28
- In the Clipboard group of the Home tab, select the dropdown under Paste
- Select Paste Special
- In the popup screen, under Operation, select Add.
- 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.
- All the numbers in the selected cells have now increased by 2.
Once 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
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
- Cindy Sharp
- Susie Steinbach
