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.
Recommended Excel Productivity Tools
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.
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.
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 Sub
3. 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:
- 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?