Excel Add or Multiply Two Columns Without Using Formulas
This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Add or Multiply Two Columns Without Using Formulas
Problem: I’ve prepared a summary of sales by rep for the month. Due to an accounting glitch, someone gave me a similar file with additional sales made on the last day of the month. I need to add the new sales to the old sales. There is no need to keep the original two columns of partial month’s sales.
Figure 213 Add column H to column B.
Strategy: You can copy the new values in column H and use Home, Paste dropdown, Paste Special, Add to add the values to column B. Follow these steps:
1. Select H4:H22. Type Ctrl+C to copy the cells to the Clipboard.
2. Move the cell pointer to B4. Select Home, Paste dropdown, Paste Special. (Don’t select the large Paste icon; instead, choose the dropdown below the icon.)
3. In the Paste Special dialog box, choose the Add option in the Operation section. Optionally, also choose Values in the Paste section in order to preserve the formatting in column B. Click OK.
Figure 214 Choose Values and Add.
Results: The new sales values from column H are added to the values in column B. You can safely delete column H.
Figure 215 Excel adds the range on the Clipboard to column B.
Gotcha: If column B is properly formatted and the temporary data in H is not formatted, the default Paste All option will cause the formats in column B to be lost if you choose only Add and not Values.
Additional Details: The technique described here for selecting Add in the Paste Special dialog has an interesting effect if you add cells to a range that contains a formula. Amazingly, Excel handles it correctly. For example cell D4 contains a formula.
Figure 216 Before pasting, this cell contains a formula.
If you select Add in the Paste Special dialog to add a value to this formula, Excel changes the formula to add the value.
Figure 217 After Paste Special Add, Excel modifies the formula.
Additional Details: You can use the Operation section of Paste Special to handle other situations. In the figure below, you might want to increase the contract rates by 2%. Type 102% in a blank cell. Copy that cell. Select the range of contract rates and use Paste Special, Values, Multiply.
Figure 218 Multiply this range by the 102% on the clipboard.
This method was also used in Learn Excel Podcast episode 1348. Someone had received a dataset where a column of numbers needed to be divided by 100. For whatever reason, the creator of the data had put 123 instead of 1.23. The solution was to put 0.01 in a cell, copy the cell, then Paste Special Multiply.