Tables: Introduction

Section Goals

At the end of this section you will know how to create a table from a list and be able to identify the various sections of a table.

https://medium.com/codex/the-ultimate-guide-to-excel-tables-1236d826c897

Convert List to Table

We usually start with a list or a partial list and then convert it to a table. There are two main ways to convert a list to a table. They both start with us clicking on any cell in the list:

Home > Format as Table - In Excel Home tab click the Format as Table icon in the Styles group. Select a table format then when asked by the Create Table dialog (shown at right) check if the list had column headings.
CTRL-T or CTRL-L - Tables were once known as Lists. They are still known In VBA as a ListObject. So originally we would press and hold the CTRL key while typing L to convert a list to what is known now as a table. When Microsoft renamed Lists to Tables they made CTRL-T the keyboard short cut. They both do exactly the same thing and either method works now.

Table Course Outline

Table Advantages
Introduction to Tables
Working With Tables
Structured References

When we convert a list to a table and have selected any cell in that table, Microsoft as a 'contextual' menu called Table Design (shown below).

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.