Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Retaining Formatting After a Paste Multiply.
Written by Allen Wyatt (last updated January 1, 2020)
This tip applies to Excel 97, 2000, 2002, and 2003
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 your 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:
Figure 1. The Paste Special dialog box.
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.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2655) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Retaining Formatting After a Paste Multiply.
Best-Selling VBA Tutorial for Beginners Take your Excel knowledge to the next level. With a little background in VBA programming, you can go well beyond basic spreadsheets and functions. Use macros to reduce errors, save time, and integrate with other Microsoft applications. Fully updated for the latest version of Office 365. Check out Microsoft 365 Excel VBA Programming For Dummies today!
Need the contents of a cell to be shown in a direction different than normal? Excel makes it easy to have your content ...
Discover MoreAdding a custom format to Excel is easy. Having that custom format appear in all your workbooks is a different story ...
Discover MoreWant a quick and easy way to hid the information in a cell? You can do it with a simple three-character custom format.
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2020-08-01 19:04:41
Sastry
Retaining Formatting After a Paste Multiply - What if I wish to retain the formula. For e.g., I have the nos. 2, 5 & 7 in the cells and they all need to be multiplied with a common no., say, 3. I wish to choose paste-special and it will make the nos. 6, 15 & 21. Fair, but this doesn't retain the formula within the cells. I recall using that and some settings changed and I lose those formulas within those cells now. It shows a hard-coded figure of 6, 15 & 21. What if, i want to change the common multiplier to 5 instead of three. It will not give me 10, 50 & 70 since the original value is lost forever.
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Please Note:
This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here:
Comments