SHEETS Function

The Excel SHEETS function will retrieve the total number of sheets in a reference. It is categorized under the Information functions and was first introduced in Excel 2013. When evaluating this function, all the worksheets, such as non-hidden, protected worksheet, hidden, and macro, must be countable. It is merely different from the SHEET function.

It is helpful to manage a large project with a complex workbook, understand the complete structure of the worksheets, monitor the hidden worksheets, and utilize dynamic referencing when merging with the INDIRECT function.

Compatibility

This advanced Excel function is compatible with the following versions of MS-Excel −

Excel for Microsoft 365
Excel for Microsoft 365 for Mac
Excel for the web
Excel 2024
Excel 2024 for Mac
Excel 2021
Excel 2021 for Mac
Excel 2019
Excel 2016
Excel 2013

Syntax

The syntax of the SHEETS function is as follows −

=SHEETS(reference)

Arguments

You can use the following arguments with the SHEETS function −

Argument	Description	Required / Optional
Reference	It indicates a reference where its corresponding sheet name is to be retrieved. If you skipped the reference, the SHEETS function returns the total number of sheets in the specific workbook.	Optional

Argument

Description

Required / Optional

Reference

It indicates a reference where its corresponding sheet name is to be retrieved.

If you skipped the reference, the SHEETS function returns the total number of sheets in the specific workbook.

Optional

Points to Remember

Multiple worksheets (visible, macro, chart, or dialog sheets and hidden) would be included in the Sheets function.
If the recently deleted worksheet name or invalid reference is provided in the reference, then this function will retrieve the #REF! Error.
If the reference argument specifies an incorrect name of the worksheet, then the SHEETS function will return the #N/A error.
This function is not supported in the Object Model as the same functionality is included in the Object model.

Examples of SHEETS Function

Practice the following examples to learn the use of the SHEETS function in Excel.

Example 1: How to Retrieve the Numbers of Sheets?

Through the SHEETS () function, we can extract the number of worksheets in the specific workbook, including macro, hidden, and protected sheets.

First, double-click on the B3 cell and enter the formula =SHEETS().

After that, hit the Enter tab.

Therefore, the SHEETS() returns a result of 5, which means 3 worksheets are active, 1 is hidden, and 1 is protected.

Example 2: Extracting the Worksheets that is no longer Exist

If the recently deleted worksheet name or invalid reference is provided in the reference, then this function will retrieve the #REF! Error.

Solution

You enter the formula =SHEETS(Sheet1:W1) in the C2 cell in the "Sheet5".

Extracting Worksheets that is no longer Exist1

After that, press the Enter tab to get the sheet number of the referenced cell.

Extracting Worksheets that is no longer Exist2

However, if you delete the Sheet 1 worksheet, then this function will return the #REF error like this.

Extracting Worksheets that is no longer Exist3

Example 3: Use the INDIRECT, and SHEETS() Functions to Extract the Specific Value from the Last Worksheet

Solution

First, navigate to the Sheet8 and enter the formula =INDIRECT("Sheet" & SHEETS() & "!b5") in the B2 cell. In this expression, the value of cell B5 of the last worksheet, Sheet 6, is extracted.

Use the INDIRECT, and SHEETS() Functions1

After that, hit the Enter tab.

Use the INDIRECT, and SHEETS() Functions2

Download Practice Sheet

You can download and use the sample data sheet to practice the SHEETS function.

Save or share this article