Home > Macros > Basics > Table Name

Get Excel Table Name or Pivot Name

In a comples Excel workbook, with lots of Excel tables and pivot tables, it can be hard to remember what they’re named, or what source data they’re using.

To help keep track of things, you can show an Excel table name or pivot table name in a worksheet cell, by using one of the formulas shown below. For example, this formula shows that the referenced cell is in a Pivot Table, named PivotTable1, based on source data named SalesData.

Show Table Name in Cell

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 tables and pivot tables, you can use one of the methods below, to show the names in a cell on the worksheet:

-- 1) Show Table Name - Excel Function - for Excel 2013 or later (table names only)

-- 2) Show Table or Pivot Name - User Defined Function (UDF) - Excel VBA code

Recommendations

1. If you only need to show Excel table names, I recommend using the Excel Function method (Excel 2013 and later).
   • It uses built-in Excel functions, so you can save your files in xlsx format, instead of a macro-enabled format, such xlsm or xlsb
   • You'll avoid potential problems that some User Defined Functions cause, such as slower calculation speed in large workbooks
2. To show pivot table names, or in older versions of Excel, use method 2 - User Defined Function.
   • This method requires that you save the workbook in a macro-enabled format.
   • Those file formats are sometimes blocked by mail servers

Show Table Name - Excel Function

For a simple way show an Excel table name, in Excel 2013 or later, you can use the FORMULATEXT function, combined with other functions.

This method uses two cells with formulas, highlighted in this screen shot, and does NOT require any VBA code in the workbook..

• Cell A1 - Link to heading cell in Excel table
• Cell C1 - Formula to extract table name from cell A1

The formula details are in the next section.

Formula 1 - Link to Excel Table

To get the name of an Excel Table on the worksheet, the first step is to create a link to one of its heading cells.

In this example, the table starts in cell A3, and here are the steps to create the link:

• Select cell A1, where you'll create the link formula
• Type an equal sign, to start the formula
• Click on the first heading cell in the Excel table - cell A3, in this example
  • The link automatically uses a structured table reference, which includes the table name, cell location (headers), and column name
  • =tblMth[[#Headers],[Day]]
• Press Enter, to complete the formula.

The formula result in cell A1 shows the column name from the Excel table's header cell -- Day.

Formula 2 - Get Excel Table Name

Next, you'll add a formula to extract the Excel Table name from the formula in cell A1. This formula uses the FORMULATEXT function, which is in Excel 2013, and later versions.

• Tip: See more FORMULATEXT examples and details on ways to use this helpful Excel function.

In this example, I built this formula in cell C1, and here is the completed formula:

• =MID(FORMULATEXT(A1),2,FIND("[",FORMULATEXT(A1))-2)

In this formula:

• FORMULATEXT function gets the text string of the formula in cell A1
• FIND function locates the first square bracket
• MID function extracts table name, starting at 2nd character, ending at square bracket

In the sections below, there are detailed steps and explanations for building this formula.

WARNING

• If you change the table name, the formula result will NOT update immediately.
• The formula will update when the workbook calculates
• You can press the F9 key to force a calculation.

Build Excel Table Name Formula - Step by Step

To see how the formula works, you can build it step-by-step:

--1) Get the Cell Link Formula

--2) Extract Table Name from Formula

These steps are shown in detail, in the sections below.

1) Get the Cell Link Formula

are the steps:

• Select cell C1, where you'll create the formula
• Type an equal sign, to start the formula
• Next, to see what the FORMULATEXT function does, enter this formula:
  • FORMULATEXT(A1)
• Press Enter, to complete the formula.

The formula result in cell C1 shows the formula that's in cell A1

2) Extract Table Name from Formula

To extract the table name from the FORMULATEXT result, we'll use two more Excel functions:

• FIND - find the first square bracket in the formula, which is immediately after the table name
• MID - get the text, starting after the equal sign, and ending before the square bracket

In this screen shot, I've highlighted the text that we want to extract -- tblMth

2a) Use the FIND Function

To extract the table name, first we'll use the FIND function, to get the location of the square bracket, that's at the end of the table name.

The FIND function has the following syntax for its arguments:

• FIND(find_text,within_text,[start_num])
  • find_text - what we're looking for --"["
  • within_text -- text string to look in -- FORMULATEXT(A1)
  • (optional) start_num is omitted, the search starts at character 1 -- we can omit this argument

Tip: There are more FIND function examples on this page: Excel FIND Function Examples

Add the FIND Function

In cell C1, make the following change to the formula, to add the FIND function:

• =FIND("[",FORMULATEXT(A1))

Then press Enter, to see the formula result

The revised formula returns the position of the square bracket in the formula text string:

• character number 8

2b) Use the MID Function

Next, we'll use the MID function, to extract the Excel table name from the formula text string.

The table name starts at the 2nd character, and ends before the square bracket (character 8)

MID Function Syntax

The MID function has the following syntax for its arguments:

• MID(text,start_num,num_chars)
  • text -- text string to work with --FORMULATEXT(A1)
  • start_num --position of first character to extract
    • 2 (immediately after equal sign)
  • num_chars -- number of characters to return
    • square bracket position -- FIND("[",FORMULATEXT(A1))
    • minus the start position -- - 2

The items in blue text will be added to the existing formula, in the next section.

• Tip: A video shows another example with FIND and MID on this page: Reverse First and Last Names with Formula

Add the MID Function

In cell C1, make the following change to the formula, to add the MID function:

• =MID(FORMULATEXT(A1), 2, FIND("[",FORMULATEXT(A1))-2)

Then press Enter, to see the formula result in cell C1

The revised formula returns the table name from the formula text string in cell A1:

• tblMth

I've put a border on cell C1, and added light grey fill colour, to make the cell stand out on the worksheet.

Show Table or Pivot Name - UDF

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 created a User Defined Function (UDF) that he uses in his files.

In the sections below, see how to:

-- 1) Get the Show Name UDF Code

-- 2) Use the Show Table Name UDF

Get the Show Name UDF Code

Another way to show an Excel table name on a worksheet, or a pivot table name, is with a User Defined Function (UDF).

NOTE: A UDF is created with Excel VBA code, so be sure to save your workbook in a macro-enabled format, such as xlsm or xlsb, if you use this method.

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 the Copying Code to a Workbook page, if you’re not sure how to do that.

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

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. You can follow the steps below, to show the information about any table or pivot table in your file

In this example, there are a couple of empty rows above the pivot table, so I added the formula there, in cell B1.

• Select the cell where you want to see the table name or pivot table name.
• Type an equal sign and the UDF name, followed by an opening bracket: 
  • =getObjName(
• Then, click on a cell in the table or pivot table, to refer to that cell
• To complete the formula, press Enter

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

Formula Results

In the screen shot below, the formula referred to a pivot table cell, and you can see the formula results in cell B1.

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

There are three sections in the results, underlined and numbered 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

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.

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.

Show Name UDF Example

Thanks to Khushnood Viccaji, who sent this example of how the Show Name UDF helps him manage a large set of Excel tables.

Here's a slightly-edited version of Khushnood's comments about his example:

• I've been working on a project that requires me to work with dozens of Tables in Excel.
• Of course, this process has been made far easier for me because of one of your UDFs (getObjName) which I use frequently.
• However, during the process of setting up these multiple tables, each SUBTOTAL formula had to be re-written / edited to address the relevant table + column name.
• To save time, I decided to insert the function using a VBA macro.

Show Name UDF Modification

In row 2 on the worksheet, above any column where a SUBTOTAL formula is needed, Khushnood uses the UDF to return the table name.

In his workbook, Khushnood changed one line in the Show Name UDF code.

Instead of this line, which combines the table name with other text:

• TableName = "Table [" & tbl.Name & "]"

Khushnood changed it, to use the table name oly

• TableName = tbl.Name

Macro to Insert SUBTOTAL Function

The workbook also has an Excel VBA macro that inserts a SUBTOTAL formula on the worksheet.

The macro code is shown in the next section, and it's also in the sample file that you can download.

To use the macro:

• Select the cell above the UDF formula cell
• Press the macro's keyboard shortcut - Ctrl+Shift+F

The macro uses the:

• table name from the cell 1 row down from the active cell
• column heading in the cell 3 rows down from the active cell
• The first argument is 3 (Count)

Insert SUBTOTAL Formula Macro Code

Here is the code that Khushnood uses to insert SUBTOTAL formulas, quickly and easily.

Note: You can change the settings in the code, such as the function number (lFN) for the SUBTOTAL function.

Change Macro Shortcut

In the Show Name Formula UDF Example workbook, I've set the Insert Subtotal macro to have a custom keyboard shortcut - Ctrl+Shift+F

You can change that shortcut to something different, if you follow these steps:

• While the sample file is open, go to the Excel Ribbon, and click the View tab
• At the far right, click the Macro command
• When the Macro dialog box opens, click on the CreateSubTotalFormulas macro name, in the macro list
• At the bottom right, click the Options button

• Next, in the Macro Options window, delete the F in the Shortcut key box
• Then, press the Ctrl key, or press Ctrl+Shift, and type a letter to use as the keyboard shortcut
  • Tip: I usually press Ctrl+Shift, with a letter, to avoid conflicts with any existing built-in Excel shortcuts.
• Click OK, to save the revised keyboard shortcut.

Table Column Subtotal UDF

Another way to calculate a Subtotal for a table column is with a User-Defined Function (UDF).

The UDF shown below, named ColSubT, uses programming to get a table name, table column number, and subtotal for that column.

ColSubT Arguments

In the UDF arguments, there are 3 arguments:

1. Fn - function number for SUBTOTAL function, e.g. 3 = Count
2. OffR -(optional) number of rows to offset from formula cell
• If omitted, default of 1 row is used
3. OffC - number of columns to offset from formula cell
   • If omitted, default of 0 columns is used

NOTE: The target cell must be in the header row of a named Excel table

Use the ColSubT UDF

To use the ColSubT UDF on an Excel worksheet, follow these steps:

• Select the cell where you want the table column subtotal result
• Type an equal sign, UDF name and open bracket: =ColSubT(
  • Note: When using a UDF, the funtion arguments do not appear in a tooltip
• Next, type the Subtotal function number that you want to use
  • e.g. 3 for count, or 9 for sum
• (optional) Next, type a comma, and the number of rows to offset, e.g. 2
  • If omitted, default (1) is used
• (optional Next, type a comma, and the number of columns to offset, e.g. 1 or -1
  • If omitted, default (0) is used
• Finally, type a closing bracket, and press Enter, to complete the formula

Copy and Paste Formulas

After you add ColSubT formulas to the worksheet, you can copy and paste those formulas in other locations.

BE SURE that the target cell for each formula is in the heading cell of a named Excel table, and is subtotalling the correct column.

ColSubT UDF Examples

In the screen shot below, the ColSubT is used in 3 cells:

• Cell B2: =ColSubT(9,2,1)
  • Sum function, 2 rows down, 1 column right)
• Cell G1: =ColSubT(9,3)
  • Sum funstion, 3 rows down, (default - 0 columns right)
• Cell G3: = ColSubT(3)
  • Count function, (defaults - 1 row down, 0 columns right)

ColSubT UDF Code

To use the table column subtotal UDF (ColSubT) in your workbook, copy and paste the code below, onto a regular code module.

NOTE: Be sure to save your workbook in a macro-enabled format, such as xlsx or xlsb.

Function ColSubT(Fn As Integer, _
    Optional OffR As Integer = 1, _
    Optional OffC As Integer = 0) _
      As Double
'Fn - function number for SUBTOTAL
'OffR - Rows offset
'OffC - Columns offset
'target cell must be table heading
'   for column to subtotal
  Dim c As Range
  Dim rng As Range
  Dim tbl As ListObject
  Dim rngInt As Range
  Dim colnum As Long
  Dim col As Range
  On Error GoTo Err_Handler
  
  Set c = ActiveCell
  Set rng = c.Offset(OffR, OffC)
  Set tbl = rng.ListObject
  On Error Resume Next
  Set rngInt = Intersect(rng, _
      tbl.HeaderRowRange)
  On Error GoTo Err_Handler
  If rngInt Is Nothing Then
    MsgBox "not a header cell"
    GoTo exit_Handler
  End If
  
  colnum = Application _
    .WorksheetFunction _
      .Match(rng.Value, _
        tbl.HeaderRowRange.Cells, 0)
  
  Set col = tbl.DataBodyRange _
      .Columns(colnum)
  
  ColSubT = Application _
    .WorksheetFunction _
      .Subtotal(Fn, col.Cells)

exit_Handler:
  Set c = Nothing
  Set rng = Nothing
  Set tbl = Nothing
  Set col = Nothing
  Exit Function

Err_Handler:
  ColSubT = 0
  Resume exit_Handler

End Function

Get the Sample Files

1. Show Table Name - Formula -- Use built-in Excel formulas to show Excel table name on the worksheet. For Excel 2013 or later. The zipped workbook is in xlsx format, and does not contain any macros.
2. Show Table or Pivot Name - UDF -- With this User Defined Function (UDF) from AlexJ, you can show an Excel table's name, or a pivot table's name and source. The zipped workbook is in xlsm format, and it contains macros shown on this page.
3. Show Name UDF Example -- Example from Khushnood Viccaji uses the Show Name UDF, with one row modified. After that, run a macro to add SUBTOTAL formulas for table columns, or use the ColSubT UDF. The zipped workbook is in xlsm format, and it contains macros shown on this page.

Related Links

Create & Fix Excel Tables

SUBTOTAL Function

Excel Table Slicers

Macro - Copy Excel Table Rows