How to Show Excel Table Name on the Sheet

If you have an Excel workbook with lots of tables and pivot tables, it can be hard to remember what they’re named, or what source data they’re using. To help you keep track of them, AlexJ shared the User Defined Function (UDF) that he uses in his files. With this code in your file, just add a formula, click on a cell, and show the pivot table name or Excel table name on the sheet.

The code is shown below, and there is also a link for downloading a sample file with the code installed in it.

Copy the Show Excel Table Name Code

To use this UDF in your file, copy the code shown below, and paste it into a regular code module in your workbook. There are instructions on my website, if you’re not sure how to do that.

Here is the code from AlexJ, to show an Excel table name on the sheet:

Function getObjName(rng As Range) As String
  Dim tbl As ListObject
  Dim pt As PivotTable
  Dim PivotName As String
  Dim TableName As String
  Dim qName As String
  Dim ptsName As String
  
  On Error GoTo Err_NoTable
  Set tbl = rng.Cells(1).ListObject
  TableName = "Table [" & tbl.Name & "]"
  
  On Error GoTo Err_NoQuery
  qName = "[" & _
    tbl.QueryTable.WorkbookConnection.Name _
      & "]"

Res_Table:
  TableName = TableName & qName
        
Res_Pivot:
  On Error GoTo Err_NoPivot
  Set pt = rng.Cells(1).PivotTable
  PivotName = "Pivot [" & _
    rng.Cells(1).PivotTable.Name & "]"
        
Res_PTS:
  On Error GoTo Err_NoPTSource
  ptsName = "[" & pt.SourceData & "]"
        
Res_PTName:
  PivotName = PivotName & ptsName
        
XIT:
  getObjName = TableName & PivotName
  Set tbl = Nothing
  Set pt = Nothing
  Exit Function
        
Err_NoTable:
  'Not a table check for Pivot
  TableName = ""
  Resume Res_Pivot
  'Resume
        
Err_NoQuery:
  'No Query on the table
  qName = ""
  Resume Res_Table
        
Err_NoPivot:
  'Not a Pivot Table - exit
  PivotName = ""
  Resume XIT
        
Err_NoPTSource:
  'No Pivot source identified
  ptsName = ""
  Resume Res_PTName

End Function

How to Use Show Table Name Function

After you paste the Show Excel Table Name UDF code into your workbook, it’s ready to use.

There are quick steps shown below, and more details and examples on the Show Excel Table Name or Pivot Table Name page on my Contextures website.

1. First, select the cell where you want to see the table name or pivot table name.
2. Next, type an equal sign and the UDF name, followed by an opening bracket:  =getObjName(
3. Then, click on a cell in the table or pivot table, to refer to that cell
4.  Finally, to complete the formula, press Enter

Excel automatically adds the closing bracket, and the formula displays the table or pivot table information.

The Formula Results

In the screen shot below, you can see the formula results in cell B1.

• NOTE: If the table name changes, the formula will NOT update immediately. It will update when the workbook calculates.

There are three sections in the formula results, and I’ve underlined and numbered them in the screen shot.

1. The type of object – Table or Pivot
2. The object’s name
3. The object’s source name, if available

Formula Result – Named Table

In the next screen shot, the formula refers to a cell in a named table. The data is typed into the table, so there isn’t a source name available.

Formula Result – Empty String

If the formula refers to a cell that isn’t in a named Excel table or a pivot table, the formula result will be an empty string.

Video: Excel Named Table

When you work with lists in Excel, use the built-in Table feature, to make it easy to sort and filter your data. This short video shows the simple steps

Download the Sample File

To download the sample file, and test the Show Excel Table Name code, go to the Show Excel Table Name or Pivot Table Name page on my Contextures website. In the download section, look for Show Table or Pivot Name – UDF.

The zipped file is in xlsm format, and contains a macro – the Show Excel Table Name UDF code.

___________