How to Use Excel SHEET Function (6 Examples)

Get FREE Advanced Excel Exercises with Solutions!

Microsoft Excel has a variety of functions and tools. The Excel SHEET function accepts only one argument, value, which can be a reference, a specified range, or an Excel Table. Sometimes we need to deal with a huge amount of data that contains so many sheets. In that case, we can use the Excel SHEET function to track the sheets properly.


Overview of Excel SHEET Function

  • Description

The SHEET function returns the sheet number of the reference sheet.

  • Generic Syntax

=SHEET([Value])

  • Argument Description
Argument Requirement Explanation
Value Optional The name of a sheet or a reference for which we need the sheet number is the value. The SHEET function returns the number of the sheet that contains the function if the value is omitted.

How to Use Excel Sheet Function: 6 Suitable Examples

In this part, we’ll show you six different ways to use the Excel SHEET function. In this article, we will use different datasets for different examples.

1. Get The Active Sheet Index Number in Excel Using SHEET Function

First and foremost In the following figure, we can see that we will extract the index number of our active sheet named “Active_Sheet_Index” in the “Sheet Number” column. Also, follow the instructions given below to solve this problem.

Get The Active Sheet Index Number in Excel Using SHEET Function

  • Firstly, select cell C5 and insert the following code.
=SHEET()
  • Then, press Enter. We get the index number of “Active Sheet Index” in cell C5.

Get The Active Sheet Index Number in Excel Using SHEET Function


2. Use of Excel SHEET Function to Check Any Random Sheet Number

Not only can we get the active sheet’s index number, but we can also get the index number of any random sheet in the collection. From the following figure, we can see that we have to extract the index no. of the sheets under the “Random Sheet Name” column in the “Random Sheet Number” column. Let’s see how can we solve this:

  • Firstly, select cell C5. We want to insert the index number of sheet “Store-2” in this cell.

Use of Excel SHEET Function to Check Any Random Sheet Number

  • Next, place the following formula in cell C5 and press Enter. We can see the index number of the sheet “Store-2” is 5.
=SHEET(Store_2!L6)

Use of Excel SHEET Function to Check Any Random Sheet Number

  • Finally, put the corresponding formulas in cells C6, C7 & C8. Then, after pressing Enter we can see the index number of all four sheets.


3. Find Out The Index of The Sheet “Store-3”

This example is similar to the previous one. We need to figure out the sheet index number again. From the following figure we can see the “Sheet Name” column and the blank “Sheet Number” Column.

Apply SHEET Function to Find Out The Index of The Sheet “Store-3”

  • Firstly, select cell C5 and insert the following formula:
=IF(Store_3!A3=0,SHEET("Store_3"),"value A2<>0")
  • Then, press Enter. We get the value of the index number of the sheet “Store_3” is 6.

Apply SHEET Function to Find Out The Index of The Sheet “Store-3”

  • Finally, place the corresponding formulas in cells C6, C7 & C8. We can see the sheet number corresponding to their sheet name.

➥ Formula Breakdown

  • Store_3!A3=0

This part is the logical_test part. The exclamation sign refers to cell A3 in the sheet “Store_3”.

  • SHEET(“Store_3”)

It’s the value_if_true part. Select the sheet “Store_3”.

  • “value A2<>0”

It means the value of cell A2 is not equal to zero.

  • IF(Store_3!A3=0,SHEET(“Store_3″),”value A2<>0”

The IF function checks whether the value stored in cell A3 of the sheet “Store_3” is equal to zero or null.


4. Function to Get The Reference Cell Sheet Number

We can get the index numbers of a sheet using the reference of the cell value of that sheet from another active sheet. In this example, we will introduce three new datasets. The new three datasets are sales data of three stores from January to April.

Store-1:

SHEET Function to Get The Reference Cell Sheet Number We can get the index numbers of a sheet using the reference of the cell value of that sheet

Store-2:

SHEET Function to Get The Reference Cell Sheet Number We can get the index numbers of a sheet using the reference of the cell value of that sheet

Store-3:

SHEET Function to Get The Reference Cell Sheet Number We can get the index numbers of a sheet using the reference of the cell value of that sheet

Here, according to the following figure, we have to insert the “Sheet Number” of the given “Reference Cell Value”.

SHEET Function to Get The Reference Cell Sheet Number We can get the index numbers of a sheet using the reference of the cell value of that sheet

Let’s see how we can use a cell reference to find the sheet number step by step.

  • Firstly, we will select cell C5. We have to input the value of the sheet number that contains the cell value “Book”.

  • Next, enter the part of the sheet formula that we can see in the figure below.
=SHEET(

SHEET Function to Get The Reference Cell Sheet Number We can get the index numbers of a sheet using the reference of the cell value of that sheet

  • Then, go to the sheet “Store_1”. Select cell B6  and the formula bar will show formula like this:
=SHEET(Store_3!B6)

  • After that press Enter. We can see the index of the sheet that contains the cell value “Book” is 7.

  • Finally, insert the corresponding formulas for other cell values under the column “Reference Cell Value”. By pressing Enter we will get the index of the sheet containing given cell values.


5. Show Sheet Number in a List in Excel

In Microsoft Excel, we can make a list of the sheets and their index numbers that we use in a dataset. Suppose we want to enter the index number of the sheet “Store_1”  in cell C5. We will do this in the following steps:

  • Firstly, select cell C5. Place the following formula and press Enter. We will get the index value of the sheet “Store-1”.
=SHEET(INDIRECT(B5&"!A1"))

Show Sheet Number in a List in Excel

  • Next, insert the corresponding formulas in cells C6 & C7. By pressing Enter we will get the value of the index number of the sheet “Store-2” and “Store-3”.

➥ Formula Breakdown

  • INDIRECT(B5&”!A1″)

Select cell B5. Takes cell A1 as a reference cell.

  • SHEET(INDIRECT(B5&”!A1″)

Returns the index number of the sheet “Store-1”. It’s the value of cell B5.


6. Insert Function to Find Out The Sheet Number by using Table Name in That Excel Sheet

In this example, we will learn how to find the index number of a sheet by using the Table Name of that sheet.

At this time, we have the following four tables. We will figure out the sheet number of the four tables. Without a doubt, the index number of all the sheets will be the same, because all the four tables are on the same sheet.

So we will practice this example abiding by the following steps:

  • Firstly select “Table-1”. Go to the Table Design tab. We can see the table name is “Table1”.

Insert SHEET Function to Find Out The Sheet Number by using Table Name in That Excel Sheet

  • Secondly, in another sheet like shown in the figure below select cell C5. In this cell insert the SHEET function. Insert the table name “Table1” as the value of that sheet function and press Enter. We can see that “Table1” is in sheet 9.

Insert SHEET Function to Find Out The Sheet Number by using Table Name in That Excel Sheet

  • Finally, put the values of the corresponding table name in place of value in the SHEET function. We can see the index number of all tables is the same as all tables placed on a single sheet.


Download Practice Workbook

You can download the practice workbook from here.


Conclusion

In this article, we have different examples of the Excel SHEET function. Finally, you have come this far than download our practice workbook added to this article and practice yourself. You can leave a reply in the below section if you have any confusion. Stay tuned with us for solutions to Excel problems.


<< Go Back to Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mukesh Dipto
Mukesh Dipto

Mukesh Dipto is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including Rhino3D, Maxsurf C++, AutoCAD, HTML, CSS, JavaScript, and WordPress. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo