How to Use SHEETS Function in Excel (5 Useful Examples)

Introduction to Excel SHEETS Function

The SHEETS function is a built-in function added in the Excel 2013 version which returns the number of worksheets in a given reference or without reference.

Function Objective

To count the number of sheets

Syntax

SHEETS ([reference])

Arguments Explanation 

Return Value

Sheet count (number)

Example 1 – Getting Number of Sheets without Reference Argument

To get the number of all working sheets without reference argument, use the following formula.

Example 2 – Finding Number of Sheets with Reference Argument

To count sheets with a reference, use the following formula.

In our example, we want to find the sheet number of cell B4 of sheet Price_Jan.

Example 3 – Tracking Number of Hidden Sheets Using The SHEETS Function

To count hidden worksheets, use the following SHEETS function with or without reference.

Example 4 – Finding The Number of Sheets While Dealing 3D Reference

To count the number of working sheets for 3D reference, e.g. cell A5 from sheets Price_Jan to Sales, use the following formula.

The formula counts the number of sheets between two provided sheets (including those sheets as well), there are 5 sheets and the formula returned 5.

Example 5 – Determining The Number of Sheets Between Two Sheets 

To count the number of sheets between two specific sheets, for e.g. the number of working sheets between Price_Jan and Sales, use the following formula.

The formula counts the number of sheets between two provided sheets, excluding those sheets.

Things to Remember

1. If the reference is not a valid value, the SHEETS function shows #REF! Error.

2. The SHEETS function is not available in the Object Model (OM) because similar functionality is already included in the Model.

Download Practice Workbook

<< Go Back to Excel Functions | Learn Excel