Excel Tables feature frequently in management reporting tutorials, but Simon Hurst knows that they aren’t perfect. This article offers solutions for some of the key problems.
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.
Register for free and log in to AccountingWEB.co.uk to read the rest of the article, which shows you how to deal with the other four flaws:
- Structured references – absolute or relative
- No Tables allowed in a shared workbook
- External workbook references return #REF!
- You cannot add a row to a Table on a protected sheet
About Simon Hurst
Simon Hurst is the founder of technology training consultancy The Knowledge Base and is a past chairman of the ICAEW's IT Faculty.