--- description: When analyzing the data in Excel, you may find it contains multiple duplicate rows. In this case, perhaps you'll want to quickly consolidate the rows. This post will offer 2 quick means to get it. title: 2 Easy Ways to Consolidate Rows in Your Excel image: https://www.datanumen.com/blogs/wp-content/uploads/2018/05/sample-excel.jpg --- - - - [Home](https://www.datanumen.com/blogs/) > [Solutions](https://www.datanumen.com/blogs/category/solutions/) > [Office Solutions](https://www.datanumen.com/blogs/category/solutions/office-solutions/) > [Excel Solutions](https://www.datanumen.com/blogs/category/solutions/office-solutions/excel-solutions/) > 2 Easy Ways to Consolidate Rows in Your Excel # 2 Easy Ways to Consolidate Rows in Your Excel [Excel Solutions](https://www.datanumen.com/blogs/category/solutions/office-solutions/excel-solutions/) October 3, 2025 Share Now: - - - - - - - - *When analyzing the data in Excel, you may find it contains multiple duplicate rows. In this case, perhaps you’ll want to quickly consolidate the rows. This post will offer 2 quick means to get it.* Many users frequently need to merge the duplicate rows and sum the according values in Excel. For instance, I have a range of data in an Excel worksheet which contains a plenty of duplicate entries, like the following screenshot. Hence, I wish to consolidate the duplicate rows and sum the corresponding values in another column. It will be definitely troublesome if I manually do this. Therefore, I utilize the following 2 ways to realize it.![Sample Excel Sheet](https://www.datanumen.com/blogs/wp-content/uploads/2018/05/sample-excel.jpg "Sample Excel Sheet") ## Method 1: Use “Consolidate” Function 1. First off, click a blank cell where you want to place the merged and summed data. 2. Then, turn to “Data” tab and click on the “Consolidate” button.![Click "Consolidate" Button]( "Click \"Consolidate\" Button") 3. In the popup dialog box, ensure “Sum” is selected in “Function” box. 4. Next, click the ![Reference Button]( "Reference Button")  button.![Select "Sum" Function]( "Select \"Sum\" Function") 5. Later, select the range which you want to consolidate and click ![Reference Button]( "Reference Button")  button.![Select Range]( "Select Range") 6. After that, click “Add” button in “Consolidate” dialog.![Add Range for Reference]( "Add Range for Reference") 7. Subsequently, check the “Top row” and “Left column” option.![Use Lables in "Top Row" and "Left Column"]( "Use Lables in \"Top Row\" and \"Left Column\"") 8. Finally, click “OK” button. 9. At once, the rows are consolidated, as shown in the following screenshot.![Consolidated Data]( "Consolidated Data") ## Method 2: Use Excel VBA Code 1. At the very beginning, select the range that you want.![Select Range]( "Select Range") 2. Then, trigger VBA editor according to “ [How to Run VBA Code in Your Excel](https://www.datanumen.com/blogs/how-to-run-vba-code-in-your-excel/)“. 3. Next, copy the following VBA code into a module. ``` Sub MergeRowsSumValues() Dim objSelectedRange As Excel.Range Dim varAddressArray As Variant Dim nStartRow, nEndRow As Integer Dim strFirstColumn, strSecondColumn As String Dim objDictionary As Object Dim nRow As Integer Dim objNewWorkbook As Excel.Workbook Dim objNewWorksheet As Excel.Worksheet Dim varItems, varValues As Variant On Error GoTo ErrorHandler Set objSelectedRange = Excel.Application.Selection varAddressArray = Split(objSelectedRange.Address(, False), ":") nStartRow = Split(varAddressArray(0), "$")(1) strFirstColumn = Split(varAddressArray(0), "$")(0) nEndRow = Split(varAddressArray(1), "$")(1) strSecondColumn = Split(varAddressArray(1), "$")(0) Set objDictionary = CreateObject("Scripting.Dictionary") For nRow = nStartRow To nEndRow strItem = ActiveSheet.Range(strFirstColumn & nRow).Value strValue = ActiveSheet.Range(strSecondColumn & nRow).Value If objDictionary.Exists(strItem) = False Then objDictionary.Add strItem, strValue Else objDictionary.Item(strItem) = objDictionary.Item(strItem) + strValue End If Next Set objNewWorkbook = Excel.Application.Workbooks.Add Set objNewWorksheet = objNewWorkbook.Sheets(1) varItems = objDictionary.keys varValues = objDictionary.items nRow = 0 For i = LBound(varItems) To UBound(varItems) nRow = nRow + 1 With objNewWorksheet .Cells(nRow, 1) = varItems(i) .Cells(nRow, 2) = varValues(i) End With Next objNewWorksheet.Columns("A:B").AutoFit ErrorHandler: Exit sub End Sub ``` ![VBA Code - Consolidate Rows]( "VBA Code - Consolidate Rows") 4. After that, press “F5” to run this macro now. 5. When macro finishes, a new Excel workbook will show up, in which you can see the merged rows and summed data, like the image below.![Consolidated Data in New Excel Workbook]( "Consolidated Data in New Excel Workbook") ## Comparison | ** ** | **Advantages** | **Disadvantages** | | --- | --- | --- | | **Method 1** | Easy to operate | Can’t process the two columns not next to each other | | **Method 2** | 1. Convenient for reuse | 1. A bit difficult to understand for VBA newbies | | 2. Won’t mess up the original Excel sheet in that it put the merged data in the new file | 2. Can’t process the two columns not next to each other | ## When Encountering Excel Crash As we all know, Excel can crash from time to time. Under this circumstance, at its worst, the current Excel file may be corrupted directly. At that time, you have no choice but to attempt [Excel recovery](https://www.datanumen.com/excel-repair/). It demands you to either ask professionals for help or make use of a specialized Excel repair tool, such as DataNumen Excel Repair. ## Author Introduction: Shirley Zhang is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including [corrupt SQL Server](https://www.datanumen.com/sql-recovery/) and outlook repair software products. For more information visit [www.datanumen.com](https://www.datanumen.com/) Share Now: - - - - - - - - [← Previous post](https://www.datanumen.com/blogs/how-to-auto-log-each-printed-outlook-email-in-excel-workbook/) [Next post →](https://www.datanumen.com/blogs/how-to-run-vba-code-in-your-excel/) Comments are closed. ![Data Recovery Blog]() Follow or like us on Facebook, LinkedIn and Twitter to get all promotions, latest news and updates on our products and company. - - - ### Featured Products - [DataNumen Outlook Repair](https://www.datanumen.com/outlook-repair/) - [DataNumen SQL Recovery](https://www.datanumen.com/sql-recovery/) - [DataNumen Exchange Recovery](https://www.datanumen.com/exchange-recovery/) - [DataNumen Access Repair](https://www.datanumen.com/access-repair/) - [DataNumen Excel Repair](https://www.datanumen.com/excel-repair/) - [DataNumen PDF Repair](https://www.datanumen.com/pdf-repair/) - [DataNumen Word Repair](https://www.datanumen.com/word-repair/) - [DataNumen Zip Repair](https://www.datanumen.com/zip-repair/) ### Popular posts ```json {"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.datanumen.com\/blogs\/2-easy-ways-to-consolidate-rows-in-your-excel\/#article","isPartOf":{"@id":"https:\/\/www.datanumen.com\/blogs\/2-easy-ways-to-consolidate-rows-in-your-excel\/"},"author":{"name":"AuthorCCW","@id":"https:\/\/www.datanumen.com\/blogs\/#\/schema\/person\/33ded6c47c77587f9158d99f05e23957"},"headline":"2 Easy Ways to Consolidate Rows in Your Excel","datePublished":"2018-05-24T03:34:27+00:00","dateModified":"2025-10-03T09:23:26+00:00","mainEntityOfPage":{"@id":"https:\/\/www.datanumen.com\/blogs\/2-easy-ways-to-consolidate-rows-in-your-excel\/"},"wordCount":442,"commentCount":0,"publisher":{"@id":"https:\/\/www.datanumen.com\/blogs\/#organization"},"image":{"@id":"https:\/\/www.datanumen.com\/blogs\/2-easy-ways-to-consolidate-rows-in-your-excel\/#primaryimage"},"thumbnailUrl":"https:\/\/www.datanumen.com\/blogs\/wp-content\/uploads\/2018\/05\/sample-excel.jpg","articleSection":["Excel Solutions"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.datanumen.com\/blogs\/2-easy-ways-to-consolidate-rows-in-your-excel\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.datanumen.com\/blogs\/2-easy-ways-to-consolidate-rows-in-your-excel\/","url":"https:\/\/www.datanumen.com\/blogs\/2-easy-ways-to-consolidate-rows-in-your-excel\/","name":"2 Easy Ways to Consolidate Rows in Your Excel","isPartOf":{"@id":"https:\/\/www.datanumen.com\/blogs\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.datanumen.com\/blogs\/2-easy-ways-to-consolidate-rows-in-your-excel\/#primaryimage"},"image":{"@id":"https:\/\/www.datanumen.com\/blogs\/2-easy-ways-to-consolidate-rows-in-your-excel\/#primaryimage"},"thumbnailUrl":"https:\/\/www.datanumen.com\/blogs\/wp-content\/uploads\/2018\/05\/sample-excel.jpg","datePublished":"2018-05-24T03:34:27+00:00","dateModified":"2025-10-03T09:23:26+00:00","description":"When analyzing the data in Excel, you may find it contains multiple duplicate rows. In this case, perhaps you'll want to quickly consolidate the rows. This post will offer 2 quick means to get it.","inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.datanumen.com\/blogs\/2-easy-ways-to-consolidate-rows-in-your-excel\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.datanumen.com\/blogs\/2-easy-ways-to-consolidate-rows-in-your-excel\/#primaryimage","url":"https:\/\/www.datanumen.com\/blogs\/wp-content\/uploads\/2018\/05\/sample-excel.jpg","contentUrl":"https:\/\/www.datanumen.com\/blogs\/wp-content\/uploads\/2018\/05\/sample-excel.jpg","width":856,"height":542,"caption":"Sample Excel Sheet"},{"@type":"WebSite","@id":"https:\/\/www.datanumen.com\/blogs\/#website","url":"https:\/\/www.datanumen.com\/blogs\/","name":"Data Recovery Blog","description":"Discuss every aspect of data recovery","publisher":{"@id":"https:\/\/www.datanumen.com\/blogs\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.datanumen.com\/blogs\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.datanumen.com\/blogs\/#organization","name":"Data Recovery Blog","url":"https:\/\/www.datanumen.com\/blogs\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.datanumen.com\/blogs\/#\/schema\/logo\/image\/","url":"https:\/\/www.datanumen.com\/blogs\/wp-content\/uploads\/2019\/02\/logowithouttext.jpg","contentUrl":"https:\/\/www.datanumen.com\/blogs\/wp-content\/uploads\/2019\/02\/logowithouttext.jpg","width":296,"height":397,"caption":"Data Recovery Blog"},"image":{"@id":"https:\/\/www.datanumen.com\/blogs\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/DataNumen","https:\/\/x.com\/DataNumen","http:\/\/www.linkedin.com\/company\/datanumen","https:\/\/myspace.com\/datanumen\/","http:\/\/www.pinterest.com\/datanumen\/","https:\/\/www.youtube.com\/channel\/UCVIIuuKfUBHSJauUaVP13tw"]},{"@type":"Person","@id":"https:\/\/www.datanumen.com\/blogs\/#\/schema\/person\/33ded6c47c77587f9158d99f05e23957","name":"AuthorCCW","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/3516ed90fc242780d3947a989f895bc46e70c3e83485d30f2b457a007906aa58?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/3516ed90fc242780d3947a989f895bc46e70c3e83485d30f2b457a007906aa58?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/3516ed90fc242780d3947a989f895bc46e70c3e83485d30f2b457a007906aa58?s=96&d=mm&r=g","caption":"AuthorCCW"},"sameAs":["https:\/\/www.datanumen.com\/"]}]} ```