Concat Excel Different Sheets: Formulas and Examples

To concatenate values from different Excel sheets, use CONCAT with a reference to each sheet, such as =CONCAT(Jan!A2,Feb!A2). Add a text argument such as a space or comma if you want a separator; if you mean stacking rows from several sheets into one list, use a different method such as VSTACK.

What “concat different sheets” can mean

People use “concat” to describe two different spreadsheet jobs. The first is joining text or values from corresponding cells—for example, combining a first name on one tab with a last name on another. The second is appending entire tables so that the rows from several tabs form one longer table. These tasks call for different formulas, so decide which result you need before choosing a method.

For joining corresponding cells, the formula refers to each sheet and cell explicitly. The result is a single text string in the cell containing the formula. For appending tables, the result should retain the separate rows and columns; a dynamic-array function such as VSTACK can do that in supported Excel versions. Neither operation merges worksheets or changes the original source cells.

Join matching cells with CONCAT

In Microsoft Excel for Microsoft 365 on Windows, enter a CONCAT formula in the cell where you want the combined result. Assume the Jan sheet has a first name in A2 and the Feb sheet has a surname in A2. The formula below joins those two values with one space:

=CONCAT(Jan!A2," ",Feb!A2)

If Jan!A2 contains Ava and Feb!A2 contains Chen, the result is Ava Chen. The text argument between the cell references supplies the space; without it, the result would be AvaChen. You can add more references and text arguments in the order you want the output to appear.

CONCAT is useful when the number and order of items are known. It combines text, numbers, and cell values into a text result, but it does not add punctuation or separators automatically. If you need a dash, label, or other literal text, put it in quotation marks as an argument.

Reference sheet names correctly

A sheet name without spaces or certain punctuation can appear directly before an exclamation mark, as in Jan!A2. If the tab name contains spaces, Excel requires single quotation marks around the name. For example, if one tab is named North Region and the other is named South Region, use this formula:

With Ava in North Region!A2 and Chen in South Region!A2, this returns Ava Chen:

=CONCAT('North Region'!A2," ",'South Region'!A2)

The single quotation marks belong around each sheet name; the double quotation marks mark the literal space being added. A simple way to avoid typing a reference incorrectly is to begin a formula, click the source sheet tab, select the required cell, and then continue editing the formula. Excel inserts the sheet reference for you. This also works when the sheet name is difficult to type.

Add separators and handle blank cells

For a sequence of values, TEXTJOIN is often easier to maintain than placing a separator between every pair of references. It accepts a delimiter, an option to ignore empty cells, and the values or references to join. The next example assumes B2 on Jan contains 12, B2 on Feb contains 15, and B2 on Mar contains 9:

=TEXTJOIN(", ",TRUE,Jan!B2,Feb!B2,Mar!B2)

The result is 12, 15, 9. The delimiter is a comma followed by a space, and TRUE tells TEXTJOIN to skip empty cells among the listed references. If Feb!B2 is blank, the result is 12, 9 rather than 12, , 9. If you need a placeholder for a missing value, do not ignore blanks; instead, decide what text should represent a missing entry and include it explicitly.

TEXTJOIN can also combine a range on one sheet, but a separate sheet reference is still needed for each distinct range you want to include. For a short, fixed set of cells, listing each reference makes the intended order clear. For many sheets or changing sheet names, a formula that explicitly names every tab can become cumbersome; consider organizing the source data in one table or using Power Query instead.

Fill the formula down for many rows

If each sheet has matching records on the same row, enter the formula in the first result row and copy it down. For example, with a first name in A2:A20 on Jan and a surname in A2:A20 on Feb, put this in the result sheet’s B2:

=CONCAT(Jan!A2," ",Feb!A2)

With Ava in Jan!A2 and Chen in Feb!A2, B2 displays Ava Chen. When you fill the formula down one row, Excel adjusts the cell references to A3 on both tabs, then A4, and so on. This works as intended only if the rows on the two source sheets refer to corresponding people or records. If the sheets are sorted differently, matching row numbers can join unrelated records; use a lookup based on a shared ID instead of assuming the rows line up.

On Windows, drag the fill handle at the bottom-right corner of the selected formula cell, or select a range and press Ctrl+D to fill down. On Mac, the comparable shortcut is Control+D. You can also copy and paste the formula. Check a few rows after filling to confirm that the references moved as expected and that the source rows still match.

Append tables from sheets with VSTACK

If the goal is one longer list rather than one combined text value, VSTACK places arrays one below another while keeping their columns aligned. It is available in Microsoft 365 and Excel 2024, but not in every older Excel edition. Suppose the workbook contains two Excel tables, named JanSales and FebSales, each with three columns and two data rows. Enter this formula in an empty area:

=VSTACK(JanSales,FebSales)

The result is four rows by three columns: the two data rows from JanSales followed by the two from FebSales. When table rows are added later, structured table references can include the expanded table data. Ensure both tables use the same column order and compatible types; VSTACK aligns by position, not by matching column headings. The formula spills into neighboring cells, so those cells must be empty or Excel will report a spill problem.

Table names such as JanSales refer to the table’s data body, not its header row. If you use ordinary cell ranges instead, include the headers only once. For example, stacking each sheet’s full range including its header would repeat the headings in the middle of the result. Also avoid selecting much larger ranges than necessary, since blank rows included in the source ranges can appear in the stacked output.

Choose the method that fits the job

Use CONCAT when you need a single value assembled from a few known cells, such as a name, code, or label.
Use TEXTJOIN when you need a delimiter between several values or want Excel to skip empty references.
Use VSTACK when the goal is to append records from similarly shaped tables into a vertical list. It requires Microsoft 365 or Excel 2024.
Use Power Query when you regularly combine many sheets or files, need repeatable import and cleanup steps, or want to refresh a consolidated table without maintaining a long formula.

These options do not all produce the same kind of output. CONCAT and TEXTJOIN return a single text value, while VSTACK returns a multi-cell array. A common misconception is that concatenating two sheets automatically merges their tables. It does not: joining two cell values creates text, and appending rows requires a table-combining approach.

Common problems and fixes

The sheet reference shows an error: check the tab spelling and use single quotation marks around names containing spaces. A reference to a renamed or deleted sheet may also need updating.
The result has no spaces or punctuation: CONCAT adds only the values you give it. Add a quoted delimiter such as a space, comma, or hyphen as an argument.
The wrong records are joined: confirm that the source rows correspond. Sorting one sheet independently can break row-by-row alignment even though the formula itself is correct.
VSTACK returns a spill error: clear cells in the area where the output needs to appear, and check that the formula is not inside an Excel table that blocks spilling.
A function name is not recognized: CONCAT is supported in modern Excel, while VSTACK requires Microsoft 365 or Excel 2024. Older versions may require a different approach.

Formulas recalculate when their referenced cells change, but a text result does not preserve the separate source fields as editable components. If you need a permanent snapshot, copy the results and use Paste Special > Values. Keep the original data or a backup if you may need to revise the joined values later.

Excel desktop, Mac, and web notes

The formulas shown work in Excel for Microsoft 365 on Windows, and the same basic worksheet references and functions are available in current Microsoft 365 editions for Mac and Excel for the web. Ribbon layout and some keyboard shortcuts differ between platforms, but entering a formula in a cell works the same way. VSTACK availability depends on the Excel version, so an older installation may not recognize it even if the workbook was created elsewhere.

Formula argument separators can also depend on regional settings. The examples use commas, as requested for standard English-language Excel syntax. If a computer is configured to use another list separator, Excel may require that local separator when entering a formula. That setting does not change what the formula is intended to do.

Frequently Asked Questions

Can I concatenate cells from three or more Excel sheets?
Yes. Add a reference for each sheet to CONCAT, or list the references in TEXTJOIN if you want a delimiter. Confirm that the referenced cells contain the values you intend to combine.

How do I combine sheets with a space between values?
Include a quoted space between the sheet references, for example =CONCAT(Jan!A2," ",Feb!A2). If the sheet names contain spaces, wrap each sheet name in single quotation marks.

Does CONCAT combine whole worksheets?
No. CONCAT creates a text result from the values or references supplied to it. To append rows from tables, use VSTACK in a supported version or use a data-combination tool such as Power Query.

Can I combine sheets when their records are in a different order?
Not safely by joining the same row numbers. First match records using a shared identifier, then retrieve the appropriate values. Otherwise, a formula can return a valid-looking result made from unrelated rows.

Why does VSTACK not work in my Excel?
VSTACK requires Microsoft 365 or Excel 2024. If you use an older version, the function may not be available; Power Query or a manually consolidated range may be a better fit.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.