Home > Formulas > Lookup > SHEET and SHEETS SHEET and SHEETS Functions The SHEET and SHEETS functions were added in Excel 2013. The SHEETS function counts all the sheets in a reference, and the SHEET function returns the sheet number for a reference. Author: Debra Dalgleish

Introduction to SHEET and SHEETS Functions

Among the new functions in Microsoft Excel 2013 are SHEET and SHEETS.

• SHEETS function counts all the sheets in a reference
• SHEET function returns the sheet number for a reference
• SHEET and SHEETS are in the Information category for Excel functions

To see how you can use the new SHEET and SHEETS functions in Excel 2013, please watch this short video tutorial.

Using the Functions

You can use the SHEET and SHEETS formulas to do some troubleshooting in an Excel spreadsheet.

SHEETS

• Check for missing values
• Notice that sheets are hidden

SHEET

• Show sheet number in list of sheets, to spot missing numbers
• Calculate sheet number at top of each sheet, to identify gaps for hidden sheets

SHEETS Function

• Get the Number of Sheets
• Check for Missing Values
• Notice Hidden Sheets
• SHEETS Limitation

Get the Number of Sheets

The SHEETS function tells you how many sheets are in a reference. In the screen shot below, this formula:

=SHEETS(Dept01:Dept03!$I$4)

will return 3, as the number of sheets in that 3D reference.

Check for Missing Values

In the formula above, the SHEETS function told us how many sheets were in the reference. We could use the COUNT or COUNTA function to see if there is a value in each sheet.

=COUNTA(Dept01:Dept03!$I$4)

Then, subtract the count from the number of sheets, to see if there are any missing values.

In the worksheet shown above, there are 3 sheets in the reference, but only 2 entries. You can check the sheets, to find the missing value.

Notice Hidden Sheets

In the SHEETS function, if you omit the reference, it tells you how many sheets are in the workbook -- visible, hidden or very hidden sheets.

=SHEETS()

In the screen shot below, the result is 8, but you can only see 4 sheet tabs.

If the SHEETS result isn't the same as the number of visible tabs, you'll know that it's time to look for hidden sheets. This could help if you or your co-workers are sometimes hiding sheets, and forget they're hidden!

SHEETS Limitations

Unfortunately, the SHEET function doesn't work with multiple references, such as:

=SHEETS(Sheet1!B3,Sheet2!B3,Sheet4!B3)

And, you can't use INDIRECT to create the reference, based on sheet names in a cell, because INDIRECT doesn't support 3D references.

SHEET Function

• Check the Sheet Number
• See the Active Sheet Number
• Show Sheet Numbers in a List
• SHEET Limitation

Check the Sheet Number

The SHEET function tells you the sheet number for a reference. In the screen shot below, this formula:

=SHEET(Dept02!I6)

returns a 3, as the number for the Dept02 sheet.

It's the 3rd sheet tab, so you can tell that none of the sheets before it are hidden.

The SHEET function can also return the sheet number for a named range or a named Excel table

See the Active Sheet Number

In the SHEET function, if you omit the reference, it tells you the number of the active sheet.

=SHEET()

In the screen shot below, the result is 5, but the 3rd sheet tab is active. That indicates there are 2 hidden sheets before the active sheet.

Show Sheet Numbers in a List

The SHEET function takes a regular reference -- not a 3D reference like the SHEETS function. So, you can use INDIRECT with SHEET, to show the sheet numbers in a list of sheet names.

The screen shot below shows a menu sheet, with all the sheet names in the workbook. In the adjacent column, this formula calculates the sheet name.

=SHEET(INDIRECT("'" & $B3 & "'!A1"))

Now, I can sort the list in alphabetical order, or numerical order, and check for hidden sheets.

SHEET Limitations

The SHEET function works as promised, but it would be nice to have a function that lets you get information about a sheet, based on its number. Or, perhaps we could use a sheet number to build a reference for our spreadsheets, just as we can use the row number and column number.

That way, we could refer to the sheet that is -1 or +1 away from the active sheet.

Get the Sample File

Get the zipped sample SHEET and SHEETS Functions file. The workbook also uses the FORMULATEXT function, to show the formulas in the example cells. These functions only work in Excel 2013 or later, so you'll see errors if you open the file in an earlier version.

More Function Tutorials

SUM Function

VLOOKUP Function

INDEX function and MATCH Function

Count Functions

INDIRECT Function

MATCH Function Video Tutorial

VLOOKUP Function Video Tutorial