Get help

Help center

Guides and answers

Contact support

Write to our team

Sign in to

Workflows

Build processes with your team

Automations

Connect files and automate tasks

One system, four parts

AI agents

Virtual Employees that work 24/7

Automations

Connect files and automate tasks

Workflows

Build processes with your team

Data Space

Trusted data sharing and control

Solutions by department

Finance

Reports, budgets, and reconciliations

Inventory and manufacturing

Stock, orders, and production in one view

HR

Timesheets, attendance, and reviews

Project management

Status and workload across projects

Sales and CRM

Leads and deals kept up to date

Education

Student records and school data

Finance

Turn manual finance work into reports that update themselves.

What Finance teams automate

  • Track payables and receivables
  • Build cash flow statements
  • Compare budget against actuals

Inventory and manufacturing

See stock levels and automate ordering.

What inventory and manufacturing teams automate

  • Track inventory across multiple sites
  • Connect sales, purchasing, and warehouse data
  • Link a barcode scanner to your sheet

HR

Run recruitment, attendance, and OKR tracking from your sheets.

What HR teams automate

  • Track recruitment and onboarding
  • Record team OKRs and results
  • Track check-ins, check-outs, and leave

Project management

Assign tasks and track hours without manual updates.

What project teams automate

  • Assign tasks and track status changes
  • Estimate effort and log hours worked
  • Track milestones across projects

Sales and CRM

Track leads and sales performance with automated reports.

What sales teams automate

  • Track and convert leads
  • Set sales goals and track team metrics
  • Monitor sales KPIs on dashboards

Education

Automate attendance, grades, and school purchasing.

What schools automate

  • Track attendance and notify parents
  • Record and compare class grades
  • Track inventory lent to students and staff

Find a use case

By department

How each team uses Sheetgo

By industry

How each industry uses Sheetgo

By company size

Small, medium-sized, or large

Learn

Blog

Guides and product news

Customer stories

How teams like yours use Sheetgo

Help center

Guides and answers

Why Sheetgo

Compare

How Sheetgo compares with other tools

Security

How we protect your data

Trust center

Our compliance details

Sign in to

Workflows

Build processes with your team

Automations

Connect files and automate tasks

One system, four parts

AI agents

Virtual Employees that work 24/7

Automations

Connect files and automate tasks

Workflows

Build processes with your team

Data Space

Trusted data sharing and control

Solutions by department

Finance

Reports, budgets, and reconciliations

Inventory and manufacturing

Stock, orders, and production in one view

HR

Timesheets, attendance, and reviews

Project management

Status and workload across projects

Sales and CRM

Leads and deals kept up to date

Education

Student records and school data

Find a use case

By department

How each team uses Sheetgo

By industry

How each industry uses Sheetgo

By company size

Small, medium-sized, or large

Get started

Templates

Ready-made workflows to start from

Learn

Blog

Guides and product news

Customer stories

How teams like yours use Sheetgo

Help center

Guides and answers

Why Sheetgo

Compare

How Sheetgo compares with other tools

Security

How we protect your data

Trust center

Our compliance details

Help center

Guides and answers

Contact support

Write to our team

Workflows

Build processes with your team

Automations

Connect files and automate tasks

How to combine rows in Excel and consolidate duplicate data

When working with Excel spreadsheets, one of the most common tasks is to merge or combine data from different worksheets. Although this may seem like a simple procedure, it’s likely that you’ll end up with duplicate entries. If your dataset is small, these duplicates might be easier to spot and quickly removed. However, this entails manual work, and it can become overwhelming if you work with high-volume data. Excel offers two different features that can help you achieve this in a quick and efficient way.

In this article, you’ll learn how to combine duplicate rows and sum the values in Excel using three methods. First, you’ll see how to combine duplicates using the consolidate function in Excel. Second, a more advanced method involves using VBA code to merge duplicates in Excel. As a third alternative option to using Excel, you’ll learn how Sheetgo, an AI-powered data automation platform, can consolidate Excel files automatically.

How to combine duplicate rows and sum the values in Excel?

Excel offers many features to help you carry out complex tasks in a quick and easy way. Here, you’ll learn how to combine duplicates in Excel following two useful methods.

Combine duplicate rows with the Consolidate function in Excel

The consolidate feature in Excel can be used to consolidate multiple worksheets or rows in Excel. This is how you can combine and add rows in Excel using the consolidate function.

1. Open Excel and click on the cell where you want to place the combined data result.

Combine rows in Excel — empty cell selected next to the loan data table on the Home tab

2. Go to Data > Consolidate.

Combine rows in Excel — Consolidate button on the Data tab of the ribbon

3. From the “Functions” drop-down list, select “Sum”. Click on the upwards arrow button to select the cell range that you wish to consolidate.

Combine rows in Excel — Consolidate dialog with Sum selected in the Function list

4. Select the cell range you want to combine in the worksheet, then click the arrow button again to return to the Consolidate dialog.

Combine rows in Excel — collapsed Consolidate Reference box holding the selected range

5. Click on the “Add” button to include the cell reference in the “All references” space.

Combine rows in Excel — range in the Reference field next to the Add button

6. Select the “Top row” and “Left column” options from the “Use labels in” group. Once you finish, click “OK” to apply changes.

Combine rows in Excel — range listed under All references, with Top row and Left column checked

Note: If your data range doesn’t have header rows, then leave the “Top row” option cleared.

7. You should now see a new list of rows containing only unique values. Any value corresponding to the same record will be summed up.

Combine rows in Excel — consolidated list of unique names with their values summed

This method is a good option if you don’t mind losing the original data; however, if you’d like to keep the original values of duplicates, then make a copy of the worksheet and apply this method in the copy version.

Merge duplicate rows with VBA code

One of the main advantages of processing data in VBA is that you can record a macro of your action and then run it every time you need to perform a similar task in the future. In other words, you have the option of automating the task of combining data. This is how you can merge duplicates using VBA in Excel.

1. Select the range of cells that you’d like to consolidate and go to Developer > Visual Basic for Applications.

Merge rows in Excel with VBA — Visual Basic button on the Developer tab

2. Select Insert > Module.

Merge rows in Excel with VBA — Insert menu in the VBA editor with Module highlighted

3. Type the macro shown in the screenshot below into the Module window.

Merge rows in Excel with VBA — MergeRowsSumValues macro pasted into the module window

4. Press “F5” to run this macro or click on the “Play” icon, as shown below.

Merge rows in Excel with VBA — Run Sub/UserForm (F5) button above the macro code

5. Excel creates a new workbook with your merged rows and added data, as shown below.

Merge rows in Excel with VBA — new workbook listing each name with its summed annual compounding period

Although Excel consolidates rows in efficient ways, you still need to put in some effort on your part. Sheetgo, an AI-powered data automation platform, can also do the consolidation step for you. It merges rows from several Excel, Google Sheets, or CSV files into one sheet and refreshes it on a schedule.

Conclusion

Although the first method is easier to execute, it can’t process the two columns not next to each other. VBA is more convenient in case you want to reuse the function in the future, but it requires more coding knowledge. Another advantage of VBA is that it won’t alter the original worksheet, as the data is merged into a new file.

You should now be able to consolidate duplicates in Excel depending on whether you’d prefer to keep the original values or not. In case you want Excel to fully consolidate rows with multiple columns, the consolidate feature is a quick and easy-to-use method. If you prefer to consolidate rows in Excel based on more complex criteria and keep the original values intact, then using the VBA code to consolidate is the best option for you. Alternatively, Sheetgo can make this process easier and faster, since its AI-powered automations run the merge for you on a schedule.

If you want to learn more about consolidating data in Excel, check out these articles below:

You may also like…

How to lock cells in Excel — Excel logo next to a green padlock on a mint background

How to lock cells in Excel? (cells, sheets & formulas)

Learn how to lock cells in Excel to stop accidental edits, plus how to protect a sheet and lock formulas, with steps for Windows and Mac.

Excel version control — stacked XLS file icon in teal on a dark navy background

The beginner’s guide to Excel version control

Discover what Excel version control is, the version control features Excel has to offer, and how to use them to share, merge, and review Excel changes.

How to remove password protection from Excel — Excel icon next to a padlock with a masked password on a light blue background

How to remove a password from Excel (multiple methods)

Learn how to remove password protection from Excel: unlock a file or a sheet when you know the password, and what to try if you’ve forgotten it.