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 programmatically [VBA]
This article demonstrates how to sort a specific column in an Excel defined Table based on event code. The event […]
Sort values in an Excel table programmatically [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 an Excel Table name in Data Validation Lists and Conditional Formatting formulas
This article demonstrates different ways to reference an Excel defined Table in a drop-down list and Conditional Formatting. There are […]
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
This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table. Debra Dalgleish described in […]
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 […]
Populate drop down list with filtered Excel Table values
This article demonstrates how to populate a drop down list with filtered values from an Excel defined Table. The animated […]
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 […]
Hide specific columns programmatically
This article describes a macro that hides specific columns automatically based on values in two given cells. I am also […]
Highlight duplicates in a filtered Excel Table
The image above demonstrates a conditional formatting formula applied to an Excel Table containing random data. The Excel Table has […]
Copy Excel Table filter criteria programmatically
I will in this article demonstrate a macro that copies criteria from one Excel Table and applies them to another […]
This article demonstrates how to filter duplicate records using a simple formula and an Excel defined table.
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.