Merging Cells to a Single Sum

As you analyze your data in a worksheet, one common task is to look for ways to simplify the amount of data in a worksheet. For instance, you might want to "merge" several consecutive cells together in an Excel worksheet, leaving only the sum of the original cells as a value. For instance, if you have values in the rangeB3:F3, how would you collapse the range into a single cell that contains just the sum of that range?

The easiest way I have found to accomplish this task is as follows:

1. Select the cell just to the right of the range you want to collapse. (In the above example, you would select cell G3.)
2. In this cell, enter a SUM formula that adds up the range. For instance, the cell could contain the formula =SUM(B3:F3).
3. Copy this formula down to other cells, if necessary.
4. Select all the cells that contain the SUM formula.
5. Press Ctrl+C to copy the cells to the Clipboard. The cells should all still be selected.
6. Choose Paste Special from the Edit menu or, in Excel 2007, click the down-arrow at the bottom of the Paste option (at the left side of the ribbon) and choose Paste Special from the resulting drop-down list. Excel displays the Paste Special dialog box. (Click here to see a related figure.)
7. Make sure the Values option is chosen.
8. Click on OK.
9. Delete the original range of cells. (For example, B3:F3.)