The same Excel formula uses different rates on different sheets: inspect name scope

Two finance worksheets can contain identical-looking formulas yet use different assumptions because the same defined name has different scopes. When a formula such as BaseAmount multiplied by SupportRate produces an unexpected result, inspect what SupportRate means on that specific sheet. A workbook-wide search for the name's text does not establish which definition each formula resolves.

Microsoft distinguishes worksheet-scoped names from workbook-scoped names and permits the same name in different scopes. Its guidance explains that a local worksheet name normally takes precedence when it conflicts with a workbook-level name. That can be intentional, but it can also make a copied or revised model use an assumption the reviewer did not expect.

Preserve the affected workbook and the reported result before editing definitions. Capture the sheet, formula and name resolution relevant to the discrepancy. The investigation should establish which assumption the formula actually used and which assumption finance intended, rather than immediately deleting every repeated name because repetition looks untidy.

Consider an illustrative planning workbook with a workbook-scoped SupportRate of 4% and a worksheet-scoped SupportRate of 6% on the RegionalPlan sheet. Assume both worksheets use the same $50,000 base. A formula resolving the global 4% produces $2,000, while the regional sheet's local 6% produces $3,000. The $1,000 difference arises from the referenced assumption, even though the formula text can look the same.

Ask the model owner whether the regional rate is an authorized exception. A local definition is not inherently a defect. The business might deliberately maintain separate assumptions by department, contract or scenario. The issue is whether the model's organization and review evidence make that difference clear to someone relying on the results.

If the regional rate is obsolete, identify the approved replacement and its effective use. Do not infer that the global rate must be correct simply because it applies more broadly. The change should follow the assumption owner's decision, with a record of why the earlier local value no longer belongs in that calculation.

Use Excel's Name Manager to review the relevant name, its scope and its Refers to definition. Microsoft describes that interface as a way to inspect values, references, comments and scope. A name can represent a cell, a constant or a formula, so the reviewer should inspect the actual definition instead of assuming every name points to the same visible assumptions table.

Check whether the reference points to the intended sheet and cell and whether it relies on another workbook. Follow a named formula far enough to understand its dependencies. A correct name can still point to an outdated input, while an apparently unusual definition may be valid for a particular scenario. Record the relationship in terms the model owner can review.

Microsoft notes that Name Manager does not display certain hidden or VBA-defined names. If the ordinary inspection cannot explain the behavior in a workbook using those features, involve its technical owner through the approved review process. Do not claim that an empty filtered list proves the workbook has no other relevant definitions.

Decide whether the assumption should be global, local or deliberately different across scenarios. If several distinct rates are legitimate, descriptive names and a visible assumption register can make the model easier to review. The right arrangement depends on how the workbook is used; a rule banning all local names can damage a template designed around repeated departmental sheets.

Have the workbook owner revise definitions or references in a controlled copy. Check dependent formulas before deleting or renaming a name. A change that fixes one summary may affect other sheets that intentionally used the local value. Keep a short map of the affected outputs and compare them with the approved intended behavior.

Where a formula needs a qualified reference, use the supported syntax and verify its resolution in the actual workbook. Do not rely on a generic textual prefix without testing the result. The goal is an unambiguous link between the formula and its intended assumption, with no new broken references or accidental external dependencies.

If the discrepancy appeared after adding a departmental sheet, importing a template or copying formulas, repeat that operation on a safe copy after the repair. Inspect the resulting names and formula values. A one-time correction to the current sheet is incomplete if the normal monthly maintenance step recreates the confusing scope arrangement.

Include one sheet that should use the common assumption and one with an authorized local exception. Change the common rate by a small fictional amount in the test copy and observe which outputs move. Then test the local rate. This dependency check is more informative than confirming that all current totals happen to match while both rates temporarily share the same value.

Restore the approved assumptions after testing and recalculate the workbook. Confirm the final outputs against independently calculated samples. Preserve the relationship between the correction and any previously distributed report, budget or allocation result. A revised name definition can change financial outputs even though no transaction rows or visible formulas were edited.

Document the naming convention and material local exceptions near the model's operating instructions. Include the owner and source of important assumptions. Someone taking over the workbook should be able to tell whether a regional sheet inherits the central rate or maintains its own approved value without discovering the distinction through a reporting discrepancy.

Keep the repair record focused on the affected definitions, resolved business choice and validated outputs. The completed investigation explains why identical-looking formulas produced different results and makes the intended differences visible. It also avoids flattening legitimate local assumptions merely to make every sheet calculate the same number.