Consolidate Lines in Excel: Methods and Examples

How-to basics · Updated November 18, 2026

To consolidate lines (rows) in Excel, use Data > Data Tools > Consolidate to summarize matching data from one or more ranges. If you mean joining text from several cells into one line or cell, use a function such as =TEXTJOIN(" ",TRUE,A2:A4) instead; these are different tasks, so choose the method that matches your data.

What Consolidating Lines Means in Excel

Excel uses the word consolidate for combining data from multiple ranges into a summary. For example, you might have monthly sales figures on separate worksheets and want one annual summary, or you might want Excel to add together repeated product totals. The Consolidate command can calculate totals and other statistics from corresponding values.

It is not the same as appending rows, which means stacking complete records underneath one another, or joining text, which combines cell contents. The wording “consolidate lines” can refer to any of these, so first consider the result you need:

  • Summarize matching categories or positions: use Data > Consolidate.
  • Stack rows from tables with the same columns: use Power Query or copy the rows into one table.
  • Combine text into a cell: use TEXTJOIN or CONCAT.
  • Combine duplicate records into totals: use Consolidate, a PivotTable, or SUMIF/SUMIFS formulas.

Consolidate works best with organized source ranges: clear headers, consistent data types, and no subtotals mixed into the detail. It produces a summary, not a cleaned replacement for the original records.

Choose the Right Method for Your Data

The built-in Consolidate command is a practical option when the source ranges are separate but have a similar structure, and you want a compact summary. It can match categories by labels when the relevant row or column labels are included. It can also combine values by their position, which is useful only when the same positions represent the same things in every range.

For example, if three worksheets each have product names in column A and sales in column B, you can summarize sales by product. If instead each worksheet is a batch of customer transactions with the same column headings, Power Query’s Append operation is usually a better fit: it stacks the records and preserves individual transactions. A PivotTable is often convenient when you want to explore totals interactively, change groupings, or filter the summary.

For a small, fixed list of categories, formulas can be easier to inspect and update. SUMIF or SUMIFS calculates totals for criteria you specify. If the goal is merely to make text appear on separate lines inside one cell, use a text-joining formula rather than the Consolidate dialog.

How to Use Data Consolidate

These steps apply to Excel for Microsoft 365 on Windows. Put the output in a blank area or on a separate summary worksheet so it does not overwrite the source. The source ranges can be on different sheets, but they should contain the values and labels you intend to summarize.

  1. Select the upper-left cell where the summary should begin. Leave enough empty space to the right and below for the result.
  2. Open Data > Data Tools > Consolidate. If the command is not visible, check the Data tab’s Data Tools group or the ribbon layout available in your Excel window.
  3. In the Function list, choose the calculation you need. Sum is common for quantities or sales; other choices include Average, Count, Max, Min, Product, and statistical functions.
  4. Click in the Reference box, select the first source range, and click Add. Repeat for every range. Use the range selector in the dialog if you need to move to another worksheet.
  5. If the source ranges have headings, select Top row and/or Left column under Use labels as appropriate. For example, select Left column when product names identify the rows being matched.
  6. Select Create links to source data if you want the summary to show links back to its source cells and an outline structure. Leave it unchecked for a summary of values that is not linked in that way.
  7. Click OK, then check the result against the source data and format the summary as needed.

On Excel for Mac, the Consolidate feature is also available from the Data tab, though the ribbon arrangement can differ by release and window size. Excel for the web may not expose the same full Consolidate dialog; if you cannot find it, use the desktop app or choose a formula, PivotTable, or Power Query workflow supported by your version.

Example: Summarize Monthly Product Totals

Suppose January, February, and March each have a small range with product names in the first column and sales amounts in the second. A product may be listed in a different order on each sheet. Choose Sum, add all three two-column ranges, and select Left column under Use labels. Excel matches the product labels and adds the amounts for each product rather than assuming that the first row on every sheet refers to the same product.

If January records Apples 120 and Pears 80, February records Pears 95 and Apples 110, and March records Apples 130 and Pears 75, the consolidated totals are Apples 360 and Pears 250. The arithmetic is 120 + 110 + 130 for Apples and 80 + 95 + 75 for Pears. This is useful for a summary, but it does not retain each month’s transaction as a separate row. Keep the monthly source data if you may need to audit or analyze it later.

When you choose not to match by labels, Excel combines values by position. If the second row means Apples in one source but Pears in another, a positional consolidation can silently produce a misleading result. Use labels when the ranges contain category headings, and review the output for missing or inconsistent names.

Use Formulas to Consolidate Repeated Rows

A formula can be a better choice when you want a visible, criteria-based summary that updates as source values change. For a simple example, assume product names are in A2:A6, quantities are in B2:B6, and the product to summarize is written in D2. With Apples in D2, enter this in E2:

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

For the source rows Apples 4, Pears 2, Apples 3, Oranges 5, and Pears 6, the result for Apples is 7, because 4 + 3 = 7. Copy the formula down beside other product names to calculate their totals. The dollar signs keep the source ranges fixed while the criterion cell changes. SUMIF handles one condition; use SUMIFS when a total must meet multiple conditions, such as product and month.

If you need to join text rather than add numbers, assume the text to combine is in A2:A4. This formula puts the cell contents in one cell, separated by spaces:

=TEXTJOIN(" ",TRUE,A2:A4)

For example, cells containing “North”, “Regional”, and “Sales” return North Regional Sales. To put each value on a new line inside the result cell, use =TEXTJOIN(CHAR(10),TRUE,A2:A4) and turn on Home > Wrap Text for that cell. TEXTJOIN is available in current Microsoft 365 versions and newer perpetual Excel versions; older Excel versions may require a different approach. Joining text does not add numeric values or match duplicate labels.

When Power Query or a PivotTable Is Better

Choose Power Query when you need to append complete tables, especially when new rows arrive regularly. In Excel for Microsoft 365 on Windows, format each source range as a table, then use Data > Get Data > From Table/Range to bring it into Power Query. In the Power Query Editor, use Home > Append Queries to stack tables with corresponding columns. Load the result back to Excel and refresh the query when the source data changes. Column names and data types should be consistent; mismatched columns can create blanks or unexpected fields.

Choose a PivotTable when you want to group and summarize a dataset without writing formulas. Select a cell in the table and choose Insert > PivotTable, then place a category field in Rows and a numeric field in Values. Check the Values setting: Excel may count a field instead of summing it if the source contains text or blanks. Refresh the PivotTable after changing its source data.

These tools solve different problems. Consolidate creates a summary from selected ranges; Append preserves records by stacking them; a PivotTable summarizes a dataset interactively; formulas calculate results in worksheet cells. For repeatable reporting, Power Query or a PivotTable is often easier to maintain than manually selecting a growing set of ranges each month.

Common Problems and How to Avoid Them

  • Totals are wrong or appear under the wrong category: check whether the ranges were combined by position or by labels. Select the relevant label option and make sure category names are spelled consistently.
  • A value is counted instead of added: verify that the Function setting is Sum. Also check whether amounts are stored as numbers; numeric-looking text may not behave as expected.
  • Some categories are missing: confirm that every source range was added and that its reference includes all intended rows and columns. Include the label row or column if you selected the corresponding label option.
  • The summary does not reflect later edits: a normal consolidation is not automatically a live, expanding summary of any future rows you add. Create links can link the output to source data, but it does not automatically discover new rows outside the selected references. Update the ranges or use a refreshable query or PivotTable workflow.
  • Repeated headings or subtotals distort the result: source ranges should contain detail and labels, not intermediate totals that would be added again. Remove or exclude subtotal rows before consolidating.
  • The source sheets have different layouts: align the structure or use label-based matching where appropriate. If the data is irregular, normalize it into a table with one record per row before summarizing.

Do not confuse consolidation with merging cells. Merge & Center combines the display area of selected cells and can discard the contents of all but the upper-left cell; it does not add rows or summarize values. For data analysis, keep individual values in separate cells and use a summary tool instead.

Frequently Asked Questions

Can Excel consolidate data from multiple worksheets?
Yes. In the Consolidate dialog, add a reference for each source range, including ranges on other worksheets. Select label options if the ranges use headings, then choose the calculation and output location.

Does Consolidate update automatically when I add rows?
Not when the new rows fall outside the references you selected. You may need to expand the references and run the consolidation again. A Power Query result can be refreshed, and a PivotTable can be refreshed, but each still depends on an appropriately defined source.

How do I combine duplicate rows and add their amounts?
Use Data > Data Tools > Consolidate with Sum and the category labels, or use SUMIF/SUMIFS formulas. For a large or changing dataset, a PivotTable or Power Query Group By operation may be easier to maintain.

How do I combine lines of text in one Excel cell?
Use TEXTJOIN with a delimiter, such as a space or comma. For line breaks inside the cell, use CHAR(10) as the delimiter and turn on Wrap Text. This joins text; it does not summarize numeric records.

What is the difference between Consolidate and Append?
Consolidate calculates a summary from selected ranges, such as a sum by product. Append stacks rows from tables into one longer table while keeping the individual records. Choose based on whether you need summarized results or a combined dataset.

Related

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.