In fact, MS Excel doesn't have any Straight Formula to Count the Number of Sheets in the Workbook. But, we have Indirect Way to Count the same.

*HOW?*First we need to "Define Name" by Referring to =GET.WORKBOOK(1)&T(NOW())

Then, Use the below Formula to Count the Number of Sheets

=COUNTA(INDEX(DEFINED NAME,0))

__Step By Step?__**2003 and Below excel versions**

1. Click ''Ctrl+F3', then 'Define Name' Box appears

2. Enter 'Names in

__w__orkbook' as**CountSheets***(Your choice)*3. Enter 'Refers To' as =GET.WORKBOOK(1)&T(NOW())

4. Click OK

5. Come to the Cell, where you want to enter the Formula

6. Enter the Formula as =COUNTA(INDEX(

**CountSheets**,0))7. Click Enter

**2007 and above Excel Versions**

1. Click 'Ctrl+F3', then 'Name Manager' Box Appears

2. Click '

__N__ew' (use shortcut key Alt+N)3. Then, 'New name' box appears

4. Enter 'Name' as "

**CountSheets**"*(Your choice)*5. Enter 'Refer To' as =GET.WORKBOOK(1)&T(NOW())

6. Click OK

7. It takes you to the 'Name Manager' box again (you can find the Defined Name in the list)

8. Click 'Close'

9. Come to the Cell, where you want to enter the Formula

10. Enter the Formula as =COUNTA(INDEX(

**CountSheets**,0))11. Click Enter

If you google on this, you can find alternative solutions with VBA code. Without using VBA, I think this is the easy way to Count Number of Sheets.

Don't forget to give comment below, if you find any alternative solution, or if you have any questions.

This was exactly what I was looking for. Thank you. It works great.

ReplyDeleteIt works fine, but in Excel 2010

ReplyDeleteFormula is =COUNTA(INDEX(CountSheets;0))

Thanks

This comment has been removed by the author.

DeleteInteresting. In Excel 2013, the recommended formula works:

Delete=COUNTA(INDEX(CountSheets,0))

thank so much

ReplyDeleteIt's what I was looking for

Absolutely brilliant! thanks

ReplyDeleteIts a very easy and a brilliant option.... thank you....

ReplyDeleteThanks for the formula. It works great.

ReplyDeleteHowever, when I close and open the file, the formula does not calculate correctly. To get the correct figure I have to click Ctrl F3 and select the CountSheet option every time I open the file. Is there a way to ensure the formula is permanent?

You have to save it as a macro-enabled workbook!

DeleteAwesome. Thanks for the help.

ReplyDeleteGreat Idea!! Thank you!!

ReplyDeleteEven easier way of doing it. Just insert a new sheet at the beginning and it will number it for you.

ReplyDeleteNot if you have renamed the sheets.

Deletelike this

ReplyDelete1. Click ''Ctrl+F3', then 'Define Name' Box appears

ReplyDelete2. Enter 'Names in workbook' as CountSheets (Your choice)

3. Enter 'Refers To' as =COUNTA(INDEX(GET.WORKBOOK(1)&T(NOW()),0))

4. Click OK

5. Come to the Cell, where you want to enter the Formula

6. Enter the Formula as =countsheets

7. Click Enter

Not valid.

DeleteYour solution is great! It will automatically update the number after you added 1 or more Sheet to your workbook.

Deleted

ReplyDeletewroking.... :)

ReplyDelete- rupali

It does not work, because excel does not reconyze the function 'GET.WORKBOOK'. Anybody can help me? It seems that it is not among the available functions. Does it depend on the edition?

ReplyDeleteTHANK YOU!!!

ReplyDeleteMANY THANKS !!!

ReplyDeleteBrahim.

Thank you boss

ReplyDelete-excel student

Thanks it works

ReplyDeleteExcel 2010, I have 10 sheets but only returning 1. Followed instructions correctly. Why?

ReplyDelete=COUNTA(INDEX(CountSheets,0))

thanks :)

ReplyDeleteAwesome. Pretty easy to do it. Thanks a lot.

ReplyDeleteAlso to Rashid who brought a modification of the formula and works great also.

Hi,

ReplyDeletei have the same problem on 2013

Iv followed instructions and it returns 1 no matter how many sheets i have.

thnks, works like a charm

ReplyDeleteThanks, Its fantastic indirect way to count sheets

ReplyDeleteI am using office 2007, i have followed the above steps, but i am getting couint as only 1. Can some one help us please.

ReplyDeleteThanks mate.

ReplyDeleteHenry Bruce Lawson

very very useful

ReplyDeletePerfect solution, thanks

ReplyDeleteGreat! Thanx!!

ReplyDeleteThis comment has been removed by the author.

ReplyDeleteHello Saran,

ReplyDeleteCould you modified this formula for me to know the current number of every single worksheet by sequence of the sheet

e.g : Column1,Row1 (Every sheets)

Sheet1=1

Sheet2=2

Sheet3=3

And if I copied one of those 3 sheets in between, the formula will automatically counting it

e.g:

Sheet1=1

Sheet1(1)=2 (newly copied sheet)

Sheet2=3

Sheet3=4

Thanks in advance

Thank You So Much. God Bless You

ReplyDeleteFor all those who got 1 as the result, they might have made the mistake of naming the "Macro" differently. If you name the macro as xyz then the formula should be =COUNTA(INDEX(xyz,0)) and not as =COUNTA(INDEX(CountSheets,0))

ReplyDeletethank you, it works.

ReplyDeleteGreat it works with excel 2010 but after adding more sheets it doesn't count automatically, so i have to double click the formula and press enter...it works

ReplyDeleteIs there a way to do this so that you are counting the sheets in a separate workbook?

ReplyDeleteGood Solution !

ReplyDeleteBut if some sheet have more than 2 pages it can't count.

How can it count total pages in workbook?

Thank You

Works great in Excel 2010

ReplyDeleteThanks for sharing the solution

Super

ReplyDeleteHow do I count sheets minus 2 sheets?

ReplyDeleteThank you!

Just put a -2 at the end:

Delete=COUNTA(INDEX(CountSheets,0))-2

Thanks a lot.

ReplyDeleteI don't know where to go to find out this question i have. Maybe one of you can tell me. I have a workbook with many sheets. If i want to enter something say in the 20th page then on the 5th page. I have to scan all the sheets to find them. Is there a way that you can "wrap" the sheets so all the sheets show and all i would have to do is click on it. it would be nice to be able to see all the sheets

ReplyDeletevery helpful thank you

ReplyDeleteSuper helpful thanks!

ReplyDeleteAnyone know how a formula to get worksheet number out of total number of worksheets? So, worksheet 5 of 10 for example. Thanks!

Awesome. It was very helpful. Thank you so much..

ReplyDelete