Written by Allen Wyatt (last updated April 18, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and 2021
As you analyze your data in a worksheet, one common task is to look for ways to simplify the amount of data you need to work with. One way to do this is 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 range B3: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:
Figure 1. The Paste Special dialog box.
When you clicked the down-arrow under the Paste tool (in step 7), you may have noticed a number of different choices you could make. If you don't want to display the Paste Special dialog box, you could instead click the Values option in the Paste Values section of the drop-down list. The Values option is the left-most option in the Paste Values section; it looks like a clipboard with the number 123 on it.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9146) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and 2021. You can find a version of this tip for the older menu interface of Excel here: Merging Cells to a Single Sum.
Program Successfully in Excel! This guide will provide you with all the information you need to automate any task in Excel and save time and effort. Learn how to extend Excel's functionality with VBA to create solutions not possible with the standard features. Includes latest information for Excel 2024 and Microsoft 365. Check out Mastering Excel VBA Programming today!
Have you ever copied information from one worksheet to another, only to have the information you paste not look the way ...
Discover MoreIf you have a large number of values in a column, you may want to move the values that meet specific criteria to another ...
Discover MoreThe fill handle can save a huge amount of time when you are editing a worksheet. If you are really good at using the ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
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 © 2025 Sharon Parq Associates, Inc.
Comments