Following are the tricks you can do with the features Excel tables offer. Following list is in no particular order. I think all of them are handy that every excel user must know to make his/her work more easy.
- You name it! – Tables got name (scroll below)
- Resize tables without affecting other areas of worksheet
- Structured referencing – A new referencing system
- Calculated Columns + Table styles + Filter and Sort options
- Dedicated formula row + Remove duplicates + Hide/Show table header + Revert table back to normal range with ease
- Select what you wish with ease
- New navigation system inside table + Drag-drop table without destroying dependent formula
- Print only table + Export table data + Special formatting for first and last table columns
- Automatic extension of table as data grow + Creating dynamic named ranges and dynamic data validation lists
- Table to Pivot table – Turbo charged data on rocket fuel
- Data entry forms for every table – Unearthing the hidden
Each table bears a unique name. And once something has a name it not only has identity it has authority as well. Same is the case with tables. Once given the name you can step into the realm of structured referencing instead of orthodox column row reference system. With the names being automatically generated it is easier for you to navigate to that data. For instance you have sales results of 10 different products then jumping from one to other will be so cumbersome. But with names defined you can jump to it easily by selecting the table from name box. Following animation illustrates the same where one workbook has several tables on several worksheets and you can jump to each table easily
Another feature that comes with it is that normally a named range is fixed and stays to the extent you mention while naming it. However, with tables it can grow as data grows and it shrinks as data volume regresses. So you don’t have to use OFFSET functions and all that mess in many instances. This will be further discussed in Structured references and dynamic ranges in upcoming tricks.