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.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
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?