How many sheets are in an Excel workbook

Knowing the number of sheets in an Excel workbook is useful when checking the structure of a document, automating data processing or preparing reports. Here are several simple ways to find out how many sheets your workbook has.

1. The SHEETS function

The most accurate and universal way is the SHEETS function. It returns the total number of sheets in the workbook, no matter which sheet you are on. Just type on any sheet:

=SHEETS()

It counts all sheets, including hidden ones and chart sheets. Available from Excel 2013.

2. The SHEET function on the last sheet

If you just need the number of the last sheet, use the SHEET function on it. Go to the very last sheet and type in an empty cell:

=SHEET()

It returns the position of the current sheet in the workbook. Hidden sheets are counted too, so if the last tab is visible, this number equals the total number of sheets.

3. The Immediate window in the VBA editor

In Excel 2010 and earlier, SHEET and SHEETS are not available, but the built-in Visual Basic editor helps:

• Press Alt+F11 to open the VBA editor.
• Press Ctrl+G to open the Immediate window.
• Type the line below and press Enter:

? ActiveWorkbook.Sheets.Count

A number appears in the window: that’s the number of sheets in the active workbook.

4. A COUNTA trick with a 3D reference

This one works in any version without VBA:

• Select all sheets of the workbook: right-click any tab → Select All Sheets.
• Go to cell XFD1 (the last column of the first row, which surely has no data) and type any value. It appears on all selected sheets at once.
• Click a single sheet tab to ungroup the sheets, and in an empty cell on any sheet, enter this 3D formula:

=COUNTA('First sheet:Last sheet'!XFD1)

Replace “First sheet” and “Last sheet” with the real names of the first and last sheets in your workbook. The result shows how many sheets the workbook contains, since the same value was written on each of them. Not the most elegant way, but it works without code. Don’t forget to clear XFD1 afterwards the same way.

If you are sure all sheets have data in a certain cell, for example A1, skip the filling step and use the formula right away.

5. VBA code to count sheets

If you often work with macros, this simple code shows the number of sheets instantly:

Sub CountSheets()
    MsgBox "Sheets in the workbook: " & Sheets.Count & vbCrLf & _
           "Worksheets only: " & Worksheets.Count
End Sub

Sheets.Count includes chart sheets, Worksheets.Count only regular sheets.

6. With the !SEMTools add-in

If you use !SEMTools for Excel, counting sheets takes literally one click. The “Create table of contents” procedure builds a separate sheet with hyperlinks to all sheets and numbers them automatically. More in the article on how to create a table of contents in Excel.

The “List of sheets” tool opens a window with all sheets, where you can find the one you need with a filter, see their total number and switch between sheets without changing the workbook:

This is especially handy in large files with hundreds of sheets.

Summary

I hope you found what you were looking for on this page. In Excel 2013 and later, =SHEETS() gives the answer at once. In older versions, use the Immediate window or the COUNTA trick, and with !SEMTools you see the number of sheets in the sheet list in one click.