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.

Excel-SHEETS-Function-Overview

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


Download Practice Workbook


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.

SHEET-Function

Function Objective

To count the number of sheets

Syntax

SHEETS ([reference])

Arguments Explanation 

Argument Required/Optional Explanation
reference Optional The value for which you’ll get the number of sheets

Return Value

Sheet count (number)


How to Use Excel SHEETS Function 

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.

Dataset-for-SHEET-Function


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.

=SHEETS()

Getting-the-Number-of-Sheets-without-Reference-Argument

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

=SHEETS(Price_Jan!B4)

Finding-the-Number-of-Sheets-with-Reference-Argument

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.

Hide-Sheets

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.

=SHEETS()

Tracking-the-Number-of-Hidden-Sheets-Using-the-SHEET-Function


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-

=SHEETS(Price_Jan:Sales!A5)

Finding-the-Number-of-Sheets-While-Dealing-3D-Reference

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.

=SHEETS(Price_Jan:Sales!B5)-2

Determining-The-Number-of-Sheets-Between-Two-Sheets

For adjustment, you need to deduct 2 from the above formula.

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.


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.

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo