There are a LOT of different ways to manipulate numbers in Excel. After all, manipulating numbers is Excel’s specialty. There are times when you have a data set that you need to manipulate against another data set but you don’t want to create a third data set that contains the manipulation results.
In the below example, we have sales figures for five people. We are only interested in retaining a running total of their sales. We have just received the “latest sales” and wish to add those numbers to the “current sales”.
We could create a formula to add Bill’s “current sales” to his “latest sales” (i.e. =B2+D2) and store the answer in a new cell; then fill that formula down to calculate the same for the remaining sales reps. The issue here is that we don’t want to create a third list of number, which we would then copy and replace atop the older, out-of-date “current sales”. Here is how we can integrate the “latest sales” into the “current sales” without writing a single formula.
Highlight the “latest sales” cells (D2:D6) and select Home –> Copy.
Select the first cell holding the “current sales” (B2) and select the lower part of the Paste button and select Paste Special…
This will present the Paste Special dialog box. In the Operation section, select Add and click OK.
You will now see all of the data that was copied in Step 1 added to the data selected in Step 2.
As you can probably guess, you could have subtracted, multiplied, or divided the “current sales” by the “latest sales” by selecting Subtract, Multiply, or Divide in the Paste Special dialog box.
In this scenario, we want to increase a list of item’s sale prices by 15%.
Highlight the “% Increase” cell (D4) and select Home –> Copy.
Select the cells holding the “Prices” (B2:B6) and select the lower part of the Paste button and select Paste Special…
In the Paste Special dialog box, in the Operation section, select Multiply and click OK.
The “Prices” have been updated with the 15% increase amount.
This is a great way of updating a block of numbers by some constant value without having to create a new set of answers that have to then replace the original values.