SHEET and SHEETS Functions in Excel 2013

I’ve been experimenting with the new SHEET and SHEETS functions in Excel 2013, to see how they could be used.

• The SHEETS function counts all the sheets in a reference
• The SHEET function returns the sheet number for a reference

You can see a demo of these functions in the video at the end of this post.

How Do You Use These Functions?

Have you found any practical uses for these functions? I came up with a few formulas that let you do some troubleshooting.

• Use SHEETS(ref) to check for missing entries in a 3D reference
• Use SHEETS() to spot hidden sheets
• Use SHEET(ref) sheet numbers in list of sheets
• Use SHEET() to show sheet number at top of sheet, identify gaps

You can see a couple of these examples below, and the rest are on the SHEET and SHEETS functions page.

SHEETS – Check for Missing Values

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.

Use COUNTA Function

Next, use the 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.

SHEET – Identify Gaps

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 SHEET formula result is 5, but the 3rd sheet tab is active.

That indicates there are 2 hidden sheets before the active sheet.

SHEETS and SHEET Limitations

I found a few limitations when experimenting with these functions:

• The SHEETS function can only take a 3D reference, so you can’t select multiple cells, and find the number of sheets that they’re on.
• INDIRECT doesn’t support 3D references, so you can’t built a SHEETS reference based on sheet names and cell addresses.
• 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.
• It would also be useful to use a sheet number to build a reference, 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.

Do you have any other SHEETS or SHEET function suggestions?

Download the Sample File

There’s a sample file that you can download, from the SHEET and SHEETS functions page on my Contextures website.

The workbook also uses the FORMULATEXT function, to show the sample formulas.

Remember, these functions only work in Excel 2013 or later, so you’ll see errors if you open the file in an earlier version.

Video: SHEET and SHEETS Functions in Excel 2013

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

You can get the sample file from the SHEET and SHEETS functions page on my Contextures website

___________________.