Excel - Formula to get total number of worksheets

I have 100+ sheets, it is hard for me to count them one by one.

Is there a one line formula to get the total number of sheets? The CELL or ADDRESS function perhaps?

I remember using something like this once, but I cannot seem to remember it.

Thank you.

4 Answers

Here is a neat, formula-based solution.

1. Create a New Name in Name Manager called wshNames, with the value: =RIGHT(GET.WORKBOOK(1),LEN(GET.WORKBOOK(1))-FIND("]",GET.WORKBOOK(1)))

2. In any cell you want to show the number of worksheets, enter: =COUNTA(wshNames)

Note: Step 1 relies on an XLM formula. More on this here - note you will need to save as a .xlsm. This technique is borrowed from David Hager, via this.

you have to use a little bit of VB for that.

Try for "Table sheets -> without Diagrams"

Public Sub test() MsgBox ThisWorkbook.Worksheets.Count
End Sub

Try for "All Sheets" -> including Diagrams

Public Sub test2() MsgBox ThisWorkbook.Sheets.Count
End Sub

You can easily use the sheets formula, in any empty cell you can type=SHEETS()and that's it. it will count all the sheets in the workbook.

Insert a module in the workbook of which you want to count the total number of sheets of.

Then copy in the below code and hit run

 Public Sub CountWorksheets() MsgBox "Total Sheets count:" & Application.Sheets.Count End Sub

You'll get a relevant output: