Home > Macros > Basics > Table Name Get Excel Table Name or Pivot NameIn 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 CellIf 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
|
Show Table Name - Excel FunctionFor 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..
The formula details are in the next section. |
Formula 1 - Link to Excel TableTo 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:
The formula result in cell A1 shows the column name from the Excel table's header cell -- Day. |
Formula 2 - Get Excel Table NameNext, 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.
In this example, I built this formula in cell C1, and here is the completed formula:
In this formula:
In the sections below, there are detailed steps and explanations for building this formula. WARNING
Build Excel Table Name Formula - Step by StepTo 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 Formulaare the steps:
The formula result in cell C1 shows the formula that's in cell A1 |
2) Extract Table Name from FormulaTo extract the table name from the FORMULATEXT result, we'll use two more Excel functions:
In this screen shot, I've highlighted the text that we want to extract -- tblMth 2a) Use the FIND FunctionTo 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:
Tip: There are more FIND function examples on this page: Excel FIND Function Examples Add the FIND FunctionIn cell C1, make the following change to the formula, to add the FIND function:
Then press Enter, to see the formula result The revised formula returns the position of the square bracket in the formula text string:
|
2b) Use the MID FunctionNext, 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 SyntaxThe MID function has the following syntax for its arguments:
The items in blue text will be added to the existing formula, in the next section.
Add the MID FunctionIn cell C1, make the following change to the formula, to add the MID function:
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:
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 - UDFIf 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 CodeAnother 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 |
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 FunctionAfter 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.
Excel will automatically add the closing bracket, and the formula displays the table or pivot table information. |
Formula ResultsIn the screen shot below, the formula referred to a pivot table cell, and you can see the formula results in cell B1.
There are three sections in the results, underlined and numbered in the screen shot.
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 ExampleThanks 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:
Show Name UDF ModificationIn 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:
Khushnood changed it, to use the table name oly
|
Macro to Insert SUBTOTAL FunctionThe 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:
The macro uses the:
Insert SUBTOTAL Formula Macro CodeHere 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. |
Sub CreateSubTotalFormulas() Dim c As Range Dim strTbl As String Dim strColHd As String Dim strSTF As String Dim lRowOff As Long Dim lHRowOff As Long Dim iFN As Integer Set c = ActiveCell iFN = 3 'Count lRowOff = 1 lHRowOff = 3 strTbl = c.Offset(lRowOff, 0).Value strColHd = c.Offset(lHRowOff, 0).Value strSTF = "=SUBTOTAL(" & iFN & ", " _ & strTbl & "[" _ & strColHd & "])" c.Formula = strSTF End Sub
Change Macro ShortcutIn 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:
|
Table Column Subtotal UDFAnother 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 ArgumentsIn the UDF arguments, there are 3 arguments:
NOTE: The target cell must be in the header row of a named Excel table Use the ColSubT UDFTo use the ColSubT UDF on an Excel worksheet, follow these steps:
Copy and Paste FormulasAfter 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 ExamplesIn the screen shot below, the ColSubT is used in 3 cells:
ColSubT UDF CodeTo 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
|
Related Links |
Last updated: November 27, 2022 11:50 AM