As we have discussed several times before on AccountingWEB, Excel Tables can be extremely useful and can help make your workbooks more robust and automatic, even eight years after its introduction the implementation of Tables is still far from complete. We are going to look at some of the remaining deficiencies in Excel Tables and also some irritations and see how they can be overcome, it at all.
Incomplete implementation of structured references
If you drag or click to refer to a Table column, or to an individual Table cell on the same row as your formula, you should see that your formula is not a normal cell reference but instead a ‘structured’ reference using the name of the Table, the column heading and possibly one of several modifiers. Usually you will only want to refer to the values in a column excluding the header but the modifier #All will ensure your reference includes the whole column, including the header, for example. Other modifiers allow you to refer to just the heading (#Headers), the total (#Totals) or the current row (@ - Excel 2007 #ThisRow).
This feature can be very useful. Firstly, it makes your formulae easier to understand as long as you have used the Table Tools, Design ribbon to give your Table a descriptive name. Secondly, references to complete Table columns expand to incorporate additional rows as they are added. However, the ability to use these structured references is incomplete. For example, neither Data Validation formulae and List sources nor Conditional Formatting formulae are able to use structured references. Data Validation has an additional idiosyncrasy. If the List source is on the same sheet as the Data Validation cell, although the reference to the Table column has to be entered as a normal cell range, it will still expand automatically as rows are added. However, where the List source is on a different sheet, the range does not expand automatically.
One solution to this and other situations where you can’t use structured references is to use Range names. If you apply a Range name to a full Table column the range will automatically expand and contract as you add or delete Table rows. The Range name can then be used in Data Validation or Conditional Formatting in place of the structured reference.
If you would prefer not to use structured references, you can either just type the cell references in rather than clicking with the mouse, or you can turn the use of structured references off altogether by deselecting the ‘Use table names in formulas’ option in the Formulas section of Excel Options.
Structured references – absolute or relative
There is another issue with structured references, this time it’s the apparent lack of ability to define a column reference as absolute within a formula. If you drag a reference to a Table across other columns it will, perhaps surprisingly, act as a relative reference changing from column to column. Here we have created a reference from C4 by clicking on B4. This will be created as =[@Value]. If we use the fill handle and drag to G4 to copy the formula, we can see how the reference changes in each column:
If you simply want to copy a reference to the Value column to each of the other columns then, instead of using the fill handle, you can select cells C4 to G4 and use the Control+r keyboard shortcut to fill right. This copies the =[@Value] reference without changing it:
So, we have a method for copying a structured reference as a relative reference (fill handle, drag) or as an absolute reference (Fill, Right or use copy and paste). However, neither method will work if our formula needs to include a mixture of absolute and relative references. There is a ‘trick’ that can achieve this (my thanks to Paul Wakefield for pointing this out on another forum). If you create a ‘range’ reference to a single column, this will be absolute however it is copied:
To create a formula that can be copied using the fill handle, keeping some column references absolute and others relative, you would combine range references and normal references:
As you can see, the reference to the Value column created using the range reference stays absolute whereas the reference to Column1 is relative.
No Tables allowed in a shared workbook & External workbook references return #REF!
Two flaws with a similar solution. You will typically encounter #REF! if references to Tables point to a closed, external workbook.
If you want to use the content in a Table from one workbook in another workbook you can use Data, Get External Data, From Other Sources, From Microsoft Query or, if you have access to it, the Power Query Add-in to create a refreshable link to the data in the Table.
You cannot add a row to a Table on a protected sheet
This is probably the most serious Table deficiency. One of the great advantages of Tables is that formulae in columns will automatically be copied to a new Table row.
This allows the automation of calculations based on the data, without the need to enter formulae manually or adjust formulae for changing numbers of rows. However, many people consider it good (or even essential) spreadsheet practice to lock any cells containing formulae to stop them being changed.
Regardless of the specific protection settings, it seems that as soon as you turn on sheet protection you lose the ability to add a row to a Table on that sheet. I’ve seen it suggested that you can resort to using Visual Basic code to unprotect the sheet, insert the row and then reprotect the sheet. Even if your VB skills are up to it, many people will prefer to avoid having to use VB in this way. The Tables feature comes close to making it possible to use Excel for limited data entry tasks. The inability to use Tables properly when a sheet is protected undermines the advisability of doing so in many cases. If anyone does know a reasonably straightforward way to solve this issue, please add a comment to let us know. Otherwise, this really should be a priority for a future upgrade – Office 2016 maybe?