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 2013. In version 2010 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.
SHEET vs SHEETS: 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)| A | B | C | |
|---|---|---|---|
| 1 | Formula (on Report) | Result | Why |
| 2 | =SHEET() | 2=SHEET() | Report is the 2nd tab |
| 3 | =SHEET(Data!A1) | 1=SHEET(Data!A1) | Data is the 1st tab |
| 4 | =SHEET(Summary!A1) | 4=SHEET(Summary!A1) | hidden Archive (3rd) is counted too |
| 5 | =SHEET("Summary") | 4=SHEET("Summary") | a sheet name as text works too |
| 6 | =SHEET("Nope") | #N/A=SHEET("Nope") | no sheet with this name |
The number is the tab position from the left, counting hidden sheets. Move a tab and the result changes.
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)| A | B | C | |
|---|---|---|---|
| 1 | Formula | Result | Why |
| 2 | =SHEETS() | 4=SHEETS() | all sheets, including hidden |
| 3 | =SHEETS(Data:Archive!A1) | 3=SHEETS(Data:Archive!A1) | Data, Report, Archive |
| 4 | =SHEETS(Report!A1) | 1=SHEETS(Report!A1) | a reference to one sheet |
| 5 | =SHEETS()-SHEET() | 2=SHEETS()-SHEET() | sheets after the current one |
A 3D reference like Data:Archive!A1 counts every sheet between the two tabs, inclusive. SHEETS()−SHEET() tells how many tabs come after the current one.
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.