Add or Multiply Two Columns Without Using Formulas
December 07, 2021 - by Bill Jelen
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.
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.
Results: The new sales values from column H are added to the values in column B. You can safely delete column H.
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.
If you select Add in the Paste Special dialog to add a value to this formula, Excel changes the formula to add the value.
Additional Details: You can use the Operation section of Paste Special to handle other situations. In this figure, 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.
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.
Tip: Excel can automatically insert s decimal point like the old adding machines. Go to File, Options, Advanced.
Additional Details: You can enter a negative number for the number of places. If you want to enter 123 million as 123, change the setting to -6.
This article is an excerpt from Power Excel With MrExcel
