Introduction
Do you find yourself creating unnecessary helper columns to multiply an entire column by one number, copying the helper column over, then delete the helper column? It's a painful string of steps, and how I used to work until I learned how to add, subtract, multiply, and divide any size range all with paste.
Overview
While we all know the standard copy and paste, within paste there exists "paste special". Normally, this is used to paste formatting, or paste values, but you are also able to multiply large ranges. With the "operation" paste, you can simply select a cell value of whatever you want to add/subtract/multiply/divide with, and paste over the data that you want to change.
Example - Multiplying a Range by a Multiplier
In this example, I'll show you how to multiply a range by a percentage. In this case, we are multiplying some employee's salaries by 105% in order to calculate a 5% salary bump.
Step 1
The first step is to copy a value that you wish to perform the operation with, in this case we're copying 105% which we will multiply against salary range in column C.
Step 2
Next, you want to highlight the range that you wish to "paste" your copied value onto.
Step 3
Finally, open up the "Paste Special" menu. This can be done by going to the Home Tab in the ribbon > Clipboard > the paste arrow > paste special, or by using the shortcut: alt + E + S . You'll see this menu pop up:
From here, simply click the "Multiply" check under "Operation" and choose "Values" under Paste, as we only want to use the 105% value, and not bring over the % sign.
And there you have it! An entire range changed with a single paste.
Final Thoughts
You can repeat these few steps above across a huge variety of data sets and applications. I can't tell you how many times I would create a column alongside the data I was looking to transform to perform simple calculations like these. With this one paste special technique I'm able to save loads of time.
Using these mathematical operations in conjunction with the standard paste special options is extremely powerful and will save you lots of keystrokes and headaches.