The SHEET and SHEETS functions are used for working with sheets in an Excel workbook. They belong to the information functions category and can be useful when creating dynamic reports, sheet references, and workbook structure checks.
Both functions appeared in Excel 2016. In version 2013 and earlier, it was more difficult to find out the number of sheets in a workbook and the number of the active sheet. Read the article: how to find out the number of sheets in an Excel workbook.
Brief differences
- SHEET returns the number of a specific sheet.
- SHEETS returns the number of sheets in a workbook or in a specified sheet range.
SHEET function
The SHEET function returns the sheet number to which the reference relates. If the argument is not specified, the number of the current sheet is returned.
Syntax
=SHEET([reference])- reference — optional argument. This is a reference to a cell or range on the desired sheet. If not specified, the active sheet is used.
Examples
=SHEET()Will return the number of the current sheet in the workbook.
=SHEET(Sheet3!A1)Will return the number of the sheet named “Sheet3”.
SHEETS function
The SHEETS function returns the total number of sheets in an Excel workbook.
Syntax
=SHEETS([reference])- reference — optional argument. If a range including multiple sheets is specified, the function will return the number of these sheets. If not specified, the total number of sheets in the workbook is returned.
Examples
=SHEETS()Will return the total number of sheets in the current workbook.
=SHEETS(Sheet1:Sheet3!A1)Will return the number of sheets between “Sheet1” and “Sheet3”, including these sheets themselves.
Like the article? Help its author! Buy !SEMTools, it has lots of useful instruments to process text data.
This post is also available in RU.