The Paste Special feature allows you to quickly do some basic mathematical operations on your data. When you’ve copied a cell, you can add, subtract, multiply or divide existing data by the value in the copied cell by using the Paste Special menu. This can be useful for scaling, offsetting, or changing the sign of data in Excel. Knowing this simple trick can save you A LOT of time.
For example, let’s say you have a worksheet and you want to scale all of the force data in that worksheet by a factor of 10. You could type 10 into any cell and copy it (Ctrl-C). Select the cells that you want to scale, right-click on them, and select Paste Special. Select Multiply and click OK. The force data will be multiplied by 10.
You can use the same technique to add an offset to the inputs. If you want to offset the data by 5, type 5 in any cell, and copy it. Select the data to be offset, right-click and select Paste Special. You could select Add or Subtract, depending on whether you need to adjust increase or decrease the values.
[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]
Finally, you can also use this for easily changing the sign of data in a table. To change the data to negative numbers, type “-1” in an empty cell and copy it using Ctrl-C. Then, select all of the cells containing the data you want to scale, right-click on them, then choose Paste Special. Again, select Multiply and click OK. All the values will change from positive to negative.
The great thing about Paste Special is that it allows you to easily adjust your data without having to create an entirely new column.