Microsoft Excel: Add Two Columns Without Using Formulas

Problem: You prepared a summary of sales by rep for the month. Due to an accounting glitch, someone gave you a similar file with additional sales made on the last day of the month (Fig. 248). Add the new sales to the old sales. You don’t need to keep the original two columns of partial month’s sales

Strategy: Copy the new values in column H and use Paste Special – Add to add the values to column B. Follow these steps.

1) Select H4:H22. From the menu, select Edit – Copy to copy the cells to the clipboard.

2) Move the cell pointer to B4. From the menu, select Edit – Paste Special.

3) As shown in Fig. 249, 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. Choose OK.

Result: As shown in Fig. 250, the new sales data from column H is 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 option of Paste All will cause the formats in column B to be lost. This is why you should consider choosing both Values and Add in the Transpose dialog.

Additional Information: The Paste Special – Add technique has an interesting effect if you add cells to a range that contains a formula. Amazingly, Excel does handle this correctly. Cell D4 contains a formula, as shown in Fig. 251. If you Paste Special – Add a value to this formula, Excel changes the formula to add the value, as shown in Fig. 252.

Summary: Using the Paste Special Add technique is useful to add two columns of numbers without using a formula.

Commands Discussed: Edit – Paste Special – Add

See all Microsoft Excel tips

Images