How to concatenate cells from another sheet/different sheets in Excel?

Normally we can apply the CONCATENATE function and specify cells from different worksheets one by one to concatenate them in Excel. This method is straightforward for a small number of sheets or when you only need to combine a few specific cells. However, as the number of worksheets increases, maintaining or editing the formula can become cumbersome.

Select a blank cell you will put the concatenation result into, and enter the formula =CONCATENATE(Tea!A1," ",Coffee!A1," ",'Soft Drink'!A1," ",Milk!A1," ",Water!A1) (A1 is the cell whose content you will concatenate, Tea/Coffee/Soft Drink/Milk/Water are worksheet names containing the specific cell you need to concatenate) into it, and press the Enter key.

And then you will see contents of the specified cells from different sheets are concatenated into one cell. See screenshot:

Limitation: This approach requires you to edit the formula manually for each sheet and cell. Therefore, it is best for small-scale concatenations or quick, one-off tasks. For more complex scenarios, consider the alternative methods below.

Excel 2019 and later versions provide the TEXTJOIN function, which efficiently concatenates a range or array of values and allows you to specify a custom delimiter between each value. This method is especially practical for newer Excel users who want a simple formula-based solution capable of ignoring empty cells, which reduces redundant delimiters.

1. Click into the blank cell where you want the merged result (for example, D1), and enter the following formula:

=TEXTJOIN(" ",TRUE,Tea!A1,Coffee!A1,'Soft Drink'!A1,Milk!A1,Water!A1)

This formula joins the values of cell A1 from the five sheets (Tea, Coffee, Soft Drink, Milk, Water) separated by a space (" "). The second argument TRUE tells Excel to ignore any empty cells automatically.

2. Press Enter to apply the formula. Your selected cell will display the concatenated result. If you need to concatenate more or fewer sheets, adjust the references in the formula to match your needs.

• Change the delimiter inside the first quotation marks—for example, use ", " for a comma and space, "-" for hyphens, etc.
• If you want to reference different cell addresses from each sheet, update them accordingly within the formula.
• For larger numbers of sheets, or variable address locations, consider using the below solution.

Advantage: Very concise, easy to adjust delimiters, and robust in ignoring blanks; requires no additional add-ins or macro activation in compatible Excel versions.

Limitations: Not available in versions prior to Excel 2019. Cell addresses must be listed explicitly, so it is best suited for a moderate (not extremely large) number of sheets.

If there are numerous worksheets whose cells you need to concatenate into one cell, the above formula may be quite tedious. Here we recommend Kutools for Excel’s Dynamically Refer to Worksheets utility to ease your concatenation work in Excel. This tool streamlines the process by automatically referencing designated cells across multiple worksheets, greatly reducing manual effort and minimizing errors, especially when handling dozens of sheets or monthly consolidated reports.

1. Create a new worksheet in Excel, and in the new worksheet select the cell which has the same cell address as those cells you will concatenate from other worksheets (for example, Cell A1). Then click Kutools > More > Dynamically Refer to Worksheets. See screenshot:

2. In the opening Fill Worksheets References dialog box, complete the following:

Now, all contents in cell A1 of the specified worksheets are extracted and listed side by side in the new worksheet.

3. Select a blank cell where you want the concatenation result (for example, F1), and enter the formula =CONCATENATE(A1:E1&" ") (A1:E1 is the range with cell contents extracted from other worksheets). Highlight A1:E1&" " in the formula bar and press F9; this converts the references into their actual values. Then delete the curly braces {} in the formula, and finally press Enter to finish. See screenshot:

All specified cells from different worksheets are now concatenated into one cell at once.

• This solution is suitable for scenarios where you often need to extract and merge the same cell from multiple worksheets, or when dealing with large numbers of sheets.
• The Dynamically Refer to Worksheets utility is especially valuable for maintaining and auditing workbook references since new sheets can be selected with just a few clicks, and the references update accordingly.

Concatenate keeping number/date formatting in Excel

Concatenate range with adding/including quotation marks in Excel

Concatenate range/cells with comma, space, line break in Excel

Concatenate first letters of names/cells in Excel