How to Always Show Excel Table Names Using the Quick Access Toolbar or VBA

Why table names deserve to be seen​

Excel labels each new table Table1, Table2, Table3 and so on. Microsoft's guidance is that you can rename a table to make it easier to refer to, especially in a workbook with many tables. Microsoft's naming documentation also says a meaningful name beats a cell-range reference like C4:G36 for readability in formulas. Microsoft's accessibility tutorial points out that a descriptive name such as "EmployeeList" is more helpful than "Table1", and that it lets you jump to the table from the Name Box or the Go To command. It also says careful naming helps people who rely on screen readers.

Naming rules, per Microsoft's documentation:

• A name must begin with a letter, an underscore or a backslash.
• Spaces are not allowed. Use underscores or periods instead.
• A name can't match a cell reference such as R1C1.
• Names are limited to 255 characters.
• Names must be unique within the workbook, and Excel ignores upper and lower case when checking.

If you rename a table later, Microsoft says every use of that name in the workbook updates with it. Name Manager lists table names too, and its filter can show table names only.

To rename a table, click it and go to Table Design (Table Tools > Design in older builds) > Table Name. On a Mac, Microsoft says to use the Table tab.

Method 1: Put the Table Name box on the QAT​

This takes about 30 seconds.

1. Click inside any table.
2. Open the Table Design tab.
3. Right-click the Table Name box on the left.
4. Choose Add to Quick Access Toolbar.

Phillips reports that the box then shows the name of whichever table is selected, whatever ribbon tab you're on, and that you can rename the table from there. Microsoft's general QAT guidance confirms the right-click method for adding commands, and it says the QAT stays visible whichever ribbon tab is active. Microsoft's page doesn't specifically cover the Table Name box, so treat this as the author's workflow rather than a guarantee for every build. Microsoft also notes that some commands can't be added to the QAT in some versions, such as the Mac builds.

Method 2: Stamp the name on the worksheet with a macro​

This suits dashboards and shared workbooks where readers shouldn't have to hunt through the ribbon. Phillips dismisses the FORMULATEXT approach as clumsy because it needs an existing structured reference somewhere on the sheet.

Step 1: Create PERSONAL.XLSB if you don't have one​

Microsoft describes PERSONAL.XLSB as a hidden workbook stored on your computer that opens in the background whenever Excel starts. The macros inside are available in every workbook you open.

• Microsoft's documented route is to record a do-nothing macro from the Developer tab. Click Record Macro, then pick Personal Macro Workbook in the "Store macro in" box.
• The Developer tab is hidden by default, so you may need to enable it first.
• Phillips starts from View > Macros > Record Macro instead. The result is the same: choose Personal Macro Workbook, click OK, then click Stop Recording.
• Press Alt+F11, right-click VBAProject (PERSONAL.XLSB), and choose Insert > Module.

If you already have a personal workbook, open the VBA editor and add a module there, or use an existing one.

Step 2: Paste the macro​

Sub InsertTableName()
    Dim tbl As ListObject
    Dim target As Range
    Set target = ActiveCell
    On Error Resume Next
    Set tbl = target.Offset(1, 0).ListObject
    If tbl Is Nothing Then Set tbl = target.Offset(0, 1).ListObject
    On Error GoTo 0
    If tbl Is Nothing Then
        MsgBox "No table found below or to the right of the selected cell.", _
            vbInformation, "Table name"
        Exit Sub
    End If
    target.Value = tbl.Name
    target.Font.Color = RGB(128, 128, 128)
    target.Font.Italic = True
End Sub

Here is what the code does:

• It looks at the cell directly below the active cell, then at the cell directly to its right.
• If it finds a table in either place, it writes the table's name into the active cell in light-gray italics.
• If it finds nothing, it shows a message and stops.
• It uses the ListObject type, which is how Excel's object model represents a table. Microsoft's VBA reference lists Name among the ListObject properties.

The design is deliberate. Putting the label above or to the left of the table means the table can still grow downward and rightward. A label placed below or to the right would get absorbed as the table expands.

Step 3: Save and put the macro on the QAT​

• Save the module. When Excel asks about saving the Personal Macro Workbook, click Save. Microsoft says you're prompted to save both the workbook and the Personal Macro Workbook when you close. If you skip this, the macro disappears.
• Open the QAT drop-down arrow and choose More Commands.
• Under "Choose commands from", select Macros.
• Pick InsertTableName, click Add, then Modify to choose an icon.

Now select the cell directly above or to the left of a table and click the new button. The table's name appears as text.

Limits worth knowing​

• The label is static. It is plain text, not a live link. If you rename the table, rerun the macro. Phillips says renaming is uncommon once a workbook is set up, but that is his opinion. In shared workbooks that evolve, stale labels are a real risk.
• The search is narrow. The macro only checks the cell below and the cell to the right of the active cell. It won't find a table elsewhere.
• It needs desktop Excel with VBA. The macro won't run in Excel for the web. Macros are also a security topic. Microsoft blocks macros in internet-downloaded files by default, and organizations can set macro policy. Keep the code in your own Personal Macro Workbook. Don't loosen macro protections to make it work.
• Version differences. Ribbon labels vary between Excel builds. Phillips also mentions that Microsoft is rolling out a way to name a table when you create it. I couldn't verify its availability or which versions get it, so don't count on seeing it.

Which method should you use?​

Use the QAT method if you want to know which table you're in while you work. It is quick and needs no code. Add the macro if the name needs to be visible to other people on the sheet itself, as in a report or dashboard. Phillips uses both.

The general lesson is that Excel's defaults aren't fixed. A well-named table, a QAT button and a small macro can make a workbook easier to read, for you and for whoever inherits it. That is the "customize Excel instead of just accepting it" approach Phillips recommends.