If an Excel formula is not working between sheets, first check that it points to the right tab and cell, then confirm the cell is formatted as a formula and Excel is set to calculate automatically. A reference to another sheet in the same workbook uses an exclamation mark between the sheet name and cell address; sheet names containing spaces need single quotation marks.
How a formula refers to another sheet
In a formula, the sheet name identifies the tab and the cell address identifies the location on that tab. For example, with a value in B2 on a worksheet tab named Orders, a formula in another sheet can refer directly to it. The layout assumed here is: the source value is in Orders!B2, and the formula goes in the destination cell.
=Orders!B2
If the tab name has spaces or punctuation, enclose it in single quotation marks. The layout assumed here is: the source value is in B2 on a tab named Monthly Sales, and the formula is entered on another tab.
='Monthly Sales'!B2
Excel often adds the quotation marks automatically when you build a reference by clicking the source tab and cell while editing a formula. A reference between tabs in the same workbook is different from a link to another workbook. An external workbook reference includes the workbook name and may also include its file path, so moving or renaming that file can break the link.
Check the reference and sheet name first
A formula can be syntactically valid and still return the wrong result because it points to the wrong cell, row, or sheet. Click the formula cell and inspect the formula bar. Confirm that the tab name is spelled exactly as it appears, that the intended cell or range is referenced, and that a copied formula has not shifted its references unexpectedly.
When you copy a formula down or across, Excel normally adjusts relative references. If every row should use one fixed source cell, lock its address with dollar signs. The layout assumed here is: the value to retrieve is in B2 on a tab named Rates, and you plan to copy the formula to other cells while keeping the source fixed.
='Rates'!$B$2
If a sheet name itself contains an apostrophe, Excel represents that apostrophe twice inside the quoted name. For example, with a value in A1 on the tab named O’Brien, the reference is written as follows; the formula is entered on another worksheet.
='O''Brien'!A1
A quick way to avoid typing a reference incorrectly is to type an equals sign in the destination cell, click the source tab, select the source cell, and press Enter. If this creates a working formula, compare its structure with the formula that failed.
When Excel shows the formula instead of its result
If the cell displays the formula text rather than a calculated value, the formula may be stored as text. Select the cell and look at Home > Number Format. If it is set to Text, change it to General, then edit the cell and confirm the formula again. Merely changing the format does not always convert an existing text string into a live formula; re-entering it is the important step.
Also check whether the entry begins with an apostrophe before the equals sign. An apostrophe tells Excel to treat what follows as text. Remove it if it was entered by mistake. Another possibility is that Show Formulas is turned on, in which case Excel displays formulas throughout the worksheet rather than their results. Toggle it off from Formulas > Show Formulas, or use the same command to check the setting.
Look for a leading space before the equals sign as well. A cell containing a space followed by a formula-like string may be treated as text. The formula bar helps distinguish a displayed formula from a calculated result, especially when the cell is too narrow to show the full contents.
Check calculation settings and refresh results
Excel can be set to calculate workbooks automatically or manually. In manual mode, a changed source cell may not immediately update formulas that depend on it. On Windows, check Formulas > Calculation Options > Automatic. If the workbook is intentionally using manual calculation, use Formulas > Calculate Now to recalculate the open workbook, or Formulas > Calculate Sheet to recalculate the active sheet.
Calculation settings can affect how an entire workbook behaves, so do not switch a carefully designed model to Automatic without considering its size and purpose. Large workbooks with many formulas may calculate slowly. If a value remains stale after recalculation, confirm that the source cell actually changed and that the destination formula points to that source rather than a similar-looking cell.
Excel for Mac has calculation controls on the Formulas tab as well, although the ribbon layout can vary by version. Excel for the web recalculates supported formulas in the browser, but some workbook features and external connections behave differently from desktop Excel. If a workbook appears correct on one platform but not another, compare the calculation setting and confirm that both versions support the formula or data connection being used.
Diagnose common errors and unexpected results
The displayed result often points to the cause. A zero may be a genuine value in the referenced cell, or it may result from a formula pointing to a blank cell. A blank-looking result can also be produced by a formula that returns an empty string, so check the formula itself rather than assuming the reference is broken.
- #REF! usually means a reference is invalid, often because a referenced row, column, or worksheet was deleted or moved in a way Excel could not preserve. Edit the formula to point to a valid location.
- #NAME? can indicate a misspelled function or an unrecognized name. Check spelling and confirm that a named range used by the formula exists.
- #VALUE! often indicates that an argument has the wrong type of value. Check whether the source data contains text where the formula expects a number.
- #N/A commonly appears when a lookup formula cannot find a match. Compare the lookup value with the source values, including spaces and differences between numbers stored as text and real numbers.
A lookup across sheets can fail even when the displayed labels look identical. One value may contain a trailing space, or one cell may contain text such as 0042 while another contains the number 42. Check the underlying values and formats, not just their appearance. Also ensure that a formula’s range includes the row where the expected match is located.
Build a reliable cross-sheet total
Direct references are useful for individual cells, but a function may be clearer when you need to total or summarize data from another tab. For example, SUM adds a range on another worksheet. The layout assumed here is: values to add are in B2:B20 on a tab named Orders, and the total formula is entered on a different sheet.
=SUM(Orders!B2:B20)
When a worksheet name contains spaces, quote it inside the function. The layout assumed here is: values to add are in B2:B20 on Monthly Sales, and the formula is entered on another tab.
=SUM('Monthly Sales'!B2:B20)
For a conditional total, SUMIF can use a criteria range on one sheet and a sum range on that same sheet. The layout assumed here is: categories are in A2:A20 and amounts are in B2:B20 on Orders; the destination sheet contains the criterion, such as East, in A2.
=SUMIF(Orders!A2:A20,A2,Orders!B2:B20)
The formula returns the total of the amounts whose category matches the destination sheet’s A2 value. Check that the criteria and sum ranges cover corresponding rows. If the categories or amounts are moved, update both ranges so they still describe the intended records.
External links and workbook changes
If the source is in a different workbook, Excel creates an external link rather than a simple sheet-to-sheet reference. That link may include the source file name and location. If the source file is closed, moved, renamed, or unavailable on another computer, Excel may be unable to update the value. A cached result can sometimes remain visible even though the link is no longer current.
To inspect links in desktop Excel, look for Data > Workbook Links when that command is available for the workbook. The label or availability can vary with Excel version and workbook contents. Confirm that the source file is accessible and that you trust it before updating links. Do not enable links from an unknown workbook simply to remove a warning. If you only need a one-time snapshot, copying the source results and pasting values removes the live connection, but those values will no longer update automatically.
A practical troubleshooting order
Work through checks in a consistent order so you do not change several settings without knowing what fixed the problem:
- Select the formula cell and read the formula bar. Verify the sheet name, exclamation mark, cell address, and range.
- Check the tab’s spelling and whether its name needs quotation marks. If in doubt, rebuild the reference by selecting the source cell while editing.
- Check Home > Number Format for Text, remove any accidental leading apostrophe or space, and re-enter the formula.
- Confirm that Formulas > Show Formulas is off if formulas appear in cells instead of results.
- Check Formulas > Calculation Options and recalculate if the workbook is set to manual mode.
- Identify any error code, then inspect the source data, formula ranges, and external links that could explain it.
After each correction, compare the result with the source cell or calculate a small example by hand. This helps distinguish a formula that now works from one that merely stopped showing an obvious error.
Frequently Asked Questions
Why does my formula work on one sheet but not another?
It may refer to a different cell after being copied, use an incorrect sheet name, or encounter text or blank values in the new location. Compare the two formulas in the formula bar and check the source cells and calculation settings.
Do I need quotation marks around every sheet name?
No. They are required when the name contains spaces or certain punctuation, and they are allowed for ordinary names too. Quoting a sheet name is a safe, clear choice when you are unsure.
Why is my cross-sheet formula returning zero?
The referenced cell may actually be zero or blank, or the formula may point to the wrong location. Check the source cell directly and verify the sheet name and address. Also inspect whether a formula is configured to return zero for the conditions it encounters.
Why does Excel show the formula instead of calculating it?
The cell may be formatted as Text, the entry may begin with an apostrophe or space, or Show Formulas may be on. Change the format to General and re-enter the formula, then check the Show Formulas command.
Will a formula keep working if I rename a sheet?
Excel generally updates formulas that refer to a renamed worksheet in the same workbook. If you see an error afterward, inspect the formula for an invalid reference and check whether it points to a different workbook or a deleted sheet.
Related
- Excel File Too Large for No Reason? Find the Hidden Data
- Excel File Large for No Reason? Causes and Fixes to Try
- Excel Essential Keyboard Shortcuts for Everyday Work
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel Date Not Recognized? How to Fix Dates Stored as TextIf Excel does not recognize a date, the cell often contains text that merely looks like a date,…
- Excel Not Working After Update? Fixes That Usually HelpIf Excel stopped opening, freezes, or behaves differently after an update, first restart Windows and check whether the…
- VLOOKUP Not Working with General Format? Fixes That WorkYes—VLOOKUP can work with cells set to General; that format alone does not prevent a lookup. If the…
- Excel Filter Button Not Working? Fix Common CausesIf the Excel filter button is missing, greyed out, or not filtering the rows you expect, check that…
- Excel Sort Dates Wrong? Fix the Order and Find the CauseWhen Excel sorts dates into the wrong order, the usual cause is that some entries are stored as…
- Excel Cell Drop-Down Options: Create, Edit, and FixTo add Excel cell drop-down options in Microsoft 365 for Windows, select the cell, choose Data > Data…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.