## 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 […]

#### 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 table [udf and array 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 table

A few days ago Debra Dalgleish described how to create a Line Between Dates in Filtered List. She modified a […]

This post describes how to add a map (background picture) to a chart. The following animated picture shows a scatter […]

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 […]

This article demonstrates how to filter duplicate records using a simple formula and an Excel defined table.

Count unique distinct values in a large dataset with a date criterion

davidlim asks : i have ~100K rows, and Excel is literally stalled when running the formula. for the time being, […]

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

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form