Excel - SHEET Function

Quiz

SHEET Function

The Excel SHEET function retrieves the reference sheet number. It was first launched in Excel 2013 and categorized under the Excel Information Functions list. This function can be evaluated with or without argument. For example, if you intend to find the number of opened worksheets in a specific workbook, you can write the formula SHEET() without passing the cell references. It is invaluable when recording a large number of worksheets along with their sheet names, managing massive worksheets, and checking the validity of the worksheets.

Compatibility

This advanced Excel function is compatible with the following versions of MS Excel −

Excel for Microsoft 365
Excel for Microsoft 365 for Mac
Excel for the web
Excel 2024
Excel 2024 for Mac
Excel 2021
Excel 2021 for Mac
Excel 2019
Excel 2016
Excel 2013

Syntax

The syntax of the SHEET function is as follows −

=SHEET(value)

Arguments

You can use the following arguments with the SHEET function −

Argument	Description	Required/ Optional
Value	It specifies the sheet name or a cell reference upon which the sheet number will be fetched. If the value argument is skipped, the SHEET function will retrieve the number of sheets in an active workbook.	Optional

Argument

Description

Required/ Optional

Value

It specifies the sheet name or a cell reference upon which the sheet number will be fetched.

If the value argument is skipped, the SHEET function will retrieve the number of sheets in an active workbook.

Optional

Points to Remember

The SHEET function must count all worksheets, visible and hidden, along with other sheet types (macro, chart, or dialog sheets).
If the invalid reference is specified in the VALUE argument, the SHEET function will retrieve the #REF! Error.
If the value argument specifies the invalid sheet, then the SHEET function will retrieve the #NA error.

How to use the SHEET Function in Excel?

Below are the step by step explanations are written below −

1. Deliberate an Excel worksheet.

2. Double click on the specific cell and write the formula =SHEET("specify the sheet name ! cell refernce) in it.

3. Then, hit the Enter tab to obtain the reference sheet number.

Examples of SHEET Function

Practice the following examples to learn the use of the SHEET function in Excel.

Example 1: Extracting Sheet Number using the Sheet() Function

Solution

In this example, we will apply the SHEET function to get the sheet number of the cell references scattered throughout the different worksheets.

Step 1 − First, double-click the B2 cell and enter the =SHEET() formula.

Extracting Sheet Number using Sheet() Function1

After that, press the Enter tab.

Extracting Sheet Number using Sheet() Function2

Hence, the number of opened worksheets is 3.

Step 2 − Afterward, double-click the B3 cell and type the formula =SHEET(Sheet2!B2). In this expression, we are provided the cell reference B2 of Sheet 2.

Extracting Sheet Number using Sheet() Function3

Therefore, the resulting value is 2 specifying the sheet number.

Extracting Sheet Number using Sheet() Function4

Step 3 − Furthermore, write the formula =SHEET(Sheet6!B3) in the B4 cell.

Extracting Sheet Number using Sheet() Function5

Extracting Sheet Number using Sheet() Function6

Example 2

If the value argument specifies the invalid sheet, then the SHEET function will retrieve the #NA error.

Solution

If you set the valid cell reference, the SHEET number gives you the correct sheet number. Otherwise, the invalid reference leads to the error value. Write the formula =SHEET("owner") in the B5 cell.

After that, hit the Enter tab.

Download Practice Sheet

You can download and use the sample data sheet to practice the SHEET function.

Print Page