An improved feature since Excel 2007 are Excel Tables (previously known as Lists). The purpose of an Excel Table is to aid with managing and analysing data.
Excel Tables allow for easier sorting, filtering, formatting, formula writing and interpretation of your data.
They have some features that I think are MAGICAL and will save time and make your workbooks easier to use days, weeks and months after you created them.
NOTE: Excel Tables are not to be confused with Data Tables that can be used for what-if analysis.
Enter your email address below to download the sample workbook.
Download the workbook and practice what you learn. Note: This is a .xlsx file. Please ensure your browser doesn't change the file extension on download.
Watch the Video
How to insert Excel Tables
Excel Tables work best with data in a tabular layout.
To insert an Excel Table, select a cell anywhere in the data range you want converted to a table and press CTRL+T.
In the example below the Table begins with the headers on row 6. Rows 1 to 5 are not part of the Table.
Magical Feature 1 - Formatting
Once your table is inserted you will see that formatting is applied automatically and when you have a cell in the Table selected, a new tab will appear on the ribbon called ‘Table Design’.
Here you can choose from 61 different built in styles to change the format, or create your own in line with your branding.
If you don’t want to work with your data in an Excel Table but you like the instant formatting it provides, you can convert the table to a regular range while keeping the style formatting you applied.
You’ll find this option on the Table Design tab of the Ribbon, or simply right click anywhere in the table and select Table > Convert to Range.
Magical Feature 2 - Automatic Totals
Insert a Total Row. But this isn’t any ordinary total row.
Total Rows in Tables enable you to select a different function from the drop down list or select More Functions to enter something else.
- You can toggle the Total Row on and off and Excel will remember the functions that exist.
- As you add rows/columns, your Total Row will automatically update to include the new data.
- The Total formulas use the SUBTOTAL function so that if you Filter your table the Totals will only SUM the visible cells.
Magical Feature 3 - Formula Writing
Insert a formula in a new column.
- Type a new heading immediately after the last column and the Table will automatically incorporate the new column in the Table range and apply the formatting.
- Tip: Format the cell before you enter your formula.
- Enter the formula and you will see that the table automatically copies the new formula and formatting down the whole column to the bottom of the table. This is known as AutoComplete.
- Add rows and your formula will AutoComplete to fill the new rows.
- Any changes you make to the formula will automatically propagate to all rows.
- Notice the formula in the formula bar below does not refer to cell references. Instead it uses what Excel calls ‘Structured References’ which is very similar to Named Ranges. Read Magical Feature 4 below for more on this.
Excel Tables Syntax:
Magical Feature 4 - Structured References
Working with Formulas in Tables
When you insert a Table Excel automatically gives the Table and each column a Structured Reference, similar to a Named Range.
This Structured Reference automatically updates as you add or remove data.
The name for the table is usually something like ‘Table1’ and the columns will have names based on the column headings in the table. If you don’t have any column headings, Excel will automatically insert headings like Column1, Column2...and so on.
As you add or remove columns and rows the Structured References automatically update.
You can rename the default ‘Table1’ name on the Design tab in the Properties section.
To rename the columns simply type a new name in the headers.
Table Formula Shortcuts
- The whole table =Table1[#All]
- The headers =Table1[#Headers]
- The entire column = Table1[[#All],[Column1]]
- All data (i.e. not including the headers) in the table =Table1[#Data]
- The header value of one column =Table1[[#Headers],[Column1]]
- The current row is simply =@Column1
These Structured References (Table1, #Headers, #Column1 etc) can also be combined e.g.
......and so on.
Note: If you renamed your Table you will replace ‘Table1’ in the formula with your Table name. Likewise you will use your column names if they aren’t the default Column1 and so on.Using Structured References in Formulas:
- Sum the Salary column =SUM(Table1[Salary])
- Average the Salary column =AVERAGE(Table1[Salary])
- Sum the whole Table =SUM(Table1)
- Multiply Salary by 0.1% =[@Salary]*0.1%
Note: [@ColumnName] is indicating that the formula is applied to each row of the column individually as in the example below.
Getting to grips with using Structured References in formulas takes a little practice, but just imagine how much time you will save once you can write a formula from anywhere in your workbook without to-ing and fro-ing back and forth between sheets.
Important things to know about Structured References:
- Table names are unique to the workbook. If you have 5 Tables in a workbook on many sheets, each table will have its own name which you can reference from any sheet.
- You can’t have a Named Range with the same name as a Table or Column.
- You will find your Table names in the Name Manager alongside your Named Ranges.
- If the table name or column names are changed the formulas will automatically update with the new names.
- Structured References are handy when working with data validation drop down lists, PivotTables, and anything else dependent on the Table range like formulas etc.
Magical Feature 5 - Filters
They automatically insert Filters.
I’m not going to go into how to use Filters here, but for those of you who know how to use them, you'll be pleased to know that all formulas used in the total row automatically exclude any rows hidden by the filter.
And any new rows added are automatically included in the total.
Absolute and Relative References
Unfortunately you can't use the F4 key to apply absolute references to Structured References in formulas. They have some quirky rules depending on whether you're copying and pasting a formula or left-clicking and dragging it. More on working with absolute references for Table Structured References.