Save content
Have you found this content useful? Use the button above to save it to your profile.

Five flaws in Excel Tables - and how to fix them

by

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.

15th May 2015
Save content
Have you found this content useful? Use the button above to save it to your profile.

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:

 =[@Value] lets you copy a formula with the fill handle so the reference changes dynamically 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:

Control+r keyboard shortcut 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:

A ‘range’ reference to a single column will remain absolute

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:

Combine range references and normal references to create a formula that can be copied using the fill handle

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?

Tags:

Replies (9)

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 (1)
Replying to Tax Dragon:
avatar
By SteveA
15th May 2015 16:46

Brian, number 1 is a great tip. Thanks

Thanks (0)
Replying to Tax Dragon:
Simon Hurst
By Simon Hurst
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)
Replying to Tax Dragon:
avatar
By johnywhy
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)
Simon Hurst
By Simon Hurst
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)
Replying to anniem:
avatar
By SteveA
15th May 2015 17:06

Yep your right. Back to the drawing board.

Thanks (0)
John Stokdyk, AccountingWEB head of insight
By John Stokdyk
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)
Simon Hurst
By Simon Hurst
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)