Skip to Content

Learn Excel SHEETS Function (with Examples)

The Excel SHEETS function lets you count the total number of sheets within the given workbook. It is beneficial when you are managing huge workbooks with multiple worksheets. 

For example, suppose that you own an online store. To analyze last month’s orders, you exported an Excel file from your website. It includes multiple sheets, including the data of orders, customers, shipping details, etc.

You wish to know the total number of sheets in this exported file.

Your formula would be as follows:

=SHEETS()

Note that we haven’t specified any argument in the above formula.

The results are displayed in the following GIF.

How to use Excel SHEETS Function (With Example)

As you can see, there are a total of 8 worksheets in the given exported Excel file.

Syntax

The SHEETS is a simple function with the following syntax.

=SHEETS([reference])

Where the reference is an optional argument, you can specify any cell reference or range from a specific sheet or a 3D reference.

Important Notes:

  • If you do not specify the reference argument, the function will return the total number of worksheets within the given workbook.
  • 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.
  • The function is capable of reporting the sheet count in a 3D reference.

You can use the SHEET function in Excel to find the index number of the given sheet within the workbook.

How to use the SHEETS Function in Excel

Download the example spreadsheet used to demonstrate the SHEETS function in the upcoming section. Practice is the key to success!

Download Now

Example 1: SHEETS Function Basics

Suppose that you are a business owner. For quarterly financial analysis, you requested an expense report from the Accounts department. They have sent you the Excel file with multiple worksheets as follows:

  • Report
  • Jan_Expenses
  • Feb_Expenses
  • Mar_Expenses
  • Notes

Let’s assume that you wish to find the count of worksheets that include monthly expenses within this Excel file. You can use the SHEETS function here.

Here are the steps:

  • Select the desired cell
  • Type =SHEETS
  • Choose the first option from the popup
  • Specify Jan_Expenses:Mar_Expenses
  • Type !
  • Specify A1
  • Complete the parentheses using )
  • Hit the Enter key

Our formula would be as follows:

=SHEETS(Jan_Expenses:Mar_Expenses!A1)

Where,

  • Jan_Expenses is the sheet from which we wish to count the sheets
  • Mar_Expenses is the sheet where our count will end
  • A1 is the cell reference (you can choose any cell reference or range here)

Make sure to use the ! symbol between the sheet names and cell reference; otherwise, you will get a #NAME? error. 

Takeaway

SHEETS is one of the rarely used functions in Excel. It helps you quickly count the total number of sheets within the given workbook.

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)
  • SHEET 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.