## Saturday, December 31, 2011

### Formula To Count The Number Of Sheets In The Excel Workbook (Version 2003 And Below)

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 workbook' 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 'New' (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.

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

2. It works fine, but in Excel 2010
Formula is =COUNTA(INDEX(CountSheets;0))
Thanks

1. This comment has been removed by the author.

2. Interesting. In Excel 2013, the recommended formula works:
=COUNTA(INDEX(CountSheets,0))

3. thank so much

It's what I was looking for

4. Absolutely brilliant! thanks

5. Its a very easy and a brilliant option.... thank you....

6. Thanks for the formula. It works great.

However, 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?

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

7. Awesome. Thanks for the help.

8. Great Idea!! Thank you!!

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

1. Not if you have renamed the sheets.

10. 11. 1. Click ''Ctrl+F3', then 'Define Name' Box appears
2. 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

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

12. 13. wroking.... :)

- rupali

14. 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?

15. THANK YOU!!!

16. MANY THANKS !!!
Brahim.

17. Thank you boss
-excel student

18. Thanks it works

19. Excel 2010, I have 10 sheets but only returning 1. Followed instructions correctly. Why?
=COUNTA(INDEX(CountSheets,0))

20. 21. Awesome. Pretty easy to do it. Thanks a lot.

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

22. Hi,
i have the same problem on 2013
Iv followed instructions and it returns 1 no matter how many sheets i have.

23. thnks, works like a charm

24. Thanks, Its fantastic indirect way to count sheets

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

26. Thanks mate.
Henry Bruce Lawson

27. very very useful

28. Perfect solution, thanks

29. Great! Thanx!!

30. This comment has been removed by the author.

31. Hello Saran,

Could 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

32. Thank You So Much. God Bless You

33. For 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))

34. thank you, it works.

35. Great 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

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

37. Good Solution !
But if some sheet have more than 2 pages it can't count.
How can it count total pages in workbook?
Thank You

38. Works great in Excel 2010
Thanks for sharing the solution

39. 40. How do I count sheets minus 2 sheets?

Thank you!

1. Just put a -2 at the end:

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

41. Thanks a lot.

42. I 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

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

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

46. Overweight is a major problem of majority of people now days, due to overweight people have to face so embarrassment for going to any parties. Because of overweight you suffer many diseases like diabetes, heart problem. Blood pressure, etc. youRead More Best Slimming Tea’s Review Best Slimming Teas

47. Phen24 is a complete weight loss solution which works around the clock, unlike any other pills available in the market.

1. i things its all about to lose weight and you should try this garcinia cambogia free trial

48. awesome, it works :) thanks mate

49. Thanks a lot.

50. 51. Is it possible to count the sheets in workbook A from another workbook B and save that formula in the workbook B? This way every time I open workbook B it will calculate and tell me the number of sheets in workbook A.

TIA.

52. Excellent help. Thank you.

53. Thank U. It helps

54. 1. 55. 56. 57. Thank you so much! This is great!

58. how do i count selective sheets in excel ? i mean if i want to count sheets starting with only letter A? is it possible?