How to use the SHEETS function

What is the SHEETS function?

The SHEETS function returns the number of sheets in a cell reference. It returns the total number of sheets in a workbook if the argument is omitted.

1. Introduction

What is a cell reference?

A cell reference lets you "fetch" and use values in other cells in a formula.

There are two styles of cell references:

• A1-style reference. The A1-style reference is the default style in Excel, it names columns by letters from A to Z. After Z it starts over with AA, AB, and so on until XFD. Rows are numbered from 1 to 1048576, older Excel versions use less row numbers.
• R1C1-style reference. The R1C1-style uses row number and column number like: R1C1, R2C5 and R10C15. Rows are labeled R1, R2, R3 and so on, columns are labeled C1, C2, C3 etc.

The A1-style reference notation is the most common one, here are some examples:

• A1 - single cell reference on the same worksheet
• A1:D5 - reference to a cell range on the same worksheet
• Budget!Z3 - a single cell reference to worksheet Budget
• 'Budget 2050'!A3 - a single cell reference to a worksheet containing a space character

There are two types of cell references:

• Relative cell references
• Absolute cell references

The examples above are all relative cell references, they change accordingly if a cell is copied and pasted to another cell which absolute cell references do not. The $ dollar character lets you an absolute cell reference meaning you can lock a cell reference horizontally, vertically or both. Here are a few examples:

• A1 is a relative cell reference. It will change when the cell is copied to another cell.
• A$1 has a relative column reference but an absolute row reference, this means that the column letter may change if the cell is copied and pasted to cells in another column than A.
• $A$1 has absolute cell references for both the row and column parts. Nothing changes in the cell reference when you copy the cell and paste it to another cell.

What is a worksheet?

A worksheet is a single page in an Excel workbook sometimes also called only sheet. You can see the different worksheets in your open workbook by examining the tabs located in the bottom of the worksheet. The tabs have names that you can rename but they also have a sheet number that represents the order or rank in the workbook. The first tab which is the bottom most left one is numbered 1, the next one is the second worksheet and so on.

The worksheet contains a cell grid where you can enter, organize, manipulate and analyze data. Excel allows worksheets within a workbook to have different visibility states:

• Visible worksheet: Worksheet is fully visible and interactive in the workbook, the tab is shown at the bottom. This state is default for new worksheets.
• Hidden worksheet: Not visible but can be made visible using the Unhide command, the tab is hidden at the bottom of the worksheet which makes it impossible to select it. This state is useful for hiding data but keeping it accessible.
• Very hidden worksheet: Not visible and not accessible via the Unhide command, the tab is hidden at the bottom. A worksheet can only be made "very hidden" using VBA or the Visual Basic Editor.

Does the SHEETS function work with 3d-ranges?

Yes, the SHEETS function work with 3d-ranges, see the example below in section 3.

2. Syntax

SHEETS(reference)

reference

Optional. The reference you want to determine the number of sheets it contains.

3. Example 1

This image shows examples of the SHEETS function in Excel and its various use cases. Column A (B2:B7) shows the results of different SHEETS function calls. Column B (C2:C7) shows the corresponding formulas used. The SHEETS function is used to count the number of sheets in a workbook or a specified reference.

The following formula counts the number of worksheets in cell reference F15.

Formula in cell D3:

This formula returns 1 which represents the number of sheets in cell reference F15. This reference points to cell F15 on the same worksheet since the worksheet name is omitted from the cell reference.

Formula in cell D4:

This formula returns 3 meaning there are three worksheets in this workbook. If no argument is specified the SHEETS function returns the total number of sheets in this workbook.

Formula in cell D5:

This formula returns 2 which represents the number of sheets in cell reference Sheet2:Sheet3!A1:B2. This reference is a 3d-reference that points to multiple worksheets.

Formula in cell D6:

This formula returns 1 which represents the number of sheets in the named range Tax_code. This named range points to Sheet2!$G$13 which is cell G13 on worksheet Sheet2.

Formula in cell D6:

This formula returns an #VALUE! error which confirms that the SHEETS function can't handle multiple cell references.

4. Example 2

This example shows how to display the count of worksheets in the current workbook. Cell B3 contains a formula that combines text and the total count of worksheets.

Formula in cell B3:

This formula combines text with the SHEETS() function to create a dynamic sentence that reports the number of sheets in the current workbook.

• SHEETS(): This function, when used without arguments, returns the total number of sheets in the active workbook.
• "&": The ampersands are used for string concatenation in Excel, joining text and the result of the SHEETS() function.
• "There are " and " sheets in this workbook.": These are text strings that form the beginning and end of the sentence.

When Excel evaluates this formula, it:

1. Calculates the result of SHEETS() which in this case is 4.
2. Concatenates this number with the surrounding text.

The result, as shown in cell B3, is: "There are 4 sheets in this workbook.". This formula is useful for automatically updating the sheet count whenever sheets are added or removed from the workbook. This formula provides a dynamic way to display this information.

Functions in 'Information' category

The SHEETS function function is one of 19 functions in the 'Information' category.

Excel function categories

Excel categories

Leave a Comment