Excel Not Pulling Data From Other Sheets? Fixes

How-to basics · Updated November 18, 2026

If Excel is not pulling data from another sheet, check that the formula uses the correct sheet name and cell reference, then confirm calculation is set to Automatic. For a direct link, enter a formula such as =Sheet2!A1; if the sheet name contains spaces, put it in single quotes, as in ='Quarter 2'!B4.

How cross-sheet references work

A formula can refer to a cell on another worksheet in the same workbook. The basic pattern is an equals sign, the sheet name, an exclamation mark, and the cell address. Excel uses the exclamation mark to separate the worksheet name from the cell reference. When the sheet name has spaces or certain punctuation, Excel encloses it in single quotation marks. Those quotation marks belong around the sheet name, not the cell address.

For example, if the source value is in A1 on a worksheet named Summary, a destination cell can use =Summary!A1. If the worksheet is called West Region, the reference needs the quoted name: ='West Region'!A1. The single quotation marks are required by the sheet name in this case, not by the value being returned.

In Microsoft 365 for Windows, you can create a reference without typing the sheet name manually:

  1. Select the destination cell and type =.
  2. Click the source worksheet tab, then click the source cell.
  3. Press Enter. Excel returns to the destination sheet and completes the reference.

This method is useful when a tab name is long or easy to mistype. On a Mac, the same point-and-click method works. In Excel for the web, select the destination cell, type the equals sign, select the other sheet and cell, and complete the formula in the same way.

Check the reference and worksheet name

A formula may be valid but point to the wrong place. Click the formula cell and inspect the formula bar. Confirm that the worksheet name, exclamation mark, column, and row match the intended source. A small change such as A12 instead of A21 can return a blank or unrelated value without producing an obvious error.

Also check whether the formula refers to the current workbook or to a different file. A reference to a worksheet in the same workbook normally looks like =Inventory!C5. A link to a different workbook includes the workbook name and often a file path. If Excel asks whether to update links when opening the file, the formula may depend on an external workbook rather than another tab in the current one.

When a sheet is renamed, Excel usually updates formulas that refer to it. But a formula typed manually with a misspelled name, or text that merely looks like a formula, will not behave as intended. If Excel displays an error such as #REF!, part of the original reference has likely been deleted or can no longer be resolved. Edit the formula to point to a cell that still exists.

Make sure the cell contains a formula

Sometimes a cell shows an old number or a formula-looking string because Excel is treating its contents as text. Select the cell and look in the formula bar. If the entry begins with an apostrophe, such as '=Sheet2!A1, Excel stores it as text rather than calculating it. A cell formatted as Text before a formula was entered can cause a similar problem.

To correct it, change the cell format to General using Home > Number Format, then edit the cell and press Enter. Changing the format alone may not convert a value that Excel already stored as text; re-entering the formula is often necessary. Check for a leading space before the equals sign as well. A formula must begin with = for Excel to evaluate it.

If you see the formula itself in the grid rather than its result, check whether Show Formulas is on. In Excel for Windows, press Ctrl+` (the grave-accent key, often near the number 1) to toggle formula display. On Mac, use Control+` where that shortcut is available, or check Formulas > Show Formulas. This display setting affects how formulas appear on the sheet; it does not turn them into text.

Check calculation settings and refresh

Excel may have calculation set to Manual, so formulas do not update when source cells change. In Excel for Microsoft 365 on Windows, go to Formulas > Calculation Options and select Automatic. Then recalculate the workbook. Press F9 to calculate open workbooks; Ctrl+Alt+F9 forces a full calculation of all formulas. On a Mac, use Formulas > Calculation Options if the control is available in your Excel version; keyboard shortcuts can differ by keyboard and macOS settings. Excel for the web generally recalculates formulas automatically.

Manual calculation can be useful in very large workbooks, but it can make an otherwise correct cross-sheet formula appear stuck. If a workbook was saved while calculation was manual, its formulas may not refresh until calculation is changed or triggered. After switching back to Automatic, change a source value and see whether the linked cell updates.

Calculation is different from refreshing imported data. A formula that refers to a worksheet cell is calculated by Excel, while data loaded through Power Query or an external connection may need a refresh. If the source sheet itself is stale, refreshing the formula will only return the stale value. Use Data > Refresh All for supported workbook connections, and check the connection’s settings or sign-in requirements if refresh fails.

Diagnose lookup formulas and mismatched data

When a formula is meant to find a matching item on another sheet, an exact lookup is usually more useful than a direct cell reference. For example, with a product code in A2 on the current sheet, product codes in Products!A2:A100, and descriptions in Products!B2:B100, enter =XLOOKUP(A2,Products!A2:A100,Products!B2:B100,"Not found"). If A2 contains P-104 and that code’s description is Desk lamp, the formula returns Desk lamp; if there is no exact match, it returns Not found. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but not in some older perpetual versions.

If the lookup returns Not found even though the item seems to be present, inspect the values on both sheets. Common causes include a trailing space, different punctuation, a number stored as text on one sheet and a number stored numerically on the other, or a code that is not actually identical. Formatting can make two values look the same without making them equal. Check the source cells directly, and consider cleaning or standardizing imported data before relying on a lookup.

For versions without XLOOKUP, a common alternative is INDEX and MATCH. With the same layout—lookup value in A2, codes in Products!A2:A100, and descriptions in Products!B2:B100—use =IFERROR(INDEX(Products!B2:B100,MATCH(A2,Products!A2:A100,0)),"Not found"). The zero requests an exact match. IFERROR replaces an error result with the stated text, which can make a missing match easier to read but can also conceal other errors. Remove IFERROR while diagnosing if you need to see the underlying error.

Understand blanks, errors, and changing ranges

A linked cell that appears blank may be pointing to a genuinely empty source cell. It may also be returning an empty string from a formula on the source sheet. Check the referenced cell itself, not just the destination. If the destination shows zero, the source may be blank and the formula may be returning a numeric zero; Excel’s display options can affect whether zero values are visible, but they do not change the underlying reference.

Errors offer clues. #REF! generally indicates a broken reference. #N/A commonly means a lookup did not find a match. #VALUE! can indicate incompatible or unexpected input, while #NAME? may point to an unrecognized function or a misspelled name. Read the formula and error together rather than replacing every error with a blank immediately.

Another source of confusion is a formula that points to a fixed range which does not include newly added rows. If a lookup covers rows 2 through 100 and the new record is in row 101, Excel cannot find it through that range. Expand the range, or consider converting the source data to an Excel table so that its structured references can expand as rows are added. Tables are created with Insert > Table or Ctrl+T on Windows; on Mac, use Insert > Table or the corresponding Table command. Verify that the table includes the intended headers and records.

Check workbook links, protection, and compatibility

If the data is actually in another workbook, confirm that the source file is available and that its location has not changed. A link can break when a file is moved, renamed, or stored somewhere the current computer cannot access. Excel may show a security prompt or ask whether to update links. Only enable or update links when you trust the source file and expect it to provide the data.

Worksheet protection is not usually a reason a formula cannot read a cell, but it can prevent you from editing the formula or the source value. If you cannot change a cell, check Review > Unprotect Sheet; a password may be required. Workbook protection and file permissions can also limit edits. Do not remove protection or bypass access controls unless you are authorized to do so.

Compatibility matters when a workbook is opened in an older Excel version or another spreadsheet program. A newer function such as XLOOKUP may not calculate in an older version, even when the formula is visible. If the workbook is shared with people using older Excel, use a compatible formula or confirm the recipients’ versions. Excel for the web and Mac share many core formula features with Microsoft 365 for Windows, though some commands, shortcuts, and connected-data options vary.

A reliable troubleshooting order

Work from the simplest causes toward the less common ones. This prevents changing a formula that was correct and makes it easier to identify the actual failure:

  • Click the destination cell and confirm the entry starts with an equals sign and is not stored as text.
  • Check the worksheet name and source address in the formula bar; use the sheet tab to verify the exact name.
  • Visit the source cell and confirm it contains the expected value, not a blank or outdated result.
  • Check Formulas > Calculation Options and recalculate if the result appears stale.
  • For lookup formulas, compare the lookup value with the source values, including spaces, data types, and punctuation.
  • For external data, distinguish a cell formula from a connection that needs Data > Refresh All or an updated workbook link.
  • Check for a range that excludes newly added rows and consider a table when the data grows regularly.

For a simple test, temporarily enter a direct reference to a known, nonblank source cell. If that works, the issue is more likely in the original lookup criteria, range, or external-data process than in cross-sheet references generally. Remove the test formula afterward so it does not become an unintended part of the workbook.

Frequently Asked Questions

Why does Excel show the formula instead of the result?
Check whether the cell is formatted as Text, has an apostrophe or space before the equals sign, or whether Show Formulas is enabled. Set the cell to General, re-enter the formula, and toggle Show Formulas off if the whole sheet is displaying formulas.

How do I reference a sheet with spaces in its name?
Put the sheet name in single quotation marks, followed by an exclamation mark and the cell address. For example, a value in B4 on Quarter 2 is referenced as ='Quarter 2'!B4. Clicking the source cell while building a formula lets Excel insert the correct reference.

Why does my cross-sheet formula not update when the source changes?
Check Formulas > Calculation Options and select Automatic, then recalculate. If the source comes from an external connection rather than a normal cell formula, refresh that connection separately. Also confirm the destination points to the cell that actually changed.

Why does XLOOKUP return Not found when the value is on the other sheet?
The displayed values may differ because of extra spaces, text-versus-number types, punctuation, or a lookup range that excludes the record. Compare the actual source and lookup values and ensure the lookup range includes the row. For exact matches, XLOOKUP searches for an identical value by default.

Can Excel pull data from another workbook as well as another sheet?
Yes. Formulas can link to cells in other workbooks, but the link depends on the source file and its location. If that file moves or access is unavailable, the link may not update. For larger or regularly refreshed datasets, an external-data connection or Power Query may be more manageable than many individual workbook links.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.