Sheet Function

Purpose

Return value

Syntax

=SHEET([value])

• value - [optional] The value to check.

Using the SHEET function

The SHEET function returns the index number of a sheet in Excel. You can use the SHEET function to get a numeric index that represents the order of sheets in an Excel workbook, starting with 1 on the left and ending with N on the right, where N is the total number of sheets in the workbook. The SHEET function includes hidden sheets in the numbering sequence.

The SHEET function takes one argument, value , which should be a reference, a named range , or an Excel Table . Value is optional. When value is omitted, SHEET will return a numeric index for the current sheet (i.e. the sheet the formula exists in).

Examples

For example, in a workbook with Sheet1, Sheet2, and Sheet3 running left to right:

=SHEET(Sheet1!A1) // returns 1
=SHEET(Sheet2!A1) // returns 2
=SHEET(Sheet3!A1) // returns 3

If Sheet2 is dragged all the way to the left, a reference to A1 on Sheet2 will return 1:

=SHEET(Sheet2!A1) // returns 1

SHEET can report the sheet number for a cell reference, named range, or Excel Table . For example, if a table called “Table1” exists on the third sheet in a workbook the SHEET function will return 3:

=SHEET(Table1) // returns 3

Notes

• If the value argument is omitted, SHEET will return the index of the current sheet.
• SHEET includes hidden sheets in the numbering sequence.
• SHEET reports the index of a sheet. The SHEETS function reports the number of sheets .

Purpose

Return value

Syntax

=SHEETS([reference])

• reference - [optional] A valid Excel reference.

Using the SHEETS function

The SHEETS function returns the total number of sheets in a given reference. SHEETS takes one argument, reference , which should be a cell reference, or a 3D reference . When no references are supplied SHEETS returns the total number of sheets in the workbook. The SHEETS function includes hidden sheets.

Examples

For example, in a workbook that contains 5 sheets, the following formula will return 5:

=SHEETS()

SHEETS can be used to report the sheet count in 3D references as well. For example, in a workbook with three sheets (Sheet1 through Sheet3) in numeric order, the formulas below return results as shown:

=SHEETS(Sheet1:Sheet1!A1) // returns 1
=SHEETS(Sheet1:Sheet2!A1) // returns 2
=SHEETS(Sheet1:Sheet3!A1) // returns 3

Notes

• If the reference is omitted, SHEETS returns the total sheets in a workbook.
• SHEETS includes hidden sheets in the count.
• SHEETS will report sheet count in 3D references.
• The SHEET function returns the index of a sheet. SHEETS returns a count.