Become more productive – Learn Excel Defined Tables
A very useful feature in Excel is defined tables, introduced in Excel 2007. Earlier versions had this feature as well but it was then known as Excel Lists.
What can an excel defined table do for you? It will simplify your work with data sets, adding or removing data, filtering, sorting, readability using cell formatting, cell references, formulas and more. I will go through all this in greater detail, keep reading.
Table of Contents
 Create table
 Name a table
 Edit data
 Sort data
 Filter data
 Sum with criteria
 Count unique distinct values in a filtered table
 Extract unique distinct values from a filtered table (udf and array formula)
 Filter duplicate records [Excel Table]
 Structured references
 Formulas in a table
 Formatting
 Show totals
 Named ranges
 Charts
 Data Validation List
 Working with a filtered excel table
 Quickly navigate to a table
Create an Excel table
Follow these simple steps to convert a cell range to a table:
 Go to tab "Insert" on the ribbon
 Select your data set
 Click "Table" button on tab "Insert"
 Click "OK" button if your table has headers, if not deselect check box and click "OK". Excel will automatically create headers for you.
 You have built an excel table
Tip! Use short cut keys CTRL + T to quickly build a table.
Name a table
I recommend you give the table and table headers descriptive names, for example it will be easier to identify cell references in formulas.
 Select a cell in your table
 Excel automatically navigates to tab "Design" on your ribbon
 Change table name
 Press Enter
Edit data
Add data to a cell adjacent to the table and the table expands automatically.
Press tab on keyboard to move from one cell to another, if you press tab while having the most lower right cell in the table selected a new row is included. See picture below.
Right click on a cell in a table to open a menu, from there you can insert or delete rows and columns, also select columns and rows or all table data.
Sort data
Sorting a table is easy, click on any black triangle located at each header, a menu appears allowing you to quickly sort data in a descending or ascending order.
An arrow next to the black triangle indicates sort order. Sort Z to A (descending) shows you an arrow pointing down.
You can also sort on multiple columns, follow these steps.
 Right click on a cell

Click Sort and then click "Custom Sort..."

Select column name to sort on and sort order then add more columns.
 Click OK button to apply sort settings to table
The following article demonstartes how to sort a values in an excel defined table using a macro:
Sort values in an Excel table [VBA]
awall asks: Hey, can you do the opposite of this  not random order but this is my situation. I […]
Sort values in an Excel table [VBA]
Filter data
 Click on a black triangle next to any header
 Select values you want to filter
 Click OK button
See animated picture below.
Excel allows you to apply filters to multiple columns easily, repeat above steps with another column.
Use the search field to quickly find the value you want to filter, see picture below.
Sum with criteria
You can quickly sum values using table filtering.
Select a cell in the table, then go to tab "Design" on the ribbon.
Click "Checkbox" to enable "Total Row".
A row with totals appears on your table (332), see picture above.
Now filter the table, see instructions on picture below.
See how the total changes from 332 to 175.
Structured references
You are probably used to cell references like this one:
Creating a cell reference to a table column returns this instead, see picture below.
First the table name (Inventory) and then the column name enclosed with brackets [Price].
The amazing thing with structured references is that if you add or remove values to a table the structured reference stays the same, no need to update cell references. In other words, they are dynamic.
Back to top
Formulas in a table
The following example demonstrates what happens if I type a formula in an excel table. I want to multiply cell E3 with F3 in cell G3, see animated picture below.
Excel creates these structured cell references in cell G3 if I type = (equal sig) and then click on cell E3, type * (asterisk) and then click on cell F3:
=[@['#]]*[@Price]
@ (at) means cell value on same row as formula.
Excel also calculates the remaining cells in column G automatically, see animated picture above.
Creating a reference to the entire excel table and headers returns this: =Inventory[#All]
A reference to data in table looks like this: =Inventory
A reference to a table column returns: =Inventory[Warehouse]
A reference to a column header only looks like this: =Inventory[[#Headers],[Warehouse]]
Back to top
Formatting
 Select a cell in excel table

Go to tab "Design" on the ribbon

Hover over a table style and see your table change
 If you like it click on it to select it

Click the black triangle to se even more table styles
You can also build your own table style.
 Select a table cell
 Go to tab "Design"

Click black triangle

Click "New Table Style..."

Enter a name for your table style
 Select a table element you want to change

Click "Format" button
 Format as you like
 Click OK button twice
Show totals
 Click on a cell in an excel table

Go to tab "Design"

Click "Total Row" check box

Click cell G7 and then on black triangle
 You can change how value in cell G7 is calculated, the menu has these formulas: Average, Count, Count Numbers, Max, Min, Sum, StdDev, Var and More Functions.

If you click "More Functions" a dialog box opens with formulas to choose from.
Named ranges
The Name Manager contains a list of all named ranges and excel tables.
 Click "Formula" tab on the ribbon

Click "Name Manager"
There are only tables in this workbook so the dialog box shows table names, no named ranges.
Charts
Combine chart and table to make use of dynamic cell references while filtering data.
More details here: How to create a dynamic chart
Back to top
Data validation list
The following animation shows you a data validation list linked to a table.
Read this post if you are interested in the details:
How to use a table name in data validation lists and conditional formatting formulas
Back to top
Working with a filtered table
If you try to use a filtered table as a data source in a formula you are in for trouble, see animated picture below.
As you can see above the SUM function sums all values in table regardless of filtered or not.
I have written a few articles about this:
 Highlight duplicates in a filtered excel defined table
 Count unique distinct values in a filtered table
 Highlight unique values in a filtered excel table
 Populate a list box with visible unique values from an excel table (vba)
 Highlight unique values in a filtered excel table
 Extract unique distinct values from a filtered table (udf and array formula)
 Vlookup visible data in a table and return multiple values
Quickly navigate to a table
Excel lets you quickly focus on a table if you type the table name in the name box.
How to use a Table name in Data Validation Lists and Conditional Formatting formulas
David Hager gave this valuable comment about how to reference a table name in conditional formatting formulas: =INDIRECT("Table1[Start]") Watch this video to […]
Extract unique distinct values from a filtered Excel defined Table [UDF and Formula]
Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]
Count unique distinct values in a filtered Excel defined Table
A few days ago Debra Dalgleish described how to create a Line Between Dates in Filtered List. She modified a […]
Remove common records between two data sets
This article demonstrates how to filter records occurring in only one out of two Excel defined tables. It also shows […]
Cyril asks how to hide all columns of a range except columns whose header is found in specific cells. I am […]
Highlight duplicates in a filtered Excel defined table
You can highlight duplicates in an excel defined table using conditional formatting. However, that won´t work if you only want […]
Copy filtered Excel tables [VBA]
Today I want to share some pretty useful macros. My first macro copies an excel defined table with vba. It is […]
Copy excel table filter criteria [VBA]
Here is how to copy filter criteria from an excel table and use the same table filters on another table. […]
This article demonstrates how to filter duplicate records using a simple formula and an Excel defined table.
Today I am going to demonstrate how amazing pivot tables are! Take a look at this time sheet. You can expand […]
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form