20+ Excel Table tricks to turbo charge your data

In my last article on tables Introduction to Excel Tables – Data beyond just formatting I discussed the basics. If you are new to excel or if you think that tables are meant for just formatting then don’t forget to read that article first and then continue with this one. This article will make much more sense after reading first article of the series on Excel Tables.

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.

For the purpose of following tricks-cum-tutorials download this excel workbook. It contains all the necessary worksheet to help you understand and apply the concepts listed below.
As there are more than 20 tricks you can perform with tables, the whole article has been divided in several pages to help you access and understand each of them easily. To quickly move to specific trick click jump link:
Excel Trick # 1. You name it!

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

et name

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.