Data Import & Export

How to Combine Duplicate Rows and Sum Columns in Excel

Elise WilliamsElise Williams Nov 11, 2026 875 views

Question details

The user needs a method to combine records with matching identifiers, sum their corresponding numeric columns, and retain other descriptive fields.

How to Combine Duplicate Rows and Sum Columns in Excel
Product
Excel
Device & OS
not provided
Scenario
Cleaning up and summarizing datasets that contain duplicate identifiers with associated numeric and descriptive data.
Observed behavior
Duplicate rows exist in the dataset, and the goal is to merge them into single rows per identifier while calculating the total for specific numeric values.
Before you start

Ensure your dataset has clear column headers and no blank rows. It is also highly recommended to create a copy of your original data before applying complex formulas or SQL queries.

Solution 1

Use an SQL Query to Group and Sum Data

Ideal for users who manage data through database connections or Power Query, allowing for robust grouping and aggregating.

If you are querying a database or using a SQL interface within your spreadsheet environment, you can use standard SQL clauses to group duplicates and sum the metrics.

1
Select your columns

Write a SELECT statement to choose your identifier and descriptive columns (e.g., f01, f04, f05).

2
Apply the SUM function

Use the SUM() function for the numeric columns you want to total, such as SUM(f02) and SUM(f03).

3
Group the records

Add the GROUP BY clause at the end of your query to group by the identifier and descriptive columns. For example: SELECT f01, SUM(f02), SUM(f03), f04, f05 FROM sheet1 GROUP BY f01, f04, f05.

Easy Data Management with WPS

Easily Combine and Sum Data with WPS Spreadsheet

Instead of writing complex SQL queries or advanced LAMBDA functions, you can quickly consolidate duplicate rows and sum columns using the intuitive Pivot Table and Data Consolidate features built right into WPS Spreadsheet.

  1. 1. Open your dataset: Launch WPS Spreadsheet and open the file containing your duplicate rows.
  2. 2. Insert a Pivot Table: Highlight your data range, navigate to the Insert tab, and click PivotTable.
  3. 3. Set up your rows: Drag your unique identifier and descriptive fields into the 'Rows' area of the PivotTable Field List.
  4. 4. Sum the numeric values: Drag the numeric columns into the 'Values' area. WPS Spreadsheet will automatically sum the data for each unique identifier.
Quickly group and summarize duplicate records without writing complex code.Fully compatible with Microsoft Excel (.xlsx) formulas and formats.Built-in Data Consolidate tool to merge identical records instantly.Lightweight software with a familiar interface for a seamless workflow.
microsoft office alternative - wps office

Frequently Asked Questions

Can I combine duplicate rows without using formulas?

Yes, the easiest way to combine duplicates and sum values without formulas is by inserting a Pivot Table or using the 'Consolidate' tool located under the Data tab.

Why is my SQL query returning an error when summing columns?

Ensure that all non-aggregated columns (identifiers and descriptive fields) included in your SELECT statement are also explicitly listed in your GROUP BY clause.

Is the LAMBDA function available in all versions of Excel?

No, the LAMBDA function is only available to Microsoft 365 subscribers. Users on older standalone versions (like 2019 or 2016) should use Pivot Tables or Power Query instead.

How does the Data Consolidate feature work?

The Consolidate feature allows you to select ranges of data and apply a specific function, such as SUM, to combine data based on matching labels in the top row or left column.