Well it’s the Boxing Day holiday here in Aussieland 2017 and one of our traditions is watching the Test Cricket in Melbourne that always starts on Boxing Day. Fantastic stuff. But also, this is the time of the year to think of New Year’s Resolutions. If you are not using Tables in Excel, this would be a good resolution for 2018 to make! Seriously. Maybe I am preaching to a diminishing group out there, but I bet there are lots of people too busy to get their heads around using Tables and especially the Structured References syntax required for formulas referencing Table data. This is a large topic and whole books (well I can think of one to the right) and certainly many chapters of books have been written on Tables
. This book by Zack Barrasse and Ken Jones is a very good one an an easy ready to get you started. However you make this change, the relatively small investment of time in adopting Tables will yield a lot for you. For practical everyday financial modelling, they are a hugely fantastic improvement in Excel, and will change how you financial model and has brought Excel closer to a true data professional’s tool.
Why do I think this….
Dynamic modelling – One of the bugbears of financial modelling in the past has been in having dynamic solutions to what ever you are doing. If you needed to make sure your formulas were still working or your dropdowns were picking up new additions you typically did one of 2 things. You either made your range big enough to include additions or a complex INDEX or OFFSET with range names formula to do the same thing which were formulas probably beyond most intermediate users. So your reports would go to print not including the latest month’s data or stop working when you were on holidays because only you knew how to insert new rows to the model. Or the stock balance calculated incorrectly because it didn’t pick up the most recent data and caused some wrong expensive decisions – sound familiar? But when tables came in in 2007, any user could simply add to the bottom or to the right of the Table and it grew effortlessly to include your data including complex formulas and formats automatically. Models instantly became more robust.
Data Discipline – Once you see the above and below advantages then you naturally organise your data in Tables which forces a more disciplined way just as a professional data analyst should be organising data i.e. ‘like with like’ such as transactions together, customer master data together, product data together and so on. The advantages of this are profound but for one, it’s easier to re-use your data and make use of powerful data tools like Pivot Tables and the relatively new but insanely awesome Power Query (called Get & Transform on the ribbon) functionality whose output is… Tables. I did a recent blog post on Organising data like an Excel Analyst which has more thoughts on this. Since Excel 2013 I think (I have Office 365 so lose track of the changes as they come with the stand alone versions) Excel has given you the power to join Tables so that your Pivot Tables could be even more powerful and massively reduce the amount of Lookup formulas required. I will do a post on this in the near future as that ability changes and greatly simplifies spreadsheets. Imagine separate Budget, Forecast and Target data Tables being joined with an Actual data set and a Pivot Table effortlessly showing the Actual with which ever other measure you want.
Easier formulas – Once you get Structured References (the term for yje specific formula syntax for using Table references), I have found writing complex formulas is way easier than it used to be because the formula bar intellisense really works for you and you aren’t relying on memory or going backwards and forwards for column names or range names anywhere near as much. Formulas accessing Table data are often are longer I admit, however they are easier to write and read because Structured Formulas use the Table column header label directly in the formula which will ensure it’s meaningful. I found you get comfortable with the lengthiness of formulas with practice and using a expanded formula bar (at least temporarily as you are writing the formulas) and breaking formulas into multiple lines helps enormously.
A little tip I have gotten into the habit of doing is naming my tables immediately after creating them or else you just have Table1, Table2 and so on to sort out later. Also I think it was from Dick Kusleika’s Daily Dose of Excel that I picked up another great tip of prefixing all Table Names with “tbl”. If you do this then when entering a formula look what happens (see under column B) if you enter tbl in the formula bar – you get a list of all tables together to choose from and use in your formula – no memory required. But it get’s even better, if, after you arrow down to your Table and choose it using tab or by double clicking, if the next thing you do is then type”[“, you will then be presented with a list of column headers from the Table you have chosen to then use directly in your formula. No remembering the column names.
After understanding this, sure the Structured References formula syntax takes a bit of getting used to but if you use the list that is presented to you in the formula intellisense using tab or double click as above, you can observe the syntax Excel writes for you and after a while it becomes second nature. If you want more explanation on this, here is good intro blog post from Chandoo on Structured References.
So as I see it main Table formula related ‘take aways’ are:
1) Every formula (and format) in a Table column starts off and should remain consistent so the Table knows without any doubt what formulas and formats to bring down to each new line added. Formulas going down columns instantly happen in Tables as the first formula is entered. You will have Table expansion problems not that easy to fix if you try to have inconsistent formulas going down a Table column or inconsistent formats, trust me.
2) Be careful on Absolute vs Relative Formula issues. Basically a Structured Reference is an Absolute Reference if you copy the formula across. This kind of makes sense to me as you are telling the Table formula you want a specific column in the Table which has a specific name which is quite different to a worksheet column reference. If you fill the formula across the formula changes as though Relative. Try it and you will see what I mean. Please note and watch for this because we are all used to copying Excel formulas across and used to them being automatically Relative which is not the case in using Structured References. Table Column Headings must be unique and can’t be a formula by the way.
Slicers – You can use Slicers easily to filter your data and the Table itself can be part of your reporting or dashboard. You can still use Autofilter for more complex filtering.
VBA – Your coding for working with Tables is different in the sense you are working with Table Objects not the Worksheet range. Chris Newman has done a great post on this at his site the SpreadsheetGuru.com
Bottom line now is for all my client work I use Tables and Structured Reference formulas where ever possible so financial models are more robust, better organised for re-use of data and I can follow complex formulas months or a year later fairly quickly.
One Downside of Tables to be aware of…
There is one downside I better mention which I would have thought the Excel Development team would have avoided given the growing importance of Tables. You can’t use Tables on a protected sheet and have them expand down or across for new rows and columns. I would have thought this should at least be a specific option in the Protection dialog or allowed if the option in the Protection dialog to to insert and delete rows and columns was ticked. Nope, you need to work around this by having some VBA allow you to add rows and columns which first Unprotects the sheet, does it’s changes and then re-Protects the sheet after the changes is made. Or even simpler, have a general macro to Unprotect the sheet so the user can operate the Table as normal and and another to Protect it. An Event macro could Protect this sheet on closing in case it’s forgotten. You can assign these macros to buttons or keystrokes and it can work fine but a shame that I hope one day soon get’s fixed.