Retaining Formatting After a Paste Multiply

One of the really cool features of Excel is the many ways you can manipulate data using the Paste Special command. This command allows you to do all sorts of things to you data, as you paste it into a worksheet. One such manipulation you can perform is to multiply data as you paste. For instance, you can multiply all the values being pasted by -1, thereby converting them into negative numbers. To do so, follow these steps:

1. Place the value -1 in an unused cell of your worksheet.
2. Select the value and press Ctrl+C. Excel copies the value (-1) to the Clipboard.
3. Select the range of cells that you want to multiply by -1.
4. Display the Paste Special dialog box. (Click here to see a related figure.) In versions of Excel prior to Excel 2007 you do this by choosing Paste Special from the Edit menu. In Excel 2007 you display the Home tab of the ribbon, click the down-arrow under the Paste tool, and then click Paste Special.
5. Click on the Multiply radio button.
6. Click on OK.

At this point Excel multiplies the values in the selected cells by the value in the Clipboard. Unfortunately, if the cells in the selected range had special formatting, the formatting is also now gone, and the format of the cells is set to be the same as the cell you selected in step 2.

To make sure that the formatting of the target cells is not changed while doing the Paste Special, there is one other option you need to select in the Paste Special dialog box—Values. In other words, you would still select Multiply (as in step 5), but you would also select Values before clicking on OK.

With the Values radio button selected, Excel only operates on the values in the cells, and leaves the formatting of the target range unchanged.