Excel Table List: Find and Manage Your Tables

To see the tables in an Excel workbook, select a table and open the Table Design tab to view its name; Excel does not provide a single built-in worksheet that lists every table. If you mean a list of values stored in a table, create a table with Insert > Table, then sort, filter, or use its column as a source for a drop-down list.

What “Excel table list” can mean

The phrase can describe two different things. You might want an inventory of the named table objects in a workbook, such as Sales, Products, and Staff. Or you might mean a list of records or choices arranged in an Excel table—for example, a product list or a category list. Excel supports both, but the steps are different.

An Excel table is a structured range of cells with a header row and built-in features such as filters, consistent formatting, and automatic expansion when you add data. It is more than a visual style. Table names and column names can also be used in formulas, making references easier to understand than ordinary cell addresses.

Create a table from a list of data

For a new table, put one field name in each column header and place one record on each row. Avoid blank header cells and merged cells within the data. Click inside the range, then choose Insert > Table. Check the range shown in the dialog and select My table has headers if the first row contains field names. Choose OK.

You can also use Home > Format as Table to apply a style and create a table. Confirm the range and header setting in the dialog. Once created, Excel adds filter arrows to the headers and displays the Table Design tab when a cell in the table is selected. If you add data directly below the table, Excel commonly expands the table to include it; check the colored outline or Table Design > Resize Table if the new row was not included.

For example, a small sales table could have the headers Date, Product, and Amount, followed by one sale per row. If the table is named Sales and its amount column is Amount, with sales of 125, 80, and 95 in that column, enter this in a cell outside the table to return 300:

With the sales records in a table named Sales and the amounts in its Amount column, use =SUM(Sales[Amount]). The total is 125 + 80 + 95 = 300. The structured reference includes the table’s data rows and adjusts as the table changes.

Find a table’s name and change it

Click any cell in the table. In Excel for Microsoft 365 on Windows, open Table Design and find the Table Name box near the left side of the ribbon. The selected table’s name appears there. Type a new name and press Enter to change it. The Table Design tab is contextual, so it will not appear when the active cell is outside a table.

Table names must be unique within the workbook, cannot contain spaces, and must begin with a letter, an underscore, or a backslash. Names such as Sales2026 or Product_List are suitable; use an underscore instead of a space. Excel may assign a default name such as Table1 when a table is created. Descriptive names make formulas and later maintenance clearer.

Excel for Mac also shows table-specific controls when you select a table, though the ribbon layout can differ by release. Excel for the web has table controls too, but some advanced workbook and macro features are unavailable there. The Table Design tab name and available commands may vary across versions, so use the table-specific ribbon controls shown for your installation.

Get one list of all table names in a workbook

Excel’s normal ribbon interface has no universal “list all tables” command. To inspect a particular table, select it and read its name in Table Design. To make an inventory of every table in a workbook, a short VBA macro can write each table name and its worksheet to a blank sheet. This is useful when a workbook has many tables or when documenting a file.

In desktop Excel, first add a blank worksheet and select it. Open Developer > Visual Basic, choose Insert > Module in the editor, and paste the code below. Run ListWorkbookTables from the VBA editor or Excel’s macro controls. It writes headings in A1:B1 and the results beneath them on the active sheet; use a blank sheet because existing values in those cells will be overwritten.

Sub ListWorkbookTables()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim outputRow As Long

    ' Write the inventory on the active worksheet.
    ActiveSheet.Cells(1, 1).Value = "Table name"
    ActiveSheet.Cells(1, 2).Value = "Worksheet"
    outputRow = 2

    For Each ws In ThisWorkbook.Worksheets
        For Each tbl In ws.ListObjects
            ActiveSheet.Cells(outputRow, 1).Value = tbl.Name
            ActiveSheet.Cells(outputRow, 2).Value = ws.Name
            outputRow = outputRow + 1
        Next tbl
    Next ws
End Sub

Save a copy of the workbook before running a macro. VBA macros cannot be undone with Ctrl+Z, and this macro writes into the active sheet. It does not create a live inventory: run it again after adding, renaming, or deleting tables. VBA is available in desktop Excel, not Excel for the web; the exact editor menus can differ between Windows and Mac.

Use a table column for a drop-down list

A table can also supply choices for data entry, such as departments, product types, or status labels. Put the choices in a single table column, then create a workbook-level name that refers to that column. For example, if a table is named Products and has a Category column, open Formulas > Name Manager, choose New, enter CategoryList as the name, and set Refers to to =Products[Category]. This reference points to the table’s data column rather than its header.

Select the cells that should have the drop-down and choose Data > Data Validation. On the Settings tab, set Allow to List and enter =CategoryList in the Source box, then choose OK. The named range gives Data Validation a convenient reference to the table column. When you add categories to the table, the source can expand with it. If the list contains duplicates or blank cells, those may also affect the choices shown; keep the source column clean if users need a concise menu.

Data Validation menus and support can differ in Excel for the web and Mac. If a structured reference is rejected directly in the Source box, use a defined name as described above. A drop-down does not automatically prevent every invalid edit in every workflow, so choose the error-alert settings in Data Validation if Excel should warn users when they enter a value outside the list.

Sort, filter, and expand table data

Each table header has a filter button. Select it to sort values, filter to selected entries, or apply options suitable for that data type. For instance, a sales table can be filtered to show one product, while a date column can be filtered to a period. To clear an individual column’s filter, open that header’s menu and choose the clear-filter option; to clear filters across the sheet, use Data > Sort & Filter > Clear.

Tables also help keep formulas consistent. When you enter a formula in a table column, Excel can fill it down as a calculated column. A total row can be enabled from Table Design > Total Row, then configured using the cell’s menu to show a sum, count, or another available calculation. A table’s style and filter controls do not change the underlying values, so check the data itself when a result looks wrong.

To turn a table back into an ordinary range while keeping its cell values, select a cell in it and choose Table Design > Convert to Range, then confirm. The data remains, but table features such as structured references and automatic table expansion no longer apply in the same way. Removing a table is different from deleting its contents: check the confirmation dialog before proceeding.

Common table-list problems

The Table Design tab is missing: select a cell inside the table. Clicking nearby cells or a chart will not show table-specific controls.
A new row is not included: paste or type immediately below the table, or use Table Design > Resize Table to set the correct range. A blank row between the table and new data can prevent automatic expansion.
A formula does not recognize a table name: check the exact name in Table Design and confirm that the column header is spelled as written. Spaces in column headers are allowed, but the structured reference must match the header.
A name is rejected: remove spaces, avoid a name that looks like a cell reference, and make sure it is not already used by another table in the workbook.
A drop-down does not update as expected: confirm the defined name refers to the table column, and check for blanks, duplicates, or a misspelled table or column name.

Another common misconception is that formatting a range with colors automatically makes it an Excel table. A styled range may look similar, but it will not necessarily have table headers, filters, structured references, or the Table Design controls. Use Insert > Table or Home > Format as Table when you need an actual table object.

When a table is the right choice

Use a table when your data is a rectangular set of records with one header row and you expect to sort, filter, add rows, or refer to whole columns in formulas. Tables are particularly useful for recurring lists such as orders, inventory, and contact records. They make formulas easier to read and can keep formatting and calculated columns consistent as data grows.

A table is not always necessary. A short, fixed list used only for a one-time calculation may be fine as a normal range. PivotTables, charts, and Power Query can also use tabular data, but they serve different purposes: PivotTables summarize, charts visualize, and Power Query imports or transforms data. In many cases, a well-structured Excel table is a good starting source for those tools rather than a replacement for them.

Frequently Asked Questions

Does Excel have a list of all tables?
Not as a standard worksheet view or one-click ribbon command. Select tables individually to see their names, or use VBA in desktop Excel to generate an inventory of table names and worksheet locations.

Where do I find the table name in Excel?
Select a cell inside the table and look in the Table Design tab’s Table Name box. The tab appears only while a table cell is selected.

Can I make a drop-down list from an Excel table?
Yes. Define a name that refers to the table column, then use that name as the Data Validation list source. This is a practical way to keep choices connected to a changing table.

How do I add a new item to an Excel table?
Type in the first row immediately below the table, or add a row within the table. If Excel does not expand it, use Table Design > Resize Table and include the new row.

Can Excel for the web run the table-list macro?
No. Excel for the web supports many table features, but it cannot run VBA macros. Open the workbook in desktop Excel to run the macro, or inspect table names individually using the table controls.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.