Easily combine multiple worksheets/workbooks into single worksheet/workbook

• Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
• More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
• Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
• Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
• Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
• Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
• More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

Normally we can apply the CONCATENATE function and specify cells from different worksheets one by one to concatenate them in Excel.

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:

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.

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Free Trial Now!

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, says Cell A1, and click Kutools > More > Dynamically Refer to Worksheets. See screenshot:

2. In the opening Fill Worksheets References dialog box,

And now all contents in cell A1 of specified worksheets are extracted and listed in the new worksheet.

3. Select a blank cell you will place the concatenation result in, and enter the formula =CONCATENATE(A1:E1&" ") (A1:E1 is the range with cell contents you extracted from other worksheets) into it, highlight the A1:E1&" " in the formula and press the F9 key, next delete the curly braces {and } in the formula, at last press the Enter key. See screenshot:

And then you will see all cells from specified worksheets are concatenated into one cell at once.

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