Structured Reference Tables have been around for a while, but have become a lot more powerful in recent versions of Excel. They can be extremely useful when using Excel for the purpose of data analysis and modelling but I’m always surprised by the number of people coming along on my training courses who’ve never heard of them, let alone seen them used.
Before I jump in and start telling you why you should or shouldn’t use them for financial modelling, I’d like to explain why I’m referring to them as “Structured Reference Tables” instead of just plain “Tables” as they are called in Excel. There are a number of features called “Tables” in Excel and it’s quite confusing – even for people who use Excel all day every day! Firstly, there are PivotTables, and then there are Data Tables, not to be confused with Data Tables. Confused? Well, there are two types of Data Tables in Excel. Go figure! Both of them are incredibly useful for modellers, however. The Data Table is fantastic for performing sensitivity analysis in financial modelling and the Data Table is a chart element which shows the numbers underneath the chart, as shown in Figure 1. I don’t want to get side-tracked, but Data Tables on a chart are great when you want to include a visualisation but not lose the numerical detail.
Structured Reference Tables are intended for entering, manipulating, sorting and filtering data in Excel. It’s an easy way of telling Excel that this data belongs together. Converting your data to a Table is particularly useful when working with PivotTables and Power Pivot. However, when we are creating pure, dynamic financial models using Tables is not always necessary, and at times it’s easier without them. This is because Tables behave a little differently when it comes to formulas. We know that financial modelling must, by definition, contain formulas, so Structured Reference Tables are not always an appropriate tool to use within a financial model.
Formulas work differently within tables. In the example below in Figure 2, we have created a SUMIFS function, and then tried to copy and paste it by dragging the cell. This is done by highlighting the range, going to the bottom right hand corner of the selected range with the mouse, and then dragging the cell to copy it. Normally this works in exactly the same way that an ordinary copy and paste does. Not when it’s referencing a Table though! If the formula’s source data comes from a table, then the dragging technique serves to change the column reference. In the example below, each of the criteria have changed to reference the next column along which is why the formula is not working. Download the sample file, and try it for yourself. If we want this formula to work properly when it’s been copied then we’ll need to use a different copy and paste method such as the control-c and control-v shortcuts. This workaround is easy enough to do, but I worry that other users of the model don’t realise it, and it causes confusion and errors for users trying to edit the model.
Unique, hard-coded headers. Another annoying feature of Tables is that the headers must be unique, and must be hard-coded (i.e. not contain formulas). Sounds simple I know but you’d be surprised how often I want to create dynamic headings in a table in financial modelling. A common example is when we’re creating a budget or forecast model with dates across the top. We normally create a budget start date in the assumptions of the model, and then every date in the column heading starts from this date using a formula such as EDATE() or EOMONTH() as shown below in Figure 3.
This isn’t possible though if the data in the model has been converted to a Structured Reference Table because a Table won’t allow formulas in the headings. I normally get around this problem by hard-coding Month 1, Month 2, Month 3 etc. across the top, and then putting the date formula in the row above it. Download the accompanying workbook for an example of this solution.
Of course there are lots of reasons why you should use Tables in financial models:
- The formatting looks great. Converting your data to a Table is the fastest way to get the alternate fill colour and in fact, I’ve been known to convert data to a Table and then convert it back to Range just to get the formatting!
- When you add a new column or row to the Table, the formulas and formatting will automatically copy as well, so that formulas will all be consistent (and we all know that this is a must for Financial Modelling Best Practice). No need to copy and paste down the column. Happy days!
- Totals can automatically sum (or count, or average) at the bottom quickly and easily.
- The headings are always visible, even when you scroll down. Instead of seeing the column letters (A, B, C etc.) across the top, the names you have given to the column will appear instead, as shown below in Figure 4. If this doesn’t happen, then make sure you have selected a cell within the Table before you scroll down.
- The column and Table name is automatically included in formulas which makes the formulas very easy to read, similar to a named range. (See the formula shown in the formula bar in Figure 2 for an example.)
- Best of all (in my opinion) is the fact that the Table automatically resizes. So if you’ve got a formula, chart or PivotTable referencing the data, and you add data to the bottom, your new data will be automatically included in the range. No fiddling around with formulas or ranges required!
