4 Efficient Ways to List All Worksheet Names in an Excel Workbook
Navigating through numerous worksheets in a large Excel workbook can be cumbersome, especially when you need a quick overview of all available sheets. This article explores four practical methods to list all worksheet names in a workbook, enhancing your productivity and workbook organization.
![](http://cdn.extendoffice.com/images/stories/doc-excel/navigationpane/doc-list-worksheet-names-3.png)
List all sheet names in a workbook
This tutorial presents four effective methods for listing all sheets in a workbook. Select the approach that best suits your needs and follow the step-by-step instructions to accomplish the task efficiently.
Using copy and paste (manually)
The simplest method to list all worksheet names involves a manual process:
Step 1: Open a new workbook
Step 2: Double-click on the sheet tab that you want to copy
Step 3: Press Ctrl + C keys to copy the sheet name
Step 4: click on a cell and press Ctrl + V to paste the sheet name
Step 5: Repeat the above steps to copy and paste all sheet tab names to the workbook one by one
This method works well for a few sheet names but becomes time-consuming with dozens of sheets. For larger workbooks, consider the more efficient methods described below.
Using Kutools for Excel (easy, sheet names are linkable)
Kutools for Excel's Create List of Sheet Names enhances Excel by quickly generating a linked list of worksheet names. This feature automatically creates hyperlinks for each sheet, allowing direct navigation with a click—perfect for managing large workbooks. It surpasses Excel's manual methods in efficiency and ease.
Click Kutools Plus > Worksheet > Create List of Sheet Names, in the popping dialog, specify the settings:
Choose the index styles;
Give a name for a new sheet which will list the sheet names;
Specify the location of the new sheet;
Decide how to place the sheet names;
Click OK.
List of hyperlinks | List buttons and macros |
Then you can copy this new worksheet in a new workbook by right-click at the sheet tab, and choose Move or Copy from the context menu.
For quick viewing and effortless navigation between sheets, Kutools for Excel's Navigation pane is highly effective. It not only lists all sheet names as clickable links for easy access but also displays all open workbooks, facilitating swift switching between them.
Using Name range (list in a new sheet)
In Excel, you can efficiently list all sheet names in a new sheet by creating a named range.
Step 1: Click Formulas > Define Name
Step 2: Edit the name and formula in the New Name dialog
Name: type a name for the name range
Refers to: copy and paste the formula below to it
=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")
Click OK.
Step 3: Using a formula to list sheet names
Type the formula below in a cell and press Enter key to get all sheet names.
=TRANSPOSE(ListNames)
ListNames is the name you give for the name range in step above.
Using VBA (unstable)
A more advanced and customizable method involves writing a VBA script to extract and list worksheet names:
Step 1: Open the workbook you want to list its sheet names
Step 2: Press Alt + F11 keys open the Microsoft Visual Basic for Applications window
Step 3: Click Insert Module
Step 4: Copy and paste below code to the module
Sub ListSheetNamesInNewWorkbook()
'UpdatebyExtendoffice
Dim wbSource As Workbook
Dim wbTarget As Workbook
Dim wsTarget As Worksheet
Dim i As Integer
' Reference the current workbook
Set wbSource = ActiveWorkbook
' Create a new workbook
Set wbTarget = Workbooks.Add
Set wsTarget = wbTarget.Sheets(1)
' List all sheet names from the active workbook in the new workbook
For i = 1 To wbSource.Sheets.Count
wsTarget.Cells(i, 1).Value = wbSource.Sheets(i).Name
Next i
' Cleanup
Set wbSource = Nothing
Set wbTarget = Nothing
Set wsTarget = Nothing
End Sub
Step 5: Press F5 key or click Run button to run the code
Now a new workbook is opened to list all worksheet names.
Each method offers different advantages depending on your needs—from simple manual listing to more sophisticated solutions like Kutools or VBA. Choose the one that best fits your workflow to effectively manage and navigate through your Excel worksheets. For more Excel tips and tricks, !stay tuned to our blog.
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
![Screen Shot of Excel (with Office Tab installed)](http://cdn.extendoffice.com/images/stories/kte-module/officetab-bottom-02.gif)