Excel’s Paste Special feature can be used to modify multiple numbers at once. Paste Special is more commonly used to paste only values or formatting, it has other tricks to offer. We’ll show you firstly how to multiply numbers in Excel by using Paste Special then add, subtract and division.
Addition and Multiplication are easy with Paste Special. Subtract and Divide need a little more care because the order of the values matters a lot.
Paste Special with Multiply
Multiplying two cells is probably the most common use of Paste Special math operations. Let’s say you’ve got a large list of gross sales for 2021, with approximate estimates for 2022.
Normally, users would expect to just calculate the values within Column B and multiply it by 109% (which would provide the answer of column C including the additional 9% estimated increase for 2022). However, Paste Special makes it a lot easier to do this.
Multiply Example
Firstly, we highlight the values within Column B.
Next, we want to copy these values (Ctrl + C). Once the values within column B have been copied, we can highlight column C where we would like the results to calculate.
From there, we go to Paste Special, this can be accessed via a quick shortcut Ctrl + Alt + V or alternatively at the ribbon Home | Clipboard | Paste | Paste Special.
This will bring up the Paste Special dialog box where we can select All under the Paste Header and Multiply under the Operation Header, then OK.
This will multiply the values of the copied cells within column B, with the values of the cells within column C.
Different multiply factors
Our examples have had the same value to multiply or subtract, but what if they’re different?
Well, luckily Paste Special can still work even if multiplying, adding etc. by different values. For example, the different percentages as shown in column E.
Once we apply Paste Special, the values are calculated within column E instantly using the different percentages in the destination cell.
Subtract Example
So, lets focus on Column D, where revenue is expected to reduce by $2,000 for each month of 2022.
We follow the same process, firstly copying column B followed by Paste Special within Column D but choose Subtract from the Operation options.
Once you press OK, this will subtract the results instantly for the entire column.
Subtraction is a wee bit more complicated because the order of the values (cells) matter.
Division
Like subtraction, Division with Paste Special needs the values in the correct order.
