Quickly Convert Excel Data in Place with Paste Special

Do you ever need to convert units, increase or decrease a range of data, or change its sign?

Then this technique should work for you.

Excel’s Copy > Paste Special > Add / Subtract / Multiply / Divide lets you transform data in place. You don’t need an intermediate column or row.

Say, for example, that you have some measurements in metres, and you would prefer to have them in feet. (Why anyone in the 21^st century would work in archaic units is open to question, but please humour me for this example!).

The Old Way: New Column, Converted Values, Copy > Paste Values

Say that we have a series of chainages in column A: Chainage in m, 20, 40, 60, 80, 100 as shown below, and we want to convert them to feet.

1. If there is data in columns to the right, we insert a temporary column, B.
2.  In column B, cell B2, we enter the formula for the conversion: =CONVERT(A2,”m”,”ft”) (see figure 1 below).
3. Since four decimals are meaningless, adjust the decimals to 2.
4. Point at the bottom right-hand corner of the selected cell, B2, to get the “black plus” mouse pointer.  Double-click to fill down.
5. The values in feet are now selected.  If you want to replace the original values, copy, select cell A2, and Paste Values.
6. You can then delete column B.

The Better Way: One Cell, Copy > Paste Special > Multiply

Again, we have a series of chainages in column A: “Chainage” heading, 20, 40, 60, 80, 100, and we want to convert them to feet.

1. Find a suitable, temporary cell, such as A11 in this example.
2. In that cell, enter the formula to convert one unit from m to ft:

3. Click OK.  Cell A11 contains the formula, which yields a value of 3.28 when formatted for 2 decimal places.
4. Copy, using the “Copy” icon or Ctrl+C
5. Select the chainage values you want to convert (cells A2:A6 in our example)
6. Choose Paste _v (dropdown menu) > Paste Special (or Ctrl+Alt+V):

7. The Paste Special dialog box opens.
8. Under “Operation” (normally “None”)), select Multiply.

9. Click OK.  The values in selected cells A2:A6 are now in feet, with the decimal places you selected for A11.

10. You can then delete temporary cells A10:A11

Increase or Decrease a Range of Data by an Amount

Here the process is very similar to the above.

To increase a block of data by (say) 5 units.

1. In a suitable temporary cell, enter the desired value (5, in this case).
2. Copy, using the “Copy” icon or Ctrl+C
3. Select the range you want to add to.
4. Choose Paste _v (dropdown menu) > Paste Special (or Ctrl+Alt+V):
5. In the Paste Special dialog, under “Operation”, select Add.

6. Click OK.  The values in selected cells change.
7. Delete the contents of the temporary cell.

To decrease the data, either use a negative amount in step 1, or “Subtract” in step 5.

Increase or Decrease a Range of Data by a Percentage

Again, the process is very like the above.

To increase a block of data by (say) 10%, multiply by 1.1 (that is, the new value must be 100% + 10% of the old value = 110%).

To decrease a block of data by (say) 5%, multiply by 0.95 (that is, the new value must be 100% – 5% = 95%).

1. In a suitable, temporary cell, enter the desired percentage.
2. Copy, using the “Copy” icon or Ctrl+C
3. Select the range of values you want to change.
4. Choose Paste _v (dropdown menu) > Paste Special (or Ctrl+Alt+V):
5. In the Paste Special dialog, under “Paste” (top section), select Values unless you want to change the format of the destination to a percentage.
6. In the Paste Special dialog, under “Operation”, select Multiply.
7. Click OK.  The values in selected cells change.
8. Delete the contents of the temporary cell.

Change the Sign of a Block of Data

Here, all you do is multiply the data by -1.  The rest of the instructions are the same as in the previous section.

Acknowledgement: Scale, Offset, or Change the Sign of Data in Excel

This tip derives from an email from engineerexcel.com.  More here: Scale, Offset, or Change the Sign of Data in Excel.