Like column names, table names can also be used in formulas. Read the previous article to understand the context.
The Problem
If you are inside the table or if you are touching the right or left side of the table, the formulas you add can use column names.
But if you are away from the table – even one cell away, the column names do not work!
This is because, you are not INSIDE the table. Therefore, the square bracket does not understand WHICH TABLE to show the field list from.
Suppose you want total of Home Team column somewhere outside the table. How do you get it? Of course you can select it manually … but then notice what syntax it generates.
It did not use the traditional range like H4:H10. It used the table name automatically.
Does that give you any ideas? But wait, Table1 is not a very useful name.
Why not change it to something more useful. Table Tools – Design – change the name.
The solution
Now we don’t have to navigate to select the range. Even if you are in a completely different sheet in the workbook, Excel recognizes table names. Table Names are unique within a workbook – so there is no chance of confusion.
So you get a nice dropdown showing the table name. Select the name and press TAB key to complete it.
Now the formula entry becomes extremely simple – even if the base data is in another sheet – you don’t know exactly which sheet it is – and there are thousands of rows in that column with hundreds of blank cells – you just don’t need to worry any longer.
That mundane and tricky work is now safely OUTSOURCED to the expert = EXCEL
That’s how your life is incrementally becoming simpler.
Unprecedented efficiency
Less effort and More Accuracy. Remember that.
What Next
We will see reference auto-update – the most important feature of Table in the next article.
Related articles
This article is a part of a series: Knowledge Pack – Excel Tables
***
