Md. Abdul Kader
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.
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.
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.
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()
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)
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.
=SHEETS()
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)
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
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.
