Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Use Excel SHEET Function (6 Examples)

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.


Download Practice Workbook

You can download the practice workbook from here.


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.

6 Examples of Using Excel Sheet Function

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

Read More: How to Get Excel Sheet Name (2 Methods)


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.

Read More: How to Search by Sheet Name in Excel Workbook (2 Handy Methods)


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.

Read More: How to Use Excel Sheet Name From Cell Value (Three Ways)


Similar Readings


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.

Read More: How to List Sheet Name in Excel (5 Methods + VBA)


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.


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.


Related Articles

Mukesh Dipto

Mukesh Dipto

Hi there! I am Mukesh Dipto. I graduated with a bachelor's degree in engineering. Currently, I am working as a technical content writer in ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include sports and meeting new people. I also enjoy sports. My favorite sports are football (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo