Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to count the number of sheets of a workbook?

Have you ever considered how to count the number of worksheets in a workbook? To count them one by one is a boring job, if there are lots of worksheets. In fact, there is no straight formula to calculate the number of worksheets. But, here, I can introduce you some useful and handy tricks to deal with this problem.

Count the number of sheets in a workbook with Define Name command

Count the number of sheets in a workbook with VBA code

Show the number of sheets in a workbook by Navigation pane good idea3

Count the number of sheets in a workbook by creating a list of sheets good idea3


arrow blue right bubbleCount the number of sheets in a workbook with Define Name command


In Excel, you can apply a formula into the Define Name feature first, and then count the number of the worksheets.

formula Never need to worry about long long formulas in Excel anymore! Kutools for Excel's Auto Text can add all formulas to a group
as auto text, and liberate your brain! Click here to know Auto Text     Click here to get free trial

1. Launch your workbook which you want to count the number of worksheets.

2. Click Formula > Define Name, see screenshot:

doc-count-number-of-sheets1

3. And in the New Name dialog box, specify a name in the Name text box, and then enter this formula =GET.WORKBOOK(1)&T(NOW()) to the Refers to text box, see screenshot:

doc-count-number-of-sheets1

4. Then click OK to close this dialog, and return to the worksheet, then type this formula =COUNTA(INDEX(CountSheets,0))(CountSheets in this formula is the name you defined in step 3, you can change it as you need.) into a blank cell, see screenshot:

doc-count-number-of-sheets1

5. And press Enter key, you will get the number of your worksheets in the current workbook.

doc-count-number-of-sheets1


arrow blue right bubbleCount the number of sheets in a workbook with VBA code

If you think the above formulas are somewhat hard to remember, here is a simple VBA code also can help you.

1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA code: Count the number of sheets in a workbook

Public Sub CountWorkSheets()
'Update 20140326
MsgBox "The total number sheets of this workbook: " & Application.Sheets.Count
End Sub

3. Then press F5 key to execute this code, and a prompt box will pop out to tell you the total number of the worksheets as following screenshot shown:

doc-count-number-of-sheets1


arrow blue right bubble Show the number of sheets in a workbook with Navigation pane

If you have Kutools for Excel installed, you can obviously view the number of sheets in a workbook with the Navigation pane.

Kutools for Excel, with more than 120 handy functions, makes your jobs easier. 

After free installing Kutools for Excel, please do as below:

1. Click Kutools > Navigation to enable the Navigation pane in left of the worksheet. See screenshot:
doc navigation 1

2. Then click Workbook & Sheets tab, and then you can view the total number of sheets in the active workbook is shown in the bottom of the pane, also you can view the number of visible and hidden sheets of the workbook. See screenshot:
doc navigation 2

With Navigation pane, you can toogle between sheets, display all column headers, range names, and add auto texts and so on. Click here to know more details.

arrow blue right bubble Navigation  pane --  List and Count All Sheets


arrow blue right bubble Count the number of sheets in a workbook by creating a list of sheets

If you think of above methods are not convenient, I can introduce a round-way for you.

If you have installed Kutools for Excel, you can create a name list of all worksheets, then select them to count in the Status bar.

After free installing Kutools for Excel, please do as below:

1. Enable the workbook you want to count its worksheets, and click Enterprise > Worksheet > Create List of Sheet Names. See screenshot:doc count sheets 1

2. Then in the popping dialog, check Contains a list of hyperlinks option, and for selecting sheet names conviniently, you can display the sheet index in 3 or 4 columns. See screenshot:
doc count sheets 2

3. Click Ok, then a worksheet is created with all sheets’ names before all sheets, and you just need to select these sheet names, and view the counting result at the Status bar.doc count sheets 3

Actually, these sheet names are linkable, you can click one sheet name, and then quickly link to the relative sheet. And with Create List of Sheet Names, you can create linkable buttons of sheet names by check Contains buttons and macros option . Click here to know more about Create List of Sheet Names.

arrow blue right bubble Create list of Sheet Names



Kutools for Excel: 300 + functions you must have in Excel, 60-day free trial from here

goodNavigation pane -- Find and Replace

Kutools for Excel's advanced Find and Replace function, can help you find and
replace a value across multiple sheets and workbooks.
doc find and replace

Recommended Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 71%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 82% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    ro · 9 months ago
    =sheets() works great
    • To post as a guest, your comment is unpublished.
      Vishal · 3 months ago
      It Works only 2010 & Higher Version of Excel. Not Working In Excel 2007
  • To post as a guest, your comment is unpublished.
    Ahmad · 2 years ago
    I like it. Thanks too much
  • To post as a guest, your comment is unpublished.
    Linda · 2 years ago
    Thank you so much for posting these directions!! I have a workbook with over 700 sheets and never knew there was a way for excel to count them for me. I used the first method and it worked absolutely perfectly!!
  • To post as a guest, your comment is unpublished.
    Fahad Anwar · 3 years ago
    Great! Saved so much of my time!!
  • To post as a guest, your comment is unpublished.
    cdavis · 3 years ago
    Hi, Thanks! This worked for me great in excel 2013 to count the number of sheets. Crazy that a formula is required to do this.