Naming Tables

Looking at the ribbon above, we can see on the left, a box labeled Table Name:. In that box is the text: Table2. When we convert a list to a table Excel assigns a default name. Excel labels the first table: Table1. It labels the next table: Table2 and so on. These names are meaningless so we should rename the new table something descriptive. To rename the table simply type over the name in the Table Name: box. When we name a table we must follow a few rules:

• Each table's name must be unique within the workbook. Table names are not case sensitive so tblSales is the same as TBLSALES.

• Table names must start with a letter, underscore (_), or backslash (\). The rest of the name can include letters, numbers, periods, and underscore characters (Note! Space is NOT allowed). 

• We cannot name tables R or C because those are reserved names for selecting the row or column for the active cell.

• Table names cannot look like any cell reference in either R1C1 notation or A1 notation. 

• Tables names must not exceed 255 characters

I prefer to start all tables with the prefix tbl. In the animated picture (GIF) below is a table I named tblSectors.

Table Sections

When a list is converted to a table, it gains several named sections. Each column is a named region as well as the entire table (#All) , the table's column headings (#Headers), the table's total row (#Totals) and the data existing above the total row and below the header row (#Data). 

Some of the sections can be turned on and off. In the GIF above we can see #HEADERS and #TOTALS being turned on and off by checking the associated options in the Table Style Options group of the Table Design tab.

Named Regions

In this section we will explore the named regions: #Totals, #Headers, #Data, and #All.

#Totals

To get the Total Row, check that box in the Table Design tab > Table Style Options group. When we have a total row, we can refer to it in formulas using the structured reference <TableName>[#Totals]. This table is named tblSectors. So if we want to refer to tblSector's total row we would use the name: tblSectors[#Totals].

We can be even more specific and zero in on the total in Demand B's column using the name: tblSectors[[#Totals],[Demand B]].

In the animated picture above, we can see how we change the total row's function to sum or count a column's numbers. In the drop down we also see several other options. When select any of these options, with the exception of "More Functions...",  Excel inserts a formula using the SUBTOTALS() function. 

SUBTOTALS() has more options than are in that list. For a complete list of options, click: SUBTOTALS(). SUBTOTALS() does something very special. It only performs sums, counts, etc. on visible rows. So if we apply a filter or slicer to a table, SUBTOTAL() ignores hidden rows.

While Excel will default the last column to apply the SUBTOTAL() to, we will find the drop down available on every column in the total row.

#Headers

Every column in the table is named after the column headings in the table's header row. We can refer to the header row in formulas using the structured reference <TableName>[#Headers]. This table is named tblSectors. So if we want to refer to tblSector's header row we would use the name: tblSectors[#Headers].

NOTE! I often use a formula to create an index number for items in a table. That formula references the header row but because the formula cell is in the table, we don't have to spell out the table name when we enter the formula. Here is how I enter the formula to automatically create an index (row number) for each item: =ROW()- ROW([#Headers])

We can be even more specific and zero in on the header in Demand B's column using the name: tblSectors[[#Headers],[Demand B]]. 

Column headings must follow a few rules:

• • Each column name must be unique within the table. Column names are not case sensitive so Name is the same as NAME.

  • We cannot use formulas in column headings

  • Some characters in a column heading will require extra brackets in some situations. Those characters are: Tab, Line Feed, Carriage Return, Comma, Colon, Period, Left or Right Bracket, Pound Sign, any kind of Quotation Mark, Left or Right Brace, Dollar Sign, and any arithmetic operator or comparison symbols. 

  • Some characters require an 'escape' character when used in formulas. The 'escape' character is the single quote (‘). The characters, if used in a column heading, that require the escape character when used in formulas are: Left or Right Bracket, Pound Sign, and Single Quote.

NOTE! While we cannot use formulas as column headings, we can turn off the header row (which will error my automatic index formula) and add formulas above columns. This will have the appearance of normal headings but will not be used as column names. I do not recommend this practice unless you are Peter Bartholomew working on an engineering project.

#Data

If we want all of a table's data (no headers and no total row), we can refer to its #Data name: tblSectors[#Data]. This is identical to referring to just the table's name: tblSectors. 

#All

If we want the entire table, (headers, data, totals), we can refer to its #All name: tblSectors[#All].