Five flaws in Excel Tables - and how to fix them

Share this content

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

Please Login or Register to read the full article

The full article is available to registered AccountingWEB.co.uk members only. To read the rest of this article you’ll need to login or register. Registration is FREE and allows you to view all content, ask questions, comment and much more.

About Simon Hurst

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.

 

Replies

Please login or register to join the discussion.

avatar
By canesbr
15th May 2015 16:29

Table Deficiencies
Here is a great workaround for the lack of absolute references in Tables formulas. http://www.excelcampus.com/tips/absolute-formula-references-excel-structured-table/Another deficiency, a workaround for which I do not know, is the inability to have a formula in a column heading.  And if you have a regular range containing some formulaic headers and you convert to a ListObject Table, the formula column headings are converted to values. Silently. No warning, no alert, no by-your-leave my lord/lady. 
>sigh<

RegardsBrian    

Thanks (0)
avatar
By SteveA
15th May 2015 16:46

Brian, number 1 is a great tip. Thanks

Thanks (0)
15th May 2015 16:48

Range workaround

Hi Brian - thanks for the additional information - I think that workaround is the same as the one I referred to towards the end of the absolute/relative section.

Thanks (0)
avatar
09th Apr 2016 03:52

How to put Formula in Table Column Heading

canesbr wrote:
Another deficiency, a workaround for which I do not know, is the inability to have a formula in a column heading.  And if you have a regular range containing some formulaic headers and you convert to a ListObject Table, the formula column headings are converted to values. Silently. No warning, no alert, no by-your-leave my lord/lady. 

Here's your workaround: Place a textbox on top of the header cell. Link the textbox to a cell containing your formula. Format the textbox to look identical to the other headers. Behind the textbox, enter a static fieldname for that table-field, to use in your formulas. You're welcome :)

 

Thanks (0)
avatar
By SteveA
15th May 2015 16:35

Add a row to a table on a protected sheet

For you last point try

Select the cells in the table, format the cells > protection > Tick Hidden (not locked)

The protect the worksheet in the usual way, but tick the insert rows.

Now it is true you can't insert table rows using the table commands

But you can insert a row onto the worksheet that "goes" through the table

Eg if you table data cells are B3:E10 you can insert a whole row on row 6 (say)

And the formula automatically applied to the new rows

Obviously not a perfect solution, as it will mess up data adjacent to the table, but if the table is on it's own on a sheet, not so bad.

 

My biggest gripe with tables is the lack of absolute addressing in the structure reference.

 

 

Thanks (0)
15th May 2015 16:58

Protection

Hi Steve

Interesting approach, but with the use of hidden rather than locked, would it not still be possible to overwrite any existing formula cells?

Thanks (0)
avatar
By SteveA
15th May 2015 17:06

Yep your right. Back to the drawing board.

Thanks (0)
15th May 2015 17:11

Protection

Well, it was certainly worth a try. Perhaps AccountingWeb could offer a £1m prize to whoever cracks the problem (without writing code...)

Thanks (0)
15th May 2015 18:27

For those who have been watching closely

There was a bit of an editing mix up, during which the article's headline changed between "four" and "five" flaws while Simon explained the nuances about external references to me. I hope the switching around hasn't diminished the usefulness of his article.

Apologies to both Simon and his fans on AccountingWEB if this was the case.

Thanks (0)
16th May 2015 12:19

My fault entirely

Thanks for sorting it out John - my fault entirely for including two flaws in a single heading and then only explaining one of them.

Thanks (0)