How to list all table names in Excel?
For listing all table names in a workbook, methods in this article can help you to get through it.
List all table names in the Name Box
List all table names in the Formula Bar
List all table names with VBA code
List all table names in the Name Box
All table names are listed in the Name Box by default.
Click the drop down list arrow on the top left corner Name Box to display all table names. See screenshot:
Note: All ranged names are also listed in the Name Box with all table names.
List all table names in the Formula Bar
If all tables were named by original table name such as Table1, Table2…, you can try to list all these table names in the Formula Bar.
1. Enter formula =ROW(T into the Formula Bar, then all table names are listed in the list box as below screenshot shown.
Note: Table names which have been modified won’t be listed out with this method.
List all table names with VBA code
If there are default table name and modified table name in your workbook. You can list them out together at once with the following VBA code.
1. Press the Alt + F11 keys to open the Microsoft Visual Basic for Applications window.
2. In the Microsoft Visual Basic for Applications window, click Insert > Module. Then copy below VBA code into the Module window.
VBA code: List all table names in Excel.
Sub ListTables()
'Updated by Extendoffice 20180503
Dim xTable As ListObject
Dim xSheet As Worksheet
Dim I As Long
I = -1
Sheets.Add.Name = "Table Name"
For Each xSheet In Worksheets
For Each xTable In xSheet.ListObjects
I = I + 1
Sheets("Table Name").Range("A1").Offset(I).Value = xTable.Name
Next xTable
Next
End Sub3. Press the F5 key or click the Run button to run the code.
Then a new worksheet named as Table Name is created with all table names listed out as below screenshot shown:
Related articles:
- How to display table or pivot table name in a cell in Excel?
- How to keep table expandable by inserting table row in a protected worksheet in Excel?
- How to reorder position of columns in table in Excel?
- How to hide zero value rows in pivot table?
Best Office Productivity Tools
Supports Office/Excel 2007-2021 and 365 | Available in 44 Languages | Easy to Uninstall Completely
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need Is Just A Click Away...
Supercharge Your Excel Skills: Experience Efficiency Like Never Before with Kutools for Excel (Full-Featured 30-Day Free Trial)
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day! (Full-Featured 30-Day Free Trial)
