SHEETS Function in Excel: Explained

In this article, you will learn what is the SHEETS formula and how to use the same in Excel.

‍

What does the SHEETS formula do in Excel?

The SHEETS function in Excel allows you to retrieve the count of sheets within a specified reference. It proves useful in determining the total number of sheets in a workbook or obtaining the count of sheets within a particular range. The SHEETS function is a versatile tool that can be used to automate tasks and improve the efficiency of your work.

‍

What are some uses of the SHEETS formula in Excel?

The SHEETS formula in Excel can be used for various purposes. Here are a few common use cases:

1. Counting sheets in a workbook: You can use the SHEETS formula to determine the total number of sheets in a workbook. This is helpful when you want to quickly find out how many sheets exist in a workbook without manually counting them.
2. Dynamic referencing: By combining the SHEETS formula with other functions like INDIRECT or INDEX, you can create dynamic formulas that reference different sheets based on the number of sheets returned by the SHEETS formula. This can be useful when you have a workbook with multiple sheets that follow a consistent structure, and you want to perform calculations or analysis across all sheets.

These are just a few examples of how the SHEETS formula can be used in Excel. Its versatility allows for flexible referencing and dynamic calculations across multiple sheets in a workbook.

‍

How to use the SHEETS function in Excel?

The syntax of the SHEETS formula in Excel is as follows:

=SHEETS(reference)

where "reference" is the cell or range of cells you want to test. If the reference argument is omitted, the SHEETS function will return the number of sheets in the workbook that contains the function.

The SHEETS function returns a value of type Integer. Do note, if a valid reference is not put as an argument the function will return #REF error.

Also, note that the SHEETS function counts the number of all worksheets including visible, hidden, or very hidden besides all other sheet types, such as macro, chart and dialog worksheets. Thus, you can use this function to ensure you don’t have any hidden sheets in the file before sending it out to others.

‍

Sample Use Case for the SHEETS formula in Excel

Suppose you have a dynamic Excel file which has sales data updated in Column B in a new sheet every month. In the summary sheet for the file, you wish to display the Total Sales for the latest month. You can follow the below steps using the Sheets function to create the summary slide that will always refer to the latest data. 

‍

Step 1: Open the Summary sheet of the file containing sales data in new sheets on a monthly basis

Step 2: Calculate the total number of sheets in the workbook using the SHEETS formula as shown below

Step 3: Use the output from the SHEETS formula to create an INDIRECT reference to Column B of the latest sheet. In the below example, information in Sheet 6 is used automatically to calculate the total sales and average sales for the most recent month in the summary sheet.  

‍

‍