SHEETS Function Excel

The SHEETS function Excel is an inbuilt Information function. It accepts a reference and returns the total number of worksheets in the specified reference as an integer.

Users can combine the SHEETS function with other Excel functions, such as INDIRECT, to create dynamically referencing formulas. Such a formula references the sheet based on the count of sheets the SHEETS function returns and performs evaluations across the specific worksheet in a workbook.

For example, the image below shows a workbook containing worksheets.

The aim is to determine the total number of sheets in the current workbook and display the output in cell B2.

Then, we can implement the built in SHEETS function Excel in the target cell to obtain the required count.

In the above built in SHEETS function Excel example, we do not supply any argument value to the SHEETS() in the target cell.

It might appear that we will face the scenario of the SHEETS function Excel not working. However, when we apply the SHEETS() without an argument value in a cell, it returns the total number of sheets in the specific workbook.

Hence, we obtain the required total count of sheets in the current workbook, which is 3.

• The SHEETS function Excel accepts a reference and returns the total number of worksheets in the reference as an integer.
• Users can apply the SHEETS function for counting the sheets in a workbook to keep a check on hidden and very hidden worksheets. Also, the function helps create formulas involving dynamic referencing and 3D references.
• The SHEETS function in Excel accepts one optional argument value, reference.
• While we can apply the SHEETS function in Excel as is, using it with other inbuilt functions, such as INDIRECT and SUM, yields dynamic results.

The SHEETS Excel function syntax is the following:

Where,

• reference: The reference for which we aim to determine the total number of worksheets it includes. The argument value can be a reference to a cell or range in one or a range of worksheets.

The SHEETS() argument is optional, and if omitted, the function will return the total number of the sheets in the workbook containing the function.

Furthermore, considering the following aspects will help avoid the scenario of the SHEETS function Excel not working.

• The SHEETS function counts all sheets, whether visible, hidden, or very hidden, along with other worksheet types such as chart, macro, or dialog sheets.
• If the SHEETS() argument value is invalid, the function output will be the #REF! error value.
• We cannot find SHEETS() in the Object Model, but it contains a similar functionality.

We do not have a SHEETS function Excel VBA. However, we can utilize the SHEETS Excel function in the following two ways:

1. Access the function from the Excel ribbon.
2. Enter the function into the worksheet manually.

Method #1 - Access The Function From The Excel Ribbon

Choose a cell to show the output - The Formulas tab - The More Functions down arrow - The Information function group right arrow - SHEETS.

The Function Arguments window appears, where we must update the SHEETS function argument value in the Reference field.

Next, click OK in the Function Arguments window to obtain the SHEETS() return value as an integer in the target cell.

Method #2 - Enter The Function Into The Worksheet Manually

1. Select a cell to show the output.
2. Type =SHEETS( in the cell.
3. Enter the argument as a reference and close the brackets.
4. Press Enter to view the SHEETS() output in the chosen cell.

The following SHEETS function examples will help us utilize the function effectively.

Example #1

The following dataset contains descriptions asking to count and determine the total sheets based on the cell references in the specified worksheets. Assume the target cells are B2 and B3.

There is no SHEETS function Excel VBA to determine the required counts. However, the following steps show how to achieve the required output by applying the SHEETS function directly or from the Formulas tab in the target cells.

Step 1: Choose cell B2 and enter the SHEETS().

=SHEETS(

Step 2: Click the tab of the worksheet specified in the corresponding column A cell description.

The specific worksheet becomes the active sheet, and the SHEETS() formula shows the sheet name. Next, choose the cell specified in the corresponding column A cell description, cell B11, which the formula will show after the sheet name and exclamation mark.

=SHEETS('Students_Physics_Test Scores'!B11

Step 3: Close the bracket in the SHEETS() and press Enter to execute the formula in the target cell.

=SHEETS('Students_Physics_Test Scores'!B11)

Since the cited reference is to a cell in one worksheet, the SHEETS function Excel returns the required count of total worksheets as 1.

Step 4: Choose cell B3 and iterate steps 1 to 3 to enter the SHEETS(). However, the reference argument value will be the reference to the cell in the worksheet mentioned in the corresponding column A cell.

=SHEETS('Students_Chemistry_Test Scores'!A10)

Example #2

The given dataset shows a description stating to count the total worksheets based on the 3-D Excel reference to the specified cell range in the cited worksheets. Assume the target cell is B2.

Then, we can use the SHEETS function in the target cell to get the desired output.

Step 1: Choose cell B2 and enter the SHEETS().

=SHEETS(

Step 2: Click the tab of the first worksheet specified in the description, Jan2023_Sales.

Next, press Shift and click the tab of the last worksheet specified in the description, Mar2023_Sales.

This step selects all the worksheets between the chosen sheets, and the SHEETS() formula shows the chosen sheet range.

=SHEETS(Jan2023_Sales:Mar2023_Sales!

Step 3: The first worksheet we chose in Step 2 will remain as the active worksheet, where we must select the required cell range according to the description in the source dataset.

=SHEETS(Jan2023_Sales:Mar2023_Sales!B2:B5

Step 4: Close the bracket in the formula and press Enter to execute it in the target cell.

=SHEETS(Jan2023_Sales:Mar2023_Sales!B2:B5)

Firstly, we enter the SHEETS() name.

Steps 2 and 3 show the method to create the required 3D cell reference.

So, the SHEETS() returns the total worksheets in the specified 3D reference to a range of three worksheets, 3.

Example #3

The following image shows a worksheet, Expenses_Consolidated Data, containing a dataset to display the total expenses in 2022 for analysis purposes.

The raw expenses data is in the worksheet Sheet12, which we must add and update as the total expenses in cell B2 of the active sheet, Expenses_Consolidated Data.

Then, here is how to use the SHEETS() in the target cell to obtain the required amount.

Step 1: Choose cell B2 and enter the SUM function containing the INDIRECT Excel function and SHEETS function.

=SUM(INDIRECT("Sheet"&SHEETS()&"!B2:B13"))

First, the SHEETS function Excel returns the total number of sheets in the current worksheet, 12. Next, the INDIRECT() returns the reference specified by the given string, which is the range B2:B13 in Sheet12.

Finally, the SUM() adds the expenses data in the range B2:B13 in Sheet12 to return the total expenses required for the year 2022, $5,011, in the target cell.

Let us see the differences between the SHEETS() and SHEET().

• Definition

The SHEETS() output is the total number of worksheets in the specified reference. Contrarily, the SHEET() output is the worksheet number of the referenced worksheet.

• Argument Value

The SHEETS() argument value is reference. It can be a reference to a cell or range in one or a range of worksheets.

The SHEET() argument value is value that can be a sheet name or a reference to a cell in a specific worksheet.

• Omitted Argument Value

If the argument value in the SHEETS function is omitted, the function will return the total number of worksheets in the current workbook containing the function. On the other hand, if we apply the SHEET Excel function without the argument value, the function will return the number of the worksheet that contains the function.

• The SHEETS function Excel considers visible, hidden, very hidden, macro, plot, and dialog sheets in the current workbook while returning the output.
• When the supplied argument value to the SHEETS function is invalid, the function output will be the #REF! error value.
• If multiple references are supplied as the argument value to the SHEETS function, Excel will display a warning message stating that we entered too many arguments.

This article must be helpful to understand the SHEETS Function Excel, with its formula and examples. You can download the template here to use it instantly.

This has been a guide to What Is SHEETS Function Excel. Here we learn the SHEETS formula syntax & how to use it in Excel with examples & points to remember. You can learn more from the following articles –

• XMATCH In Google Sheets
• XLOOKUP
• Search For Text In Excel