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.
Scaling Data in Excel
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.
Elevate Your Engineering With Excel
Advance in Excel with engineering-focused training that equips you with the skills to streamline projects and accelerate your career.
The force data will be multiplied by 10.
Add an Offset to Excel Data
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.
The data is offset by +5:
Change Sign of Data in Excel
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.
