SHEETS

What it does

SHEETS([reference])

SHEETS([reference]) returns how many sheets a reference spans, or — with the argument omitted — how many sheets the containing workbook has.

Microsoft's description is one line: "Returns the number of sheets in a reference." The argument description supplies the omitted case: "If Reference is omitted, SHEETS returns the number of sheets in the workbook that contains the function."

The quoted one-line description is Microsoft's text, from its support article https://support.microsoft.com/en-us/office/sheets-function-770515eb-e1e8-45ce-8066-b557e5e4b80b.

So the function has two quite different jobs depending on whether you give it an argument, and the two are worth separating:

• SHEETS() — a workbook census. How many sheets exist.
• SHEETS(ref) — a span measurement. How many sheets this reference reaches across, which is 1 for an ordinary reference and larger only for a three-dimensional reference such as Sheet1:Sheet3!A1 (the value universe calls that a three-dimensional reference shape). SHEETS(Sheet1:Sheet3!A1) is 3.

That second job is the one the function was really added for. Before dynamic arrays, a 3-D reference was the only way a single argument could reach across sheets, and SHEETS is how a formula finds out that it did.

The counting rule is documented and is the same as SHEET's: "SHEETS includes all worksheets (visible, hidden, or very hidden) in addition to all other sheet types (macro, chart, or dialog sheets)." A workbook showing three tabs can report 5.

Arguments

reference — optional, at most one. The published signature is SHEETS([reference]).

Unlike SHEET, this function takes only a reference. It does not accept a sheet name as text: there is no "how many sheets does the sheet called X span" question to ask. That asymmetry between the two siblings is easy to miss and is the source of most SHEETS errors.

The argument is a reference position, not a value position — the live reference must survive argument preparation, because the answer depends on the reference's shape, not on the values it designates.

The omitted form is a workbook-level query, not a caller-aware one in the coordinate sense: it does not depend on which cell the formula is in, only on which workbook. That is why the declared volatility is NonVolatile — the answer changes only when a sheet is added or removed, which is a structural edit.

Try it

Quirks

Result and edge cases

Returns a Number — a count, minimum 1 for any valid reference.

• An ordinary reference is 1, whether it is a single cell, an area, or a structured reference; none of those spans sheets.
• A 3-D reference returns the span size, counting every sheet between the endpoints in tab order — which means hidden and chart sheets caught inside the span are counted too, by the documented counting rule. A Sheet1:Sheet3 span in a workbook with a hidden sheet between them is 3, not 2, and the hidden sheet's cells participate in whatever the 3-D reference is doing. That is a real workbook hazard, and SHEETS is how you detect it.
• A multi-area reference spanning two sheets ((Sheet1!A1, Sheet2!A1)) — not addressed by the documentation, not established here. Whether it counts 2, counts 1, or errors is open.
• An external-workbook reference — whose sheet count is being returned, and does it differ when the source workbook is closed? Not addressed, not established.
• SHEETS is not available in the object model. Microsoft says so explicitly: "SHEETS is not available in the Object Model (OM) because the Object Model already includes similar functionality." Automation code should read Workbook.Sheets.Count.

Errors

• #REF! — documented: "If reference is not a valid value, SHEETS returns the #REF! error value."

Note the difference from SHEET, which has two documented errors because it has two argument forms. SHEETS takes only references, so it has only the reference error. There is no #N/A path, because there is no name lookup to fail.

#NAME? would arise before SHEETS runs, from an unresolvable defined name, in reference resolution (coercion and lifting).

Arity failure (two or more arguments) is expected to be refused at formula entry rather than evaluated (the call pipeline).

How to implement

Notes for implementers

1. Count every sheet type, including hidden, very hidden, chart, macro and dialog sheets. An implementation that enumerates visible worksheets will under-report, silently and workbook-dependently.
2. The argument's shape is the subject. SHEETS must inspect the reference's sheet span, not dereference it. A values-only path cannot implement this function at all.
3. Do not accept a text sheet name. SHEET does; SHEETS does not. Adding it for symmetry would be a divergence.
4. SHEETS() and SHEETS(ref) are different queries — a workbook census and a span measurement. Model them separately rather than passing a null reference into one code path.
5. Decide the multi-area and external-reference cases explicitly and record them as decisions awaiting evidence.

Rust code

4 sheets_fn "SHEETS" (0, 1) [R "reference"] |s, a, sv| { sheets(s, a, sv) }

fn sheets(site: &Site<'_>, a: &[CalcValue], sv: &mut EvalServices<'_>) -> Step<CalcValue> {
    if !a.is_empty() && !a[0].is_missing() {
        // The sheets the reference spans: one, or each sheet of a 3-D reference (L3, dc-m3u;
        // Excel 16.0, 2026-10-23: `=SHEETS(Ja:Ma!A1)` is 3).
        if let CoreRef::Reference(reference) = a[0].core_ref() {
            let mut sheets: Vec<_> = reference.areas().iter().map(|area| area.sheet()).collect();
            sheets.sort();
            sheets.dedup();
            return site.number(sheets.len() as f64);
        }
        // A value that is no reference is #N/A, an error value the result (Excel 16.0.20430,
        // 2026-10-25, dc-6au: `=SHEETS(1)`, `=SHEETS("a")`, `=SHEETS({1,2})` and
        // `=SHEETS(A1:A3*1)` are #N/A, `=SHEETS(#REF!)` #REF!).
        return match a[0].core_ref() {
            CoreRef::Error(code) => fail(code),
            _ => fail(WorksheetErrorCode::NA),
        };
    }
    match sv.host().and_then(|host| host.sheet_count()) {
        Some(count) => site.number(f64::from(count)),
        None => Err(site.not_modelled(None)),
    }
}

No implementation has been published with a Handbook test suite for this function.

Version differences

No comparison between specific Excel builds is recorded here yet. This does not establish that all builds behave alike.

Alternatives

Relationships

• SHEET([reference]) is the index to SHEETS' count. Same module, same 2013 vintage, same documented counting rule, routinely confused. Mnemonic: singular returns a position, plural returns a quantity. Note also that SHEET accepts a text sheet name and SHEETS does not.
• INFO("numfile") counts worksheets across all open workbooks; SHEETS() counts within the containing workbook. Two different numbers that are equal only when one file is open.
• AREAS(reference) counts the areas in a multi-area reference the way SHEETS counts the sheets in a 3-D one. They are the two cardinality functions over reference shape, they share an implementation module, and together they characterise a reference's geometry.
• ROWS and COLUMNS complete the set: ROWS, COLUMNS, SHEETS and AREAS are the four functions that measure a reference instead of reading it.
• CELL, INFO, ISFORMULA, FORMULATEXT are the other host-backed Information functions.

Named variants

No named variants are recorded yet.