Excel Tables

What are Excel Tables?

Excel Tables, or just Tables, are tables superimposed over regular cells.

Excel Tables are assumed to be containing structured data.

Both tables and individual table columns are named and intended to be referenced in formulas by those names.

As they are typically used to load structured data from an external source into Excel, and that external data is typically accessed from Excel using the Data ribbon and then loaded into Table, they are also sometimes called Data Tables, even if that term is technically reserved for another object in Excel.

However, any existing range of cells can be formatted as a Table.

 

How to create or remove an Excel Table

We can select any desired cell range and apply Format as Table to that range:

Excel Table, Format as Table

Once we format our cell range as a table, we can position ourselves in any part of that table and a new ribbon, Table Design will become available:

Excel Table, Table Design

In general, in order to modify an existing Table in any way, you will have to be positioned inside of that table.

 

It is also worth distinguishing between the table itself and the table style.

Excel Tables are by default rather colorful, and the rows are banded so that the data is better distinguishable.

Those styles can be modified in the Table Design ribbon, Quick Styles (style can be changed), and Table Style Options (banding of rows can be turned off, banding of columns can be turned on, first or last column can be bolded):

Excel Table, Table Design, Table Style Options & Quick Styles

 

However, if we want to remove our table, this is can be done with the help of the Convert to Range button. Data is converted to a range but the formatting remains.

Vice versa, if we remove the formatting, the table itself will remain.

Those are two separate actions, and both can be done without the other one being done.

Hence, in order to completely remove the Table, we have to do both:

Excel Table, Table Design, remove style & Convert to Range

 

How to remove Excel Table connections

When data is loaded into an Excel Table from an external source, we have to distinguish between the connection to the external data source on the workbook level and the loading of that data into the Table itself.

We can stop future updates of Excel Tables (data refresh) with the help of the Unlink button:

Excel Table, Table Design, Unlink

The Unlink button will not convert the table to a cell range or remove the formatting.

However, if we are converting a table to a range, the Convert to Range action described above will by definition also stop future updates as Table will no longer exist.

In both cases, the now inactive connection to the external data source will remain in our workbook. If it is not needed, it can be removed in Queries and Connections:

Excel Table, Table Design, remove connection

 

Excel Table Names

Generic Table names are generated when Tables are created (Table1, Table2, etc.)

Those names are visible in the Table Design ribbon. They are easy to change, and in principle, should be changed in order to represent the data available:

Excel Table, Table Name

 

All available Tables in a workbook will be present in the Name Box. This can be used in order to navigate to a table:

Excel Table, Table Name, Name Box

Once a table name is selected, we are led to the desired table:

Excel Table, Table Name, jump to table

 

Table column names are also generated when Tables are created.

If the “My table has headers” option is selected, as it was in our example, they are taken from the first row of selected cells.

If the My table has headers” option is not selected, generic names are given (Column1, Column2, etc.).

In principle, if a table is used to load external data, column names should not be messed with. However, if needed, they can be changed directly in the Header Row:

Excel Table, Table Name, column name

Column Names in Excel Tables will always be text stings.

If a date or any other type of number is being used as a column name, it will be converted to a text string. Those numbers formatted as text can cause issues with matching (lookup).

 

Excel Table Components

Each Excel Table will contain:

  • Data, in one or more rows.
  • Headers, with Header Row being turned on by default, can be turned off if desired.
  • Totals, with Total Row being turned off by default, can be turned on if desired.

Filter buttons will be located in the Header Row, turned on by default, and can also be turned off if desired.

Total Row can actually be customized and totals can be created for all columns. Each Total Row cell can contain a function calculating some kind of total.

The default options offered are based on the SUBTOTAL function (filtered out values will not be accounted for in the total row), but any function can be manually entered:

Excel Table, Header Row, Total Row, Filter Button

 

Filter buttons will offer all of the typical Sort & Filter options:

Excel Table, Total Row, Filter button

Note the changes in the totals.

 

Filtering can also be done via slicers:

Excel Table, Total Row, Filter Slicer

Again, note the changes in the totals.

Also note that, when the slicer is in use, the filter button is also active.

 

Dynamically updated Calculated Columns

We can insert (delete, select) table columns by positioning ourselves on the desired location in the table, right-clicking and selecting Insert Table Column (to the Right):

Excel Table, insert table column

 

We can manually rename those table columns or enter values into them as we please:

Excel Table, insert value in table column

 

It is different with formulas.

By default, any formula entered in any single cell is automatically expanded to the rest of the column.

We can start by entering a formula into any cell of the column:

Excel Table, insert formula in table column

And that formula is automatically expanded when we press the Enter button:

Excel Table, expanded calculated column

 

When a formula is changed in any row of the column, that update will also expand to the rest of the calculated column:

Excel Table, changed formula, expanded calculated column

 

If you enter a formula, and it expands, those are two steps to potentially Undo.

If, for some reason, you want to have a different formula for a specific cell of the Table, you can enter it, and after it expands, use just one Undo.

 

If our column is a collection of different formulas and/or values, we can override that and apply a single formula to the whole column by (re)entering the correct formula and applying the following option:

Excel Table, changed formula, overwrite all cells in this column with this formula

 

If we expand our table with additional rows, our calculated columns will also expand.

That will happen if we enter or paste data directly below the table, or if our linked table contains more rows after refresh.

Consider the following example, we intend to paste new rows under our current table:

Excel Table, paste additional rows

Momentarily, those rows are formatted as part of the table, and the calculated column formulas are expanded:

Excel Table, paste additional rows, expanded columns

As we are pasting data instead of refreshing an external connection, if needed, in this case it would be possible to use one Undo to remove the automatic expansion of the table and calculated columns.

 

Structured references in Excel Tables

Structured references are references to tables and table column names, in contrast to references to cell ranges.

They are the intended way of referencing data from Excel Tables due to their presumed advantages (“human readable” references, structured references adjust automatically when data is added or removed from the table, or when columns are renamed).

 

Structured references can contain:

Table name, if a table is referenced from outside of the table (qualified reference). If we are using structured references inside of a table, we don’t have to name that table (unqualified reference).

Column name, enclosed in brackets.

Item specifier (#All, #Headers, #Data, #Totals, #This Row), which is used only situationally, and, if used, is also enclosed in brackets. If no specifier is used, only the data part of the table is referenced, headers and totals are not.

The only case in which an item specifier is often used are references to single rows, and then a shorter @ specifier is used.

This image shows all the names and specifiers we can use:

Excel Table, structured reference components

 

In the following example, we are referencing a single row:

Excel Table, structured reference to a single row at item specifier

The automatically expanded formula from the G5 cell contains only column names enclosed inside of the brackets. These are unqualified references.

The formula in the cell I7 first references the table name, and then the column names enclosed inside of the brackets. These are qualified references.

The formula in the cell I14 is returning an error, as there is no data in our table in that row.

 

Note the different ways columns incentive and tax_shield are referenced.

Double brackets are required when special characters are present in column names. When brackets or a number sign are present in a column name, a single quotation mark can be used as an escape character.

 

When referencing a whole table column (data part), we are writing our formulas in this way:

Incentive[[#Data];[net_incentive]]

This can be shortened to

Incentive[[net_incentive]]

This can (and will be automatically in this case) be shortened to:

Incentive[net_incentive]

 

Considering the following usage of structured references in the SUMIFS function:

Excel Table, structured reference to a column

 

However, if we drag that formula to the right, it will be automatically adjusted in the following way:

Excel Table, structured reference drag formula

In a sense, those references are “not locked”.

 

If we want to reference two or more columns, we can do that by using the range operator (colon) inside of multiple brackets:

Excel Table, reference multiple columns

 

We can use that way of writing formulas in order to “lock” structured references to columns in an Excel Table, as those are not moving:

Excel Table, locked structured references

 

References to Tables located in external workbooks will look something like this:

Book1.xlsx!Incentive[net_incentive]

However, problems with formula updates and other issues can occur. Structured references are, after all, intended to be used inside of Excel Tables, or to reference Tables located in the same workbook.

Retrieving data from Tables in outside workbooks is still better done via references to cell ranges those tables are superimposed over.

 

4 thoughts on “Excel Tables”

Leave a Reply