TECHNOLOGY & CERTIFICATION EDITORIAL

How to Link and Use Formulas Across Multiple Sheets in Excel

A workbook with a sheet for each month often starts simply: one summary page, twelve monthly tabs and a total that someone copies by hand. The copying is the problem. As soon as one source amount changes, the summary can become stale. Cross-sheet formulas solve it by referencing the source cells or ranges directly, but only if the workbook has consistent labels and its formulas can be audited.

This guide uses a small, specific model: worksheets named January, February, March, a Summary worksheet, and a separate Customer List lookup sheet. Each monthly sheet has a revenue amount in cell B2. The examples use valid Excel formula syntax, with English function names and commas as argument separators. Excel function names and argument separators can vary with a user’s locale, so the examples use English names and commas.

1. Reference one cell on another worksheet

Place the following formula in Summary!B2 to retrieve the value stored in cell B2 on the January sheet. The exclamation mark divides the sheet name from the cell or range address. It is not a minus sign or a quotation mark.

=January!B2

If the tab contains spaces, quote its entire name in single apostrophes. A source tab called January Sales needs a different reference even though the underlying cell address is still B2.

='January Sales'!B2

An ordinary cell reference returns the value of its source cell, not a copy that must be synchronized manually. However, the displayed result still depends on Excel calculation settings, source availability and valid references. Do not tell a reader that every change is reflected “instantly” under all workbook and calculation states. If automatic calculation is disabled, recalculation behavior needs separate checking.

2. Choose relative or absolute references before copying

A formula such as =January!B2 uses a relative B2 reference. When copied one row down, the address can become B3. When the summary must always refer to the same rate or control cell, lock the appropriate parts using dollar signs. This is the same relative/absolute behavior as on a single worksheet.

=January!$B$2
=January!B$2
=January!$B2

The first holds both row and column; the second holds row 2 while letting the column change; the third holds column B but allows the row to move. Sheet names and cell locking solve different problems, and conflating them produces formulas that appear correct until someone fills them down a report.

A reviewer should inspect one source cell, one copied formula and the result after a controlled source edit before copying to hundreds of cells. If values are financial, confirm number formats and units separately. A formula can be arithmetically valid while mixing net revenue, gross revenue and tax-inclusive amounts.

3. Add several sheets without manual copy-and-paste

For a small fixed set of sources, you can add individual references. The example below adds the three revenue cells and makes the included months obvious in the formula.

=January!B2+February!B2+March!B2

For consecutive worksheets with identical structure, a 3-D reference is more compact. Excel’s SUM function supports a range of sheet names. It includes the sheets between the endpoints in workbook tab order, so moving or inserting sheets can change the result.

=SUM(January:March!B2)

A 3-D formula is not an instruction to find every sheet whose name resembles a month. It uses the physical order of the worksheet tabs between January and March. If someone inserts Archive between the two endpoints and that sheet has a numeric B2, the sum can include that value. This behavior is useful in a carefully controlled template and dangerous in a workbook whose tab order changes unpredictably.

Not every function supports a 3-D reference. Microsoft’s documentation lists supported functions, including SUM, AVERAGE, COUNT, MAX and MIN; do not assume that a lookup formula or arbitrary dynamic array can accept the same January:March! notation. When tabs do not have compatible layouts, a normalized table is often safer than a 3-D calculation.

4. Pull the correct record across sheets with XLOOKUP

A cross-sheet formula is not limited to the same cell address. Suppose Summary!A2 contains a customer ID, and the Customer List tab has IDs in A2:A500 and corresponding account managers in D2:D500. A lookup should match a specific key, not rely on the customer appearing on the same row in both sheets.

=XLOOKUP(A2,'Customer List'!$A$2:$A$500,
         'Customer List'!$D$2:$D$500,"Not found")

The quoted sheet name is important; the absolute ranges keep the lookup table fixed when you copy the formula down. Not found is a visible outcome, not an account-manager value to include in a statistical average. Duplicate customer IDs require their own data-quality rule; a lookup that returns one matching row does not prove uniqueness.

XLOOKUP is available in modern Microsoft 365 and newer Excel editions; teams supporting older Excel installations should verify function availability before distributing the workbook. Some older installations may need INDEX/MATCH or another compatible lookup design. Formulas are not a substitute for choosing a proper source-of-truth table.

5. Compare actual versus budget without concealing missing data

Create tabs named Actuals 2026 and Budget 2026, each with comparable expense values in B2. A simple variance formula exposes whether actual expense differs from budgeted expense, without copying amounts into the Summary tab manually.

='Actuals 2026'!B2-'Budget 2026'!B2

Negative values mean actual expense is below budget under this convention, but a sales-revenue comparison may use the opposite interpretation. Write the sign convention beside the report. Before treating a zero as a clean reconciliation, verify that both source cells contain numeric values and represent the same period and currency.

Avoid wrapping every mismatch in IFERROR(...,0). A removed worksheet and a genuine zero variance are not the same business event. If a report should remain readable during a temporary source outage, display a conspicuous status such as Source unavailable and investigate the cause instead of silently converting it into a successful result.

6. Diagnose broken cross-sheet formulas systematically

The common failures are understandable if you trace the exact dependency. #REF! means a reference has become invalid, often because a source cell or sheet was deleted or a move broke the intended relationship. #NAME? can arise when a function or defined name is not recognized. #N/A from a lookup often means the key was not found, but an unexpected text-versus-number mismatch can be part of the cause.

A normal rename performed inside Excel is not automatically a broken reference: Excel ordinarily updates dependent formulas when a worksheet is renamed. The higher-risk operations are deleting a referenced sheet, moving a 3-D endpoint, changing an external file path or writing a formula as plain text without Excel’s reference management. Explain this distinction rather than warning that any tab rename necessarily destroys a formula.

For a failing total, begin at the output cell and use Excel’s formula auditing tools to trace precedents. Click the referenced source range and compare expected types, units and dates. Inspect manual versus automatic calculation mode if the value looks stale. If a formula relies on an external workbook, check its links, file location, permissions and refresh behavior before blaming the arithmetic.

7. Design sheet structure so formulas remain reviewable

A good workbook does not need hundreds of sheet-specific formulas in every calculation. Give each tab a clear name, reserve a row for units and reporting periods, use consistent column layouts, and protect inputs from accidental edits when appropriate. If monthly tabs all follow the same structure, a concise 3-D summary may be sensible. If rows vary between teams, append them into a normalized data table and aggregate the table instead.

For an auditable month-end workbook, keep raw input separate from transformation and presentation. Annotate the assumptions that most often break: whether data includes tax, whether sources are gross or net, which tabs are included in a 3-D total, and which sheet contains authoritative customer IDs. Maintain a reconciliation total that can be independently compared with source records.

Named ranges can help when a specific amount or range has a stable business meaning, but names should be chosen and scoped carefully. If a named range points to the wrong sheet, its friendly label can hide rather than solve the underlying mistake. Audit both the name and its refers-to address, especially after copying templates.

8. Test the formulas with deliberate changes

Before treating a workbook as reliable, edit one January source number and verify the summary responds as expected under the current calculation settings. Copy a relative reference down a row and confirm it follows the intended address. Move an irrelevant worksheet inside a 3-D span in a disposable test copy and observe whether the aggregate changes. Remove a test lookup key and ensure Not found is visible rather than counted as legitimate data.

Finally, verify what happens when the workbook is opened by colleagues with different Excel versions or regional formula separators. Keep the reference map understandable enough that another analyst can identify a broken source without reverse-engineering the entire workbook. The real benefit of cross-sheet formulas is not merely automatic arithmetic: it is a transparent chain from source record to reported number.

Back to Insights
Explore what matters. Knowledge that goes beyond the exam.
Explore ExamTopics