Auditing large financial workbooks containing dozens of tabs, monthly summaries, and departmental reports can become chaotic when sheets are added, moved, or deleted. Formulas that rely on hardcoded worksheet references or manual page counting are fragile and prone to breaking during reorganisations. To simplify workbook auditing and dynamic reporting, Microsoft Excel includes two native information functions: SHEET and SHEETS.
Introduced to standardise worksheet indexing without relying on complex VBA macros, the SHEET and SHEETS functions return the numerical index of a given tab and the total count of sheets in a workbook or 3D reference range.
Syntax and Behaviour of the SHEET Function
The SHEET function returns the sequential position index (1-based) of a referenced worksheet within the current workbook:
=SHEET([value])Parameters and Arguments
[value](Optional): The name of a worksheet (enclosed in quotation marks), a cell reference on another worksheet (e.g.Summary!A1), or an Excel defined name.- Omitted Argument: If you omit the argument entirely (
=SHEET()), Excel returns the index number of the sheet containing the formula itself.
Syntax and Behaviour of the SHEETS Function
The SHEETS function calculates the total number of worksheets within a workbook or across a 3D span:
=SHEETS([reference])Parameters and Arguments
[reference](Optional): A reference to a sheet or 3D block of sheets (e.g.Jan:Dec!A1).- Omitted Argument: If omitted (
=SHEETS()), Excel returns the total count of all visible, hidden, and very hidden worksheets in the current workbook.
Practical Applications and Real-World Examples
Combining SHEET and SHEETS with other Excel functions unlocks practical auditing and navigation capabilities.
1. Dynamic “Sheet X of Y” Page Headers
When generating printable reports or cover pages, you can display an automated page tracking indicator that updates automatically whenever tabs are added or rearranged:
="Sheet " & SHEET() & " of " & SHEETS()If the active tab is the 3rd sheet in a 12-tab workbook, Excel displays: Sheet 3 of 12.
2. Checking If a Specific Worksheet Precedes or Follows Another
To audit whether an auditing review sheet (e.g. AuditCheck) sits after your raw data tab (RawData):
=IF(SHEET(AuditCheck!A1) > SHEET(RawData!A1), "Correct Order", "Review Hierarchy")This validation ensures standard operating procedures are maintained before executing consolidation models.
3. Counting Sheets Across a 3D Departmental Range
If you maintain a multi-entity budget where individual country tabs are sandwiched between a FirstDept and LastDept boundary tab, calculate how many entities are actively included in the 3D consolidation:
=SHEETS(FirstDept:LastDept!A1)If you drag three new departmental tabs between those boundary sheets, the formula instantly updates its tally without manual formula edits.
4. Detecting Hidden Worksheets
You can verify whether a workbook contains hidden tabs without writing a single line of VBA. By comparing the visible sheet index of the final tab against the total count:
=IF(SHEET() < SHEETS(), "Warning: Additional or Hidden Sheets Exist", "Final Sheet Reached")SHEET vs SHEETS Function Comparison Table
| Comparison Factor | SHEET Function | SHEETS Function |
|---|---|---|
| Output Type | Single integer (Index position) | Single integer (Total count) |
| Omitted Argument Output | Index of current active sheet | Total sheets in the entire workbook |
| Accepts Text Sheet Names | Yes (e.g. =SHEET("Q1_Report")) |
No (Requires range or 3D reference) |
| Includes Hidden Sheets | Yes (Hidden sheets retain their numerical index) | Yes (Hidden sheets are tallied in total count) |
| Compatibility | Excel 2013, 2016, 2019, 2021, and Microsoft 365 | Excel 2013, 2016, 2019, 2021, and Microsoft 365 |
Common Errors and Troubleshooting
#REF!Error: Occurs in theSHEETfunction if the sheet name provided in quotation marks does not exist in the workbook (e.g.=SHEET("NonExistentTab")).#VALUE!Error: Occurs inSHEETSif an invalid object type or malformed syntax is supplied as the reference.- Sheet Renaming Resilience: If you pass a cell reference (such as
Summary!A1) rather than a hardcoded text string ("Summary"), Excel updates the reference automatically if the sheet is later renamed by another user.