Excel Consolidate Rows With the Same Value

Yes. To consolidate rows that share a value in Excel, summarize them with a PivotTable or Power Query’s Group By command; for a live formula-based result, list the distinct values and use SUMIF to total each group. The right method depends on whether you want a one-time summary, a refreshable report, or a cleaned table that replaces the original rows.

What consolidating rows means

Consolidating rows usually means collecting records with the same key—such as a product, customer, region, or date—and producing one output row per key. The repeated rows might have quantities or sales to add, costs to average, or fields to count. For example, if a sales list has three rows for North with amounts of 120, 45, and 35, a sum-based consolidation produces one North row with a total of 200.

Decide what counts as the same value before choosing a tool. If names are in column A and amounts are in column B, grouping by column A combines rows with identical names. If the key is a combination, such as customer and month, both fields should define the group. Grouping only by customer would merge that customer’s records across all months.

Also decide what should happen to the other columns. Additive figures can be summed; a date might need to show the earliest or latest transaction; and descriptive text may need to be kept, joined, or excluded. Excel cannot infer the right business rule from the data, so choose an aggregation deliberately.

Choose a method for your goal

PivotTable: Best for a quick summary you can rearrange, filter, and refresh.
Power Query: Best for repeatable cleanup and grouping that can be refreshed from a source table or file.
Formulas: Best when you want a summary that updates as the source data changes and remains visible beside the source.
Remove Duplicates: Use only when you want to keep one complete record and discard repeated records—not when you need to add their amounts together.

For a single total by category, a PivotTable is often the simplest starting point. If the source is regularly replaced or appended, Power Query avoids repeating manual steps. Formulas are useful for compact, transparent summaries, but they need a clear range and may require extra work as the data grows.

Summarize matching rows with a PivotTable

A PivotTable groups identical labels and applies a summary function to the related values. It does not delete or alter the source rows, so it is a safe way to inspect totals before deciding whether a separate consolidated table is needed.

Make sure the source has one header row, no blank header cells, and a consistent field in each column. Click inside the data or select the full range.
On Windows, choose Insert > PivotTable, confirm the table or range, and choose where to place the PivotTable. Select New Worksheet for a separate report.
In the PivotTable Fields pane, drag the field to combine—such as Region—into Rows. Drag a numeric field, such as Sales, into Values.
Check the value summary. Excel commonly sums numeric data, but if the field is being counted or another calculation is shown, open its field menu, choose Value Field Settings, and select the appropriate summary, such as Sum, Count, or Average.

For the example with North sales of 120, 45, and 35, placing Region in Rows and Sales in Values produces a North total of 200. The same layout creates totals for each other region. To group by two fields, place both fields in Rows, in the order you want them nested.

When source records change, refresh the PivotTable using PivotTable Analyze > Refresh, or right-click inside it and choose Refresh. If new rows are added outside the original source range, convert the source to an Excel table first with Insert > Table (or Ctrl+T on Windows; Command+T on Mac), then build the PivotTable from that table. This makes it easier for the PivotTable source to include new table rows after a refresh.

Use Power Query to group and load consolidated data

Power Query is a better fit when consolidation is one step in a repeatable import or cleanup process. It records the transformation steps, leaves the original source intact, and can rerun those steps when you refresh the query. In Excel for Microsoft 365 on Windows, select a cell in the source table and choose Data > From Table/Range. If prompted, confirm that the table has headers.

In the Power Query Editor, select the column or columns that define a group, then choose Home > Group By. In the dialog, select the column to aggregate, give the result a name, and choose an operation such as Sum, Average, Count Rows, Minimum, or Maximum. Use Advanced when the group depends on more than one key or you need multiple aggregations. Select OK to preview the grouped result.

For example, group by Region and sum Sales to turn several North records into one North row and a total of 200. When the preview is correct, choose Home > Close & Load to return the result to Excel. Later, use Data > Refresh All to rerun the query after the source changes. Exact Power Query availability and options can differ by Excel edition and platform; Microsoft 365 for Windows is the assumed interface here.

Power Query is especially useful when incoming data needs cleanup before grouping—for example, trimming spaces, changing data types, or combining monthly files. These steps can reduce accidental split groups, such as North and North followed by a space. Review the query’s result and data types before loading; a text-formatted amount cannot reliably be treated as a numeric total until its type is corrected.

Build a formula-based summary

In Microsoft 365, UNIQUE can produce a distinct list of keys, and SUMIF can total the amounts for each key. This keeps the summary connected to the source without creating a PivotTable. The example below assumes the six regions are in A2:A7 and their sales amounts are in B2:B7: North 120, South 80, North 45, East 60, South 20, and North 35.

With the region names in A2:A7, enter this in D2 to list each distinct name once:

=UNIQUE(A2:A7)

With source names in A2:A7, source amounts in B2:B7, and the first unique name in D2, enter this in E2 to calculate its total, then fill the formula down beside the distinct names:

=SUMIF($A$2:$A$7,D2,$B$2:$B$7)

The results are North 200, South 100, and East 60. The arithmetic checks against the source: North is 120 + 45 + 35 = 200, South is 80 + 20 = 100, and East has 60. UNIQUE and dynamic arrays are available in Microsoft 365 and Excel 2021 or later. In older Excel versions, make a distinct list another way, then use SUMIF beside it.

The example uses fixed ranges so the calculation is easy to follow. For data that grows, an Excel table with structured references can make formulas easier to maintain, or you can expand the source ranges as new records arrive. Be aware that the UNIQUE list and the SUMIF ranges must cover the same source records. If the data contains blank keys, decide whether blanks should appear in the summary or be excluded.

Why Remove Duplicates is not the same thing

The command Data > Remove Duplicates removes repeated records from a selected range or table. It keeps one instance of a duplicate and deletes the others; it does not add their values together. That is useful for making a list of unique customer names, but it is wrong if three order rows must become one row containing the combined quantity.

It also matters which columns are selected in the Remove Duplicates dialog. If you select only the name column, Excel treats repeated names as duplicates even if the remaining columns differ. If you select every column, two rows count as duplicates only when all selected values match. Make a copy before removing records if you may need the original detail later.

Clean the keys and check the output

Rows that appear identical may not contain identical values. A trailing space, inconsistent spelling, different date values, or a number stored as text can create separate groups or incorrect totals. Before consolidating, standardize the key fields and check that numeric columns are truly numeric. In Power Query, use its text-cleaning and data-type tools; in a worksheet, inspect suspicious values and correct the source rather than relying on visual appearance alone.

After grouping, validate a few results against the detail rows and compare the total of the source amounts with the total of the consolidated amounts. If the figures differ, check for filtered-out records, blank or misspelled keys, incorrect value-field settings, and text values in the amount column. A total that matches is a useful check, although it does not prove every group was assigned correctly.

For a combination key, such as Region and Product, group on both columns in Power Query or place both fields in a PivotTable’s Rows area. In a formula summary, create a distinct list of the combinations or use a helper key carefully. Do not group by a combined text value if the separator could also occur in the original values, since different pairs could then become ambiguous.

Excel’s Consolidate command and its limits

Excel also has a command literally named Data > Consolidate. It combines values from multiple ranges using a function such as Sum or Average, with options to match labels in the top row or left column. It is designed for consolidating separate blocks or worksheets with a similar layout, rather than being the most direct way to group a transaction list into one row per repeated key.

For a list of records with a category in each row, a PivotTable or Power Query Group By is usually clearer. Use Data > Consolidate when the inputs are separate ranges that share a layout and the desired output is a summary of those ranges. It does not offer the same flexible, row-oriented cleanup workflow as Power Query, and it is not a substitute for removing exact duplicate records.

Frequently Asked Questions

How do I combine duplicate names and add their amounts in Excel?
Use a PivotTable with the name field in Rows and the amount field in Values, set to Sum. Alternatively, list unique names and use SUMIF to calculate each name’s total.

Can Excel merge duplicate rows automatically?
A PivotTable summarizes duplicates, and Power Query can create a refreshable grouped result. Neither needs to overwrite the original records. Remove Duplicates does delete repeated rows, but it does not combine their amounts.

How do I consolidate rows using two matching columns?
Define both columns as the key. Add both fields to the PivotTable Rows area or select both in Power Query’s Group By dialog. This keeps, for example, each Region–Product pair separate.

Will a PivotTable update when I add new rows?
Refresh it after changing the source. If you expect new records, use an Excel table as the source so added rows are included more reliably, then refresh the PivotTable.

How can I combine text from matching rows?
Grouping and summing do not automatically join text fields. Decide whether the text should be retained, selected by a rule, or concatenated; Power Query can group rows with a more customized transformation, while simple PivotTable summaries are intended for aggregations such as sums and counts.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.