get-digital-help.com

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

1. Create table
2. Name a table
3. Edit data
4. Sort data
5. Filter data
6. Sum with criteria
7. Count unique distinct values in a filtered table
8. Extract unique distinct values from a filtered table (udf and array formula)
9. Filter duplicate records [Excel Table]
10. Structured references
11. Formulas in a table
12. Formatting
13. Show totals
14. Named ranges
15. Charts
16. Data Validation List
17. Working with a filtered excel table
18. Quickly navigate to a table

Create an Excel table

Follow these simple steps to convert a cell range to a table:

1. Go to tab "Insert" on the ribbon
2. Select your data set
3. Click "Table" button on tab "Insert"
4. Click "OK" button if your table has headers, if not deselect check box and click "OK". Excel will automatically create headers for you.
5. You have built an excel table

Tip! Use short cut keys CTRL + T to quickly build a table.

Back to top

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.

1. Select a cell in your table
2. Excel automatically navigates to tab "Design" on your ribbon
3. Change table name
4. Press Enter

Back to top

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.

Back to top

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.

1. Right click on a cell
2. Click Sort and then click "Custom Sort..."
   
3. Select column name to sort on and sort order then add more columns.
   
4. 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]

Back to top

Filter data

1. Click on a black triangle next to any header
2. Select values you want to filter
3. 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.

Back to top

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.

Back to top

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

1. Select a cell in excel table
2. Go to tab "Design" on the ribbon
   
3. Hover over a table style and see your table change
   
   
4. If you like it click on it to select it
5. Click the black triangle to se even more table styles
   

You can also build your own table style.

1. Select a table cell
2. Go to tab "Design"
3. Click black triangle
   
4. Click "New Table Style..."
   
5. Enter a name for your table style
   
6. Select a table element you want to change
7. Click "Format" button
   
8. Format as you like
9. Click OK button twice

Back to top

Show totals

1. Click on a cell in an excel table
2. Go to tab "Design"
   
3. Click "Total Row" check box
   
4. Click cell G7 and then on black triangle
   
5. 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.
6. If you click "More Functions" a dialog box opens with formulas to choose from.
   

Back to top

Named ranges

The Name Manager contains a list of all named ranges and excel tables.

1. Click "Formula" tab on the ribbon
2. Click "Name Manager"
   
   There are only tables in this workbook so the dialog box shows table names, no named ranges.

Back to top

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

Back to top

Quickly navigate to a table

Excel lets you quickly focus on a table if you type the table name in the name box.