SHEET and SHEETS are two new functions that appeared in Excel 2013 and are also present in Excel 2016 and Excel 2019.
The SHEET function returns the index of the selected sheet. It starts counting from 1.
The SHEETS function returns the number of sheets in the reference.
Get the Sheet index
The SHEET function returns the index of a sheet. Take a look at the following example.
B5 – The function returns 1. This is the index of the current worksheet. If you write this formula in Sheet2 (second sheet), it’s going to return 2.
B6 – You have to make a reference to a cell inside a worksheet, not directly to a sheet. That’s why this formula returns an error.
B8 – In the SHEET function, it doesn’t matter to which cell you are going to reference.
Get the number of sheets
The SHEETS function returns the number of sheets in a reference.
B5 – The function SHEETS with no parameters returns all worksheets inside a workbook.
B6 – This time the function returns 3 because it counts sheets between Sheet1 and Sheet3.
Find hidden sheets
Let’s see how the function works when you hide one worksheet.
Between Sheet2 and Sheet4, there is a hidden sheet called Sheet3.
Here are the formulas and results.
B5 – If you write the SHEETS formula with no parameters, it’s going to return all sheets, even the hidden ones. That’s why we have number 4.
B6 – Despite writing the range between Sheet1 and Sheet4 (the hidden Sheet3 is between them), Excel doesn’t count the hidden sheet.
Check for missing data
There is a way to check whether there is a value inside a particular cell inside a sheet.
To do this, you have to use the COUNTA function.
You have the following data for each sheet.
Now, let’s see how our formulas look like.
C3 – The formula displays the number of sheets between Sheet2 and Sheet4.
C4 – the COUNTA formula checks whether there is a data inside B1 for each sheet.
C5 – The difference shows how many values are missing.