A few years ago I picked up on a gem of a tip from Dick Kusleika from his then blog Daily Dose of Excel, on using the Table Name prefix of “tbl”. This site seems to have stopped sadly for Excel nerds, I am not sure if that is temporary or for good, but it had really practical, mostly advanced and eye opening topics from Dick and other Excel Experts with some good humour thrown in especially from Jeff Weir a voluminous contributor.
Anyway I have made Dick’s tip of prefixing all Tables with “tbl” a habit and I find it pays off time and time again. So if I have Table of transactions I call it tblTransactions and so on. I probably should also say it’s a good idea to name Tables in the first place as it helps your Structure Reference formulas which I find these days are much easier to write than regular Excel formulas as you are referencing more readable and memorable column headings not column letters. And to reference these column headings you need to reference the Tables so Table names are better than the default Table1, Table2 and so on. So I have found building formulas especially if these tables are on other sheets (unlike my simplistic example) is so much easier.
So firstly the name is entered under the (Table) Design tab that appears if you have selected within a table – the highlighted box has the default Table1 so let’s name it tblTransactions
So let’s say we want to enter a SUMIFS formula to sum sales for customer A on 03/06/18. We start with =SUMIFS(tbl in the 3rd image – the moment we do this the auto-complete suggestions shows all your named tables neatly together (in this simplistic case I just have two so it’s not that dramatic a list but imagine a dozen) to select using Tab, then once the Table name is filled in you just have to enter “[” and up pops the list of column headers related to that Table see the 4th image. An easy select by tabbing and closing the “]” and then carrying on completing formulas in this fashion. Once you get used to this way of entering Structured Formulas (the name for formulas referring to Table elements) made so much easier by the tbl prefix, you become more efficient in formula writing. If you write VBA code using Table Names is also clearer in the code.
So I thought this tip was worth highlighting to you as I have found this simple habit helps me time and time again.
One other little tip in Dick’s post is also worth noting… “I also use the singular noun, so it’s tblCustomer and not tblCustomers”. This one you might gloss over but is another great habit to adopt – I have in the past called my Tables say tblCustomers but other Table Names are naturally singular like tblFreight so you can end up with a mix which just gets all a bit inefficient when you forget which name was singular and which is plural. Better to get in the good habit of using one or the other – Dick uses singular which makes sense to me.