Combining data in Excel

Combining data is one of the basic and at the same time most multifaceted tasks in Excel. The term covers completely different operations: from joining text in neighboring cells to assembling data from dozens of files, sheets or tables into one final dataset.

In this article, I’ll go through the main objects you can combine in Excel, the standard formulas, their limitations and the more advanced scenarios.

What you can combine in Excel

Combining applies to different levels of the data structure. Depending on the task, it can be:

• individual cells with text or numbers;
• ranges of cells;
• table columns;
• sheets within one workbook;
• different Excel workbooks;
• CSV files as an external data source.

Each level needs its own tools: formulas, built-in Excel procedures or specialized tools.

The concatenation operator (&)

When it comes to formulas, you can join cells without any function at all: there is a special concatenation operator, the ampersand (&). It’s short, fast and still widely used. Join the first and last names from A1 and B1 with a space:

=A1&" "&B1

This way is especially handy when the formula is simple and you don’t need to process whole ranges.

Excel functions for joining text

Text joining in Excel evolved gradually. New functions didn’t replace the old ones, they were added on top, extending the work with ranges and large data. That’s why several functions coexist in Excel today, each reflecting its own stage of development.

CONCATENATE: the original function

The CONCATENATE function is one of the oldest; it was there back in Excel 2000 and 2003. It solves a simple task: join several separate values (text, numbers or cell contents) into one string.

=CONCATENATE(A1," ",B1)

Its concept reflects the old versions of Excel: each element has to be listed manually, ranges are not supported, and there is no handling of empty cells. As data grew, this model became inconvenient. The function still works and is kept for compatibility with old files.

CONCAT: working with ranges

The next step was CONCAT, introduced in Excel 2019 and Microsoft 365. It simplifies the syntax and accepts whole ranges:

=CONCAT(A1:A5)

Unlike CONCATENATE, it joins the values of a range one after another. But the logic stays as simple as possible: values are glued together directly, without delimiters (“JohnAnnaBobEve”) and without any handling of empty cells. In effect, CONCAT is a technical replacement for CONCATENATE, not a universal tool for analytical tasks.

TEXTJOIN: the modern standard

TEXTJOIN is the latest and most functional tool for joining text. It appeared in the same versions as CONCAT, but solves a much wider range of tasks:

=TEXTJOIN(", ",TRUE,A1:A10)

Here a clear logic of bulk joining appears for the first time: you set the delimiter, say explicitly whether to skip empty cells (TRUE), and pass large ranges or even the results of other formulas. With TRUE, “John, Anna, Bob, Eve” has no double commas where a cell is empty. That’s why TEXTJOIN is considered the main function for joining text in Excel today.

In Excel 365, TEXTJOIN combined with FILTER joins only the values that meet a condition, for example the names of all managers from the East region:

=TEXTJOIN(", ",TRUE,FILTER(B2:B100,A2:A100="East"))

Numbers, dates and line breaks

Joining a date or a formatted number returns its internal value: =”Date: “&A1 gives “Date: 45000” instead of the date. Wrap the value in the TEXT function to keep the format you need:

="Date: "&TEXT(A1,"mm/dd/yyyy")

To put each value on a new line within one cell, use CHAR(10) as the delimiter, =TEXTJOIN(CHAR(10),TRUE,A1:A5), and turn on Wrap Text for the cell.

Why the old functions haven’t disappeared

Excel almost never removes old functions. Millions of files created over decades still use CONCATENATE and &, and removing them would break those files. So the new functions don’t replace the old ones literally; they set the recommended path: from single formulas to ranges and automation.

In practice, the choice looks like this: for simple one-off joins, CONCATENATE or & are still fine, but for tables, lists and bulk data, TEXTJOIN is the best choice.

Merge Cells and its drawbacks

Excel also has the Merge & Center command on the Home tab. Keep in mind that it doesn’t combine data: it merges cells as interface objects. Its main drawbacks:

• only the value of the upper-left cell is kept, the rest of the data is lost;
• you can’t sort and filter the data correctly;
• copying, pasting and working with tables become problematic;
• formulas and automation become less predictable.

That’s why merging cells for formatting is not recommended in working tables. In most cases, you should combine the data, not the cells. If you only need a heading centered over several columns, use Format Cells → Alignment → Horizontal → Center Across Selection: it looks the same, and the cells stay separate.

Combining selected cells with !SEMTools

For tasks where you need to combine the contents of the selected cells quickly and safely, my !SEMTools add-in for Excel has a group of free tools. They combine values in different ways without losing data and without formulas: select the cells, choose the delimiter, done. No TEXTJOIN syntax to remember and no helper column to fill down and delete afterwards.

Combining ranges, columns, sheets and workbooks

When the amount of data grows, combining goes beyond individual cells. Excel and add-ins let you collect data:

• from several ranges horizontally;
• from all columns of a sheet into one;
• from all sheets of a workbook into one sheet;
• from several open Excel workbooks;
• from many CSV files into one final file.

Each scenario has its own guide and tool:

• Merge CSV files into one
• Merge all open workbooks into one
• Combine data from several sheets into one
• Combine all columns into one

Creating hyperlinks as a special case of combining

Creating hyperlinks deserves special attention. In essence, a hyperlink in Excel is the result of combining three elements:

• the link address;
• the display text;
• the screen tip text.

Manually, Excel lets you create only one hyperlink at a time, with the dialog or the function, as described in the article on 10 ways to create a hyperlink in Excel. With large amounts of data, this approach doesn’t scale. !SEMTools creates hyperlinks in bulk, combining texts, addresses and tips into full-fledged links automatically. More in the article on how to bulk create and edit hyperlinks in Excel.

Summary

Combining data in Excel is a whole set of approaches depending on the level of data and the task. Formulas fit pointwise work with text, and TEXTJOIN is the best of them. Merge Cells is only for visual purposes. And for systematic, bulk processing of ranges, sheets, workbooks and files, specialized tools like !SEMTools are the best option.

I hope you found what you were looking for on this page. All combining tools of the add-in are collected in the Join/Split section.