Summary

The Excel SHEETS function returns the total number of sheets in a given reference. When no arguments are supplied SHEETS returns the total number of sheets in the workbook.

Purpose 

Get number of sheets in a reference

Return value 

Sheet count

Syntax

=SHEETS([reference])
  • reference - [optional] A valid Excel reference.

How to use 

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.
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.