Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Retaining Formatting After a Paste Multiply.
Written by Allen Wyatt (last updated August 14, 2021)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
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 6), 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 (10813) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Retaining Formatting After a Paste Multiply.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
Is the information in your cells too jammed up? Here are some ways you can add some white space around that information ...
Discover MoreWant to repeat cell contents over and over again within a single cell? Excel provides two ways you can duplicate the content.
Discover MoreNeed to replace the formats applied to some cells with a different format? You can use Excel's Find and Replace tool to ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2021-08-14 09:01:50
JD Murphy
How about Retaining Formatting After a Find and Replace? Any solution?
2021-08-14 08:46:00
Jeff Carno
This is why I love your posts so much. I've been using excel since it came out and never knew that. Thank you!
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments