SHEET & SHEETS Workbook Index Formulas
Learn how to count and index worksheets in Excel using SHEET and SHEETS. Audit multi-tab financial models, build dynamic navigation, and track hidden sheets.
Large corporate budgeting files often include dozens of worksheets—one for each regional entity, product division, or expense category. Model builders frequently lose track of whether sheets have been rearranged by team members or if hidden sheets are skewing calculations.
Excel provides two simple formulas to track workbook structure: SHEET and SHEETS.
Quick Comparison: SHEET vs SHEETS
| Formula | What It Measures | Default Behavior (No Arguments) | Best Fit in Real Work |
|---|---|---|---|
SHEET([value]) |
The 1-based index position of a worksheet (left-to-right) | Returns the index number of the current active sheet | Auditing sheet sequence and verifying report ordering. |
SHEETS([reference]) |
Total count of worksheets in a workbook or 3D range | Counts all worksheets across the entire file | Detecting forgotten hidden sheets and building dynamic headers. |
Auditing Hidden and Very Hidden Sheets
A key audit benefit of SHEETS() is that it counts all worksheets, including sheets that are manually hidden or set to "VeryHidden" via macro code. If you count 8 visible tabs on your screen but SHEETS() returns 11, you immediately know three hidden sheets exist in the file.
Building Dynamic Navigation Headers
You can combine both formulas to display a dynamic page progress counter in your report title:
="Sheet " & SHEET() & " of " & SHEETS()As you add, delete, or rearrange tabs, the counter updates automatically without manual maintenance.
Hands-on Practice Playground
Practice indexing sheets and counting tabs using the structure audit table below. Complete each objective to verify your understanding.
In cell C2, find the index position of the active worksheet using SHEET.
In cell C2, determine the numeric tab position of the active worksheet using the SHEET formula with no arguments.
In cell C3, count the total worksheets in the workbook using SHEETS.
In cell C3, count the total number of worksheets in the entire workbook using the SHEETS formula with no arguments.
In cell C4, construct a dynamic sheet navigation string.
In cell C4, combine text with SHEET and SHEETS to output a label showing sheet progress: "Sheet " & SHEET() & " of " & SHEETS().
Share This Lesson
Spread the word with your peers and challenge your friends