The Excel SHEET function lets you check the sheet number. You can either go to any sheet and type the formula or specify any cell from the target sheet to know the index number.
For example, suppose that you are an HR Manager responsible for maintaining the data of employees in an Excel spreadsheet. It includes multiple worksheets based on departments in your company.
You wish to quickly know the index number of the sheet that includes the data of employees from the Accounts department.
Our formula would be as follows:
=SHEET(Accounts!A2)Where,
- Accounts is the sheet name
- A2 is the cell within the Accounts sheet
The use of ! symbol is mandatory here. Without that symbol, the function will return #NAME? Error.
The results are displayed in the following GIF.
The Accounts department sheet is in the 4th position in the given workbook.
Syntax
The SHEET is a simple function with the following syntax.
=SHEET([value])Where the value is an optional argument, you can specify any cell reference or range from the target sheet whose index number needs to be displayed.
Important Notes:
- If you do not specify the value argument, the function will return the index number of the current worksheet.
- If there are any hidden sheets in the workbook, the function will count them during the calculation.
- You will get a #REF! error when the sheet name is specified incorrectly.
You can use the SHEETS function in Excel to find the total number of sheets within the given workbook.
How to use the SHEET Function in Excel
Download the example spreadsheet used to demonstrate the SHEET function in the upcoming section. Practice is the key to success!
Example 1: SHEET Function Basics
Suppose that you prepared an Excel workbook that includes the scores obtained by students in a monthly test. This Excel includes multiple sheets based on the subjects.
Now, our task is to find the number of sheet that include the scores obtained by the students in the Maths subject.
Here are the steps:
- Go to the Maths sheet
- Select any cell
- Type =SHEET
- Choose the first option from the popup
- Complete the parentheses using )
- Hit the Enter key
Our formula is as follows:
=SHEET()Here, we are not specifying any cell reference or range in the place of the value argument, as we are in the same worksheet whose index number we wish to find.
The results are displayed in the following image.
Takeaway
The SHEET function in Excel is useful, particularly when there are multiple worksheets in the given workbook. It helps us reference cells dynamically based on their location.
If you wish to find the total number of sheets in the given workbook, please use the SHEETS function.
I hope this article taught you all the bells and whistles of the SHEET function. Please comment below if you are stuck or encounter any particular error while using it. I will answer your questions as soon as possible.
Additional Resources:
- Learn All Excel Information Functions (With Examples)
- SHEETS Function in Excel
- IF Function in Excel
- Guide to Conditional Formatting in Excel
Get an Office 365 Subscription to access all the powerful Functions and Tools in Excel.