TUESDAY TIPS: Using Paste Special to scale values or change sign

Have you ever needed to scale a dataset from thousands to millions, or vice versa? Or perhaps received a table of expense items with positive values which you needed to convert to negative values before incorporating into your file?

One of the most widely used functions in Excel, the Copy / Paste (Ctrl + C / Ctrl + V) combination allows the user to copy data from one range to another, replicating all contents of the cell including formatting, formulas and values. The “Paste Special” alternative enables the user to select which properties of the copied range to paste in the destination cell(s). While Paste Special is frequently used to paste only values or formatting, the focus of our post today is a lesser known feature of this menu which allows the user to multiply or divide values. This can be a very efficient way of scaling values or changing the sign of a series of values simultaneously.

How to access Paste Special

The Paste Special menu can be found in the ribbon under Home -> Clipboard -> Paste. Alternatively, you can use the shortcut Alt + H + V + S. The menu contains several options, with the “Multiply” and “Divide” functions in the Operation section being the focus of this post.

How to use the Paste Special Operation to scale values

Step 1) Enter the relevant denominator in a temporary input cell in your worksheet. For example, to convert GBP to GBP’000, enter 1,000 in your helper cell.
Step 2) Copy the value entered in the helper cell using the shortcut Ctrl + C.
Step 3) Highlight the range of values which you want to convert.
Step 4) Activate the Paste Special Menu by using the Alt + H + V + S shortcut.
Step 5) Select “Values” from the Paste section and “Divide” from the Operation section as shown below; then hit OK.

How to use Paste Special Operation to change sign

The same approach can be used to change the sign of an array of values. In this case, you can enter -1 as the temporary input in your helper cell. Follow Steps (2) to (4) exactly as in our previous example.
In Step (5), you can either choose ‘Divide’ or ‘Multiply’ from the Operation section in the Paste Special menu. Dividing or multiplying a value by -1 will result in the sign of that value being changed.

Bonus Tip: Using Paste Special to Transpose values

A further option in the Paste Special menu is the ‘Transpose’ function. This allows you to flip data from a column to a row, or vice versa. You can achieve this by copying the data you wish to transpose, selecting the (first) destination cell, activating the Paste Special menu (Alt + H + V + S) and selecting ‘Transpose’.

More to come…

We will be publishing a new tip every Tuesday – follow our page on LinkedIn to be notified of our posts.