# 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.

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 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 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?