How to Pull Data from Another Sheet in Excel

Applies to: Microsoft 365 (Excel for Windows, Excel for Mac, Excel for the web); Excel 2016, 2019, 2021
Article Type: How-To
Last Updated: 2026-08-08

Summary

Your summary sheet needs the total that lives on the sheet next to it, and retyping it means retyping it again every month. One formula fixes that permanently: =Sheet2!A1 shows a cell from another sheet and updates itself whenever that cell changes. This article builds the reference by clicking rather than typing, explains what Excel wrote and why the quote marks appear, covers totalling a range on another sheet, and is honest about what happens when the source lives in a different file.

Before You Start

• A workbook with at least two worksheets. The tabs run along the bottom of the Excel window.
• Edit permission on the workbook. No admin rights required.
• Nothing to install. Everything here works in Excel for Windows, Excel for Mac, and Excel for the web — the cross-workbook steps at the end are desktop territory.

Instructions

The examples use a sheet named Sheet2 holding raw figures and a summary sheet where the results are shown.

1. Build the Reference by Clicking, Not Typing

Let Excel write the syntax. It gets the punctuation right, and it shows you the pattern for next time.

1. Click the cell on your summary sheet where the value should appear. Its address shows in the Name Box at the left end of the formula bar.
2. Type =. The status bar at the bottom-left of the window changes from Ready to Enter, which is Excel telling you it is now building a formula.
3. Click the tab of the other sheet along the bottom of the window. Excel switches to that sheet and keeps the half-finished formula alive — the formula bar still shows =.
4. Click the cell you want. A moving dashed border surrounds it and the formula bar fills in: =Sheet2!A1.
5. Press Enter. Excel jumps back to your summary sheet, and the cell now shows the value from the other sheet.

That value is live. Change the source cell and this one changes with it the moment you press Enter there.

Verification: Click your new cell and read the formula bar. It shows =Sheet2!A1 while the cell itself shows the value. If the cell displays the formula as text instead, the cell was formatted as Text before you typed — on the Home tab, open the format dropdown in the Number group, choose General, then double-click the cell and press Enter to re-enter it.

2. Read the Reference Excel Wrote for You

Three pieces, always in the same order, and knowing them means you can type one directly when clicking is slower.

=Sheet2!A1 breaks down as:

• Sheet2 — the name of the sheet, exactly as it appears on the tab.
• ! — the separator. It reads as "on", so the whole thing is "cell A1 on Sheet2".
• A1 — the cell address on that sheet.

A sheet name containing a space gets wrapped in single quote marks:

• ='Sales Data'!A1 — the quotes tell Excel the name is Sales Data and not Sales.
• The same happens when the name starts with a number or contains punctuation such as - or &.
• Type it without the quotes and Excel returns #NAME?, because it cannot tell where the sheet name stops.

Two behaviours worth knowing before you build anything on top of this:

• Renaming the sheet is safe. Rename Sheet2 to January and every formula pointing at it rewrites itself to =January!A1. You do not have to touch them.
• Deleting the sheet is not. Every formula that pointed at it turns into #REF!, and the original address is gone — Excel does not record what the reference used to be. Press Ctrl + Z straight away and undo restores the sheet and the formulas together.

Tip: To point at a cell on the sheet you are already on, drop the sheet name entirely: =A1. The sheet name is only needed when you are crossing from one sheet to another.

3. Fill the Reference Down a Column

A single reference is rarely the job. Copying it down works the same way as any other formula, and it moves with you.

1. Click the cell holding your =Sheet2!A1 formula.
2. Point at the small square in its bottom-right corner — the fill handle. The cursor becomes a thin black cross.
3. Drag down as far as you need, or double-click the handle to fill to the bottom of the adjacent data.
4. Click any of the filled cells and read the formula bar. Row 2 shows =Sheet2!A2, row 3 shows =Sheet2!A3, and so on down.

The row number moves because the reference is relative — it describes a direction from where the formula sits, not a fixed address. Drag sideways instead and the column letter moves the same way.

To stop it moving, add dollar signs: =Sheet2!$A$1 stays pointed at A1 however far you copy it. Press F4 with the cursor inside the reference to cycle through the combinations. Understanding Absolute and Relative Cell References in Excel explains when each one is the right choice.

Note: Filling down assumes the other sheet's rows line up with yours. If row 5 on your summary sheet does not correspond to row 5 on Sheet2, the values will be wrong while looking entirely plausible. That is the case for a lookup — see step 6.

4. Total a Range on Another Sheet

The same reference syntax works inside a function, so a total can live on a different sheet from the numbers.

1. Click the cell where the total should appear.
2. Type =SUM(.
3. Click the other sheet's tab, then drag across the range you want to add up. The formula bar fills in as you drag: =SUM(Sheet2!B2:B50.
4. Type ) and press Enter. Excel returns to your summary sheet showing the total.

To add the same cell across a run of sheets — twelve monthly tabs, say — use a 3-D reference:

1. Click the cell for the yearly total.
2. Type =SUM(.
3. Click the Jan tab, hold Shift, and click the Dec tab. All the tabs between them highlight together.
4. Click cell B5, type ), and press Enter. The formula reads =SUM(Jan:Dec!B5) and adds cell B5 on every sheet from Jan to Dec inclusive.

A 3-D reference follows the tab order, not the names. Drag a new sheet in between Jan and Dec and its B5 joins the total automatically; drag one outside that range and it drops out.

Verification: Select the source range on the other sheet and read the Sum figure in the status bar at the bottom-right of the window. It should match what your formula returns.

5. Pull a Value from a Different Workbook

This works, and it comes with a cost that is worth understanding before you build a report on it.

1. Open both workbooks in Excel.
2. In the destination workbook, click the cell for the value and type =.
3. Open the View tab, click Switch Windows, and select the source workbook. Excel brings it to the front with your formula still open.
4. Click the cell you want and press Enter. Excel returns to the destination workbook showing the value.
5. Click that cell and read the formula bar. With both files open it reads =[Budget2026.xlsx]Sheet1!$A$1. Close the source file and the same formula redraws with its full path: ='C:\Users\yourname\Documents\[Budget2026.xlsx]Sheet1'!$A$1.

That path is the catch. The link is a set of directions to a file in a specific place, and it holds only as long as the file stays there:

• Move or rename either file and the reference returns #REF! the next time the workbook recalculates.
• Email the destination workbook to a colleague without the source file and they get the last saved values plus an error the first time they refresh.
• Save the source to a different folder in OneDrive or SharePoint and the stored path no longer matches.
• Every time the destination workbook opens, Excel shows This workbook contains links to one or more external sources that could be unsafe with Update and Don't Update buttons. That prompt is what a cross-workbook link looks like from the other end.

If the numbers are final, copy them in as values instead: copy the source range, then right-click the destination and choose the Values icon under Paste Options. Link only when the source genuinely keeps changing.

Note: External references default to absolute ($A$1) when Excel writes them. Remove the dollar signs by hand if you intend to fill the formula down a column.

6. Check or Break Links to Other Workbooks

Before you send a workbook to anyone, find out what it is still connected to.

1. Open the Data tab.
2. In the Queries & Connections group, click Workbook Links. A Workbook Links pane opens down the right side listing every external file this workbook points at, each with its status. In Excel 2021 and earlier the button is Edit Links and it opens a dialog listing the same files.
3. Click a file in the list to see the cells that reference it. A status of Error: Source not found means the file has moved or been renamed.
4. To repoint a broken link, click More Commands — the ... beside the file — and choose Change source, then browse to the file's new location. In the Edit Links dialog the equivalent button is Change Source.
5. To cut a link permanently, click More Commands beside the file and choose Break links, or use Options at the top of the pane and choose Break all links. In the Edit Links dialog, select the file and click Break Link.

Breaking a link replaces every formula that used it with the value it last returned. The numbers stay exactly as they are on screen — what you lose is the connection, and any future update. This cannot be undone, in this session or any other: save a copy of the workbook first if there is a chance you will need to re-link it.

Breaking links is the right call when you are sending a workbook outside your team, archiving a finished report, or clearing the update prompt for good. How to Fix "This Workbook Contains Links to External Sources" in Excel covers hunting down links hiding in named ranges and chart series, which the pane does not always list.

7. Know When You Need a Lookup Instead

A sheet reference points at a fixed address. That is the whole boundary.

=Sheet2!B7 says "show me whatever sits in B7". It has no idea what that value means, and it does not care whether the row moved. Sort the source sheet and the formula keeps pointing at B7, now holding somebody else's number.

The moment your question becomes "find the row where the order number is SO-4471 and give me its total", you have left this territory. That is a lookup:

• How to Use XLOOKUP in Excel — the current function, and the one to learn first if your Excel has it.
• How to Use VLOOKUP in Excel — the older function that every existing workbook is full of.

Both take the same sheet reference you have been building here as their search range, so nothing you have learned is wasted: =XLOOKUP(A2, Sheet2!A:A, Sheet2!C:C) is step 1's syntax doing the work inside a lookup.

Troubleshooting

WARNING

Breaking workbook links cannot be undone once done — Ctrl + Z will not bring them back, and neither will closing without saving in a session where other changes were saved. Every linked formula becomes a fixed value. Save a copy of the workbook under a different name before you break anything you may need to reconnect.

Symptom / Error	Potential Cause	Solution
#NAME?	Sheet name with a space typed without quote marks	Write it as ='Sales Data'!A1, or build the reference by clicking (step 1) so Excel adds the quotes.
#REF! across a column of formulas	The source sheet was deleted	Press Ctrl + Z immediately, before any other edit. Otherwise rebuild the sheet — Excel does not record what the reference pointed at.
The cell shows the formula text, not a value	Cell formatted as Text before typing	Set the cell to General on the Home tab, then double-click it and press Enter to re-enter the formula.
This workbook contains links to one or more external sources that could be unsafe	The workbook references another file	Click Update for current figures or Don't Update to keep the last saved ones. To stop the prompt for good, break the links (step 6).
Values are wrong but no error appears	Filled-down references and source rows do not line up	Check one row by hand against the source sheet. Use a lookup (step 7) rather than a fixed reference when rows can move.
#REF! in a cross-workbook formula	The source file was moved, renamed, or is offline	Open Data > Workbook Links, click More Commands beside the file, and choose Change source to point at its new location.
Workbook Links is missing from the Data tab	Excel 2021 or earlier	The same feature is Edit Links on the Data tab in those versions.

Related Articles

• How to Write Your First Excel Formula
• Understanding Absolute and Relative Cell References in Excel
• How to Use XLOOKUP in Excel
• How to Use VLOOKUP in Excel

Did this solve your problem?