How to Use SHEETS Function in Excel (5 Useful Examples)

How would you feel if you have numerous worksheets in your Excel workbook? In such a situation, Excel provides a useful function to know the number of working sheets. In this article, I’ll discuss the basics of the SHEETS function also with some basic examples.

The above figure shows the overview of the utilization of the SHEETS function. I’ll show you the uses of the function elaborately.

Introduction to Excel SHEETS Function

The SHEETS function is a built-up function added in the Excel 2013 version, which returns the number of worksheets in a given reference or without reference.

Function Objective

To count the number of sheets

Syntax

SHEETS ([reference])

Arguments Explanation 

Return Value

Sheet count (number)

How to Use Excel SHEETS Function: 5 Useful Examples 

Assuming that we have numerous worksheets for example price of some products at various months and also their total sales. Now, we have to utilize the SHEETS function from different aspects.

1. Getting Number of Sheets without Reference Argument

If you want to get the number of all working sheets, simply, you need not put the reference argument. Just use the following formula.

The SHEETS function returns the number of working sheets without any given reference.

2. Finding Number of Sheets with Reference Argument

Previously we’ve seen finding the number of sheets without reference, but if your requirement is to count a specific sheet with a reference! Like you want to find the sheet number of B4 cell of Price_Jan named sheet.

Then the following formula will be useful

The above picture reveals that the SHEETS function returns the sheet number of B4 cell of the given sheet is 1.

3. Tracking Number of Hidden Sheets Using The SHEETS Function

In some cases, you may hide specific sheets in the workbook for your need like the following way.

But the SHEETS function allows counting the total number of worksheets though those are not visible to you.

Just input the formula as the SHEETS function with a reference or without reference.

4. Finding The Number of Sheets While Dealing 3D Reference

Again, if you need to count the number of working sheets for 3D reference e.g. A5 cells from Price_Jan to Sales named working sheet.

In that case, the formula will be-

As you see the formula counts the number of sheets between two provided sheets (including those sheets as well), there are 5 sheets and the formula returned 5.

5. Determining The Number of Sheets Between Two Sheets 

Last but not least, if you want to determine the number of sheets between two specific sheets. For example, the number of working sheets between Price_Jan and Sales named sheet.

For this, you may use the following formula.

You need to deduct 2 from the above formula for adjustment.

Here, the function counts the number of sheets as 3 i.e. Price_Feb, Price_Mar & Price_Apr.

Things to Remember

1. If the reference is not a valid value, the SHEETS function shows #REF! Error.

2. The SHEETS function is not available in the Object Model (OM) because similar functionality is already included in the Model.

Download Practice Workbook

Conclusion

This is how you can count the number of sheets in an Excel workbook using the SHEETS function. I strongly believe that this article will articulate calculation methods. If you have any queries or suggestions, please let me know in the comments section below.

<< Go Back to Excel Functions | Learn Excel