How to link and combine data from multiple sheets in Excel

Spreadsheet tools · Published September 27, 2026

Reference cells on other sheets and workbooks, total the same cell across many sheets, pull matching rows from another sheet, and keep links from breaking.

The short answer

To use a value from another sheet, type =, click the other sheet's tab, click the cell and press Enter. Excel writes a reference such as =Jan!B4, and it updates whenever the source cell changes. To total the same cell across many sheets, use a 3D reference such as =SUM(Jan:Mar!B4). To pull matching rows from another sheet, use a lookup.

Links to other workbooks work too, but they break when files are moved or renamed. Keep data you link to in the same workbook when you can.

Reference a cell on another sheet

1. Select the cell where you want the value.
2. Type = (or the start of a formula, such as =SUM().
3. Click the tab of the other sheet, then click the cell or select the range.
4. Press Enter. Excel returns you to the first sheet.

The result is the sheet name, an exclamation point and the cell: =Jan!B4 means "cell B4 on the sheet named Jan". If the sheet name contains spaces or other characters that aren't letters, Excel wraps it in single quotes:

='Q1 Sales'!B4

If you type references by hand, add the quotes yourself; without them, Excel can't read the name. If you later rename a sheet, Excel updates every reference to it automatically.

Example 1: a summary sheet with 3D references

Say you have one sheet per month (Jan, Feb, Mar), all with the same layout, and each has its total in B4. On a Summary sheet you can pick up each month and the quarter total.

=SUM(Jan:Mar!B4)

Jan:Mar means every sheet from Jan to Mar in tab order, and B4 is the cell to add on each. To build it by clicking: type =SUM(, click the Jan tab, hold Shift and click the Mar tab, click B4, then press Enter.

3D references work with SUM, AVERAGE, COUNT, MAX, MIN and a few other functions, but not with lookups or SUMIF.

Example 2: pull matching rows from another sheet

When the rows on two sheets aren't in the same order, a plain reference like =Staff!B2 picks up the wrong person. Look the value up by an ID instead. Here a Staff sheet lists each employee's ID, name and department, and an Hours sheet has only IDs.

=VLOOKUP(A2,Staff!$A$2:$C$5,2,FALSE)

=INDEX(Staff!$C$2:$C$5,MATCH(A2,Staff!$A$2:$A$5,0))

The VLOOKUP finds the ID from A2 in the first column of the Staff table and returns column 2 (the name); FALSE means exact match. The INDEX/MATCH formula returns the department. The dollar signs keep the table fixed as you copy the formulas down.

Wrap the formula in IFNA to show a message instead, for example =IFNA(VLOOKUP(A5,Staff!$A$2:$C$5,2,FALSE),"Not on staff list"). In Microsoft 365 and Excel 2021 or later, XLOOKUP does the same job. See VLOOKUP vs XLOOKUP vs INDEX/MATCH for the details, and how to compare two lists in Excel to find which IDs are missing.

Link to another workbook

1. Open both workbooks.
2. In the destination, select a cell and type =.
3. Switch to the source workbook, click the sheet and cell, and press Enter.

While the source is open, the link looks like =[Budget.xlsx]Annual!$C$10. When it's closed, Excel shows the full path, such as ='C:\Reports\[Budget.xlsx]Annual'!$C$10. Clicking creates absolute references (with dollar signs); remove them if you want to copy the formula to other cells.

When you open a file with links, Excel may show a security warning. Select Enable Content only if you trust the file. To see and manage links, go to Data › Queries & Connections › Workbook Links in current versions, or Data › Edit Links in older ones. From there you can refresh values, change the source file, or break the links. Breaking a link replaces the formulas with their current values, and Microsoft warns it can't be undone, so save a copy first.

INDIRECT: build the sheet name from a cell

INDIRECT turns text into a reference. With a sheet name in A2, this returns B4 from that sheet:

=INDIRECT("'"&A2&"'!B4")

The single quotes are joined around the name so sheet names with spaces work. In Example 1, this gives the same totals as =Jan!B4. Use it with care:

• The reference is text, so Excel doesn't update it when a sheet is renamed.
• It recalculates after every change in the workbook, which can slow large files.
• For another workbook, Microsoft says it returns #REF! unless that file is open.

Combine sheets with Consolidate or Power Query

Data › Consolidate totals several ranges into one table. Choose a function (such as Sum), select each range and choose Add, check Top row and/or Left column so rows are matched by their labels, then select OK. Microsoft notes it isn't available in Excel for the web. To stack many sheets or files into one table that refreshes, Power Query (Data › Get Data) is the better fit; it's beyond this guide.

Keep links from breaking

• Renaming a sheet is safe for normal references; moving or renaming a linked workbook isn't.
• Store linked files in a shared folder that everyone opens by the same path.
• Link by ID with a lookup, not by position, when rows may be sorted or inserted.
• If you see #REF!, the sheet or cells the formula pointed to were deleted. If Excel rejects a formula you typed, check the sheet name's spelling and its single quotes.

To send someone the results of a single sheet without any links, export it as CSV. A CSV holds one sheet, and formulas are saved as their results. Filecon's Excel to CSV does this in your browser, and you pick which sheet to convert.

In short

Click to create references like =Jan!B4, use =SUM(Jan:Mar!B4) to total the same cell across sheets, and look rows up by ID when the order differs. Keep linked data in one workbook where possible, use INDIRECT sparingly, and manage external links from Workbook Links or Edit Links.