Excel Tables are one of the most interesting and useful features in Excel.
This article provides an introduction and overview.
Creating a table is fast
you’re free to create an Excel Table in less than 10 seconds.
Navigate directly to tables
Likenamed ranges, tables will appear in the namebox dropdown menu.
Just nudge the menu, and snag the table.
Excel will navigate to the table, even if it’s on a different tab in a workbook.
Watch the video below for a quick rundown.
Video:Shortcuts for Excel tables
4.
Painless drag and drop
Tables make it much easier to rearrange data with drag and drop.
After you’ve selected a table row or column, simply drag to a new location.
Excel will quietly insert the selection at the new location, without complaining about overwriting data.
Note: you mustselect the entire row or column.
For columns, that includes the header.
Tables solve this problem in a clever way.
When column headers scroll off the top of the table, Excel silently replaces worksheet columns with table headers.
In a similar way, a table automatically contracts when rows or columns are deleted.
When combined with structured references (see below) this gives you a dynamic range to use with formulas.
Totals without formulas
All tables can display an optional Total Row.
The Total Row can be easily configured to perform operations like SUM and COUNT without entering a formula.
When the table is filtered, these totals will automatically calculate on visible rows only.
Rename a table anytime
All tables are automatically assigned a generic name like Table1, Table2, etc.
However, you’re free to rename a table at any time.
Select any cell in the table and enter a new name on the Table Tools menu.
Change formulas automatically
The same feature also handles formula changes.
In the screen below, the tax rate has been changed to 7% in one step.
Human-readable formulas
Tables use a special formula syntax to refer to parts of a table by name.
This feature is called “structured references”.
you could easily use this dynamic range in your formulas.
For example, the table in the screen below is named “Properties”.
Excel will automatically enter the structured reference for you.
In the screen below, the price column was selected after entering =MAX(
14.
Enter structured references by typing
Another way to enter structured references is by typing.
Use the arrow keys to select and the TAB key to confirm.
Video:Introduction to Structured References and Tables
15.
With one click, the table will inherit the new style.
Remove all formatting
Table formatting is not a requirement of Excel tables.
Override local formatting
When you apply a table style, local formatting ispreserved by default.
However, you might optionally override local formatting if you want.
Right-click any style and choose “Apply and Clear formatting”:
19.
Set a default table style
you could right-click any style and choose “Set as Default”.
New tables in the same workbook will now use the default you set.
In the template file, set the default table style of your choice.
Watch the video below to see how this works.
Video:Use a table for your next pivot table
21.
Use a table to create a dynamic chart
Tables are a great way to create dynamic charts.
Video:How to build a simple dynamic chart
22.
The table below has a slicer for Department:
23.
You might be surprised to see that converting a table back to a normal range doesn’t remove formatting.
