How to get a list of sheets in an Excel workbook

Sheets in Excel workbooks can be given names that match the content. Of these, it would be convenient to make a table of contents, but not everyone knows how to do this. There are simple ways to generate a list of sheets and methods that require effort, such as installing third-party add-ons. With Excel tools, users can also count the number of sheets in a large workbook. Let’s find out how to get a table of contents for reading or jumping to each sheet, what formulas you need to use for this.

List of sheets using a formula

This method is based on using a function that cannot be found in the Manager. It is related to Excel 4.0 macros. To put the formula into practice, you need to go through an additional step, rarely seen in working with functions – go to the name manager and add an expression there.

1. Go to the “Formulas” tab and click on the “Name Manager” button. The option is in the Defined Names section.

2. Click “Create” in the dialog box that opens.

3. We write a new name in the upper field, select the “Book” area (usually it is set by default) and write this formula in the “Range” column: =REPLACE(GET.WORKBOOK(1),1; FIND(“]”,GET.WORKBOOK(1));””)
4. After filling in all the fields, click “OK”. Nothing has changed in the Excel workbook yet, but these steps will help in the future. The Name Manager window can be closed.

5. Open the sheet where the list will be located. Select a cell and write in it the formula with the name you just created: =INDEX(List_sheets, STRING()). Press Enter, and the name of the first sheet will appear in the cell.

6. It is necessary to display all sheet names in a column. To do this, hold down the fill marker, which is located in the lower right corner of the selected cell, and select the required number of cells. In the example table, there are 4 sheets, so many cells were selected.

7. Let’s create a list from which you can go to each sheet. Select another empty cell and paste this formula: =HYPERLINK(“#”&A1&”!A1″;””&A1).

Pay attention! Cell A1 is written in the formula so that users can navigate to a specific cell on each sheet. After pressing Enter, a clickable sheet name will appear.

8. A complete list with hyperlinks can also be created using the fill handle.
9. If the sheet name is changed, you will have to repopulate the lists – to do this, you just need to delete them, insert the same formulas again and select the cells below.

Important! Unable to link to chart sheet in Excel. An error message appears on the screen, the transition does not occur.

How to List Sheets Through VBA

There is another way to list sheets from a book – you can connect a user-defined function through the Visual Basic editor. This method may seem complicated, but it is not so if you use a template to add a function to a program.

1. Open VBA with Alt+F11 key combination. If pressing F11 dims the brightness or activates another function assigned to this button, hold down the Fn key.
2. Click “Insert” on the top bar and select “Module” from the menu that opens.

3. Paste this text into the free field:

Function SheetList(N As Integer)

SheetList = ActiveWorkbook.Worksheets(N).Name

End Function

4. You can then close the Visual Basic window because you no longer need this tool and the feature has already been added to the program.

5. Open the sheet for the list and enter the formula in the starting cell. Now you do not need a long expression to create a list of sheets. The new formula looks like this: =SheetList(ROW()).
6. Press Enter and get the name of the sheet in the cell. Create a list with a fill marker.

7. For hyperlinks, you’ll have to use the same long formula: =HYPERLINK(“#”&A1&”!A1″;””&A1).

Sheet list add-ins

Add-ins are add-ons for Microsoft Excel that are created by advanced users. Microsoft talks on the official website about the ability to connect add-ons, but does not offer to download add-ons, so they are usually downloaded from other sources.

Always check downloads for malicious items with an antivirus program.

There are paid and free add-ons. Today we will consider a set of add-ons for Excel 2007-2019 called “Ёxcel”. The developer distributes the file on his site for a voluntary donation. Install the add-in according to the instructions – after that you can start compiling the list.

1. Open the sheet where the list will be placed, and left-click on the initial cell of the future enumeration.
2. On the add-ons tab, we find the “Sheets” button. We click on it to open the menu, and select the item “Get list of sheets of the book.” Screenshot of the add-on creator:

3. Choose which sheets to show in the list. For the simplest enumeration of sheet names, click on the “Simple List” item and click on the button with a check mark in the lower left corner of the dialog box.
4. A list of sheets will appear on the screen. If the settings are more difficult to set, then the appearance of the list will change slightly.

How to count the number of sheets in a book

Sometimes many sheets appear in Excel workbooks, for example, if the document belongs to a large project. You can find out how many pages are in a file using the SHEETS function.

Pay attention! The function works only in versions of Microsoft Excel from 2013.

1. Select an empty cell and write the formula in it: =SHEETS(). It is not necessary to fill in the “Reference” argument if you want to count the sheets in one workbook.

2. Press Enter and get a numeric value.

If all sheets have been renamed and you need to know their numbers, use the SHEET function. This formula has also been available since 2013. The SHEET function has one argument – “Value”. If the argument is not filled in, after pressing the Enter key, the number of the same sheet where the formula was entered will appear in the cell. A simple expression with LIST looks like this: =SHEET().