UGTS Document #57 - Last Modified: 8/29/2015 3:23 PM
Advantages of Excel Tables

Excel 2007 improved on a previous feature known as the 'list' and called it the 'table'. An Excel table is a two dimensional data range which is treated specially by Excel:
  • Auto-grow - You can type data in a cell just below the bottom of the table, and the table will automatically grow to include that row. You can also click on a box in the lower right corner of the table range to manually grow or shrink the table by multiple rows at a time.
  • Auto-format - As rows are added to the bottom of the table, the color format you've applied to the table is automatically extended to the new rows.
  • Auto-filter - A filter is automatically applied to the header row in the table and automatically includes all the rows in the table as new rows are added.
  • Smart-formatting - If the color format you use has alternating row colors, the colors still alternate even if you filter the table to show two even numbered rows next to each other (the alternating colors are not fixed to a particular row number, but are updated dynamically based on what's visible).
  • Auto-formulas - If a column has a formula, that formula is automatically copied down to new rows as they are added.
  • Structured References - Cell references can use the header name to reference a column in their own table or another table, rather than to hard-code a column letter code.  Rarely again do you have to reference rows and columns by letter and number, although you do have to sometimes call the MATCH function to translate a structured reference into an integer offset.
  • Auto-Named Range - Creating a table automatically creates a named range called Table[N] which is the boundary of the table. It is displayed with a table icon in the Name Manager, and as rows are added, the named range automatically grows to match the size of the table.
  • Auto-Range Selection - When you've defined the range as a table, you can no longer select only some of the columns in the range by mistake and sort some of the columns and not the others (which would result in a mangled table).  Instead, if you select any cell in the table (not just the header), and then sort, all the cells in the table are selected for sorting.

There are also some limitations of tables (none of which are major), and a learning curve to use them effectively:

  • Indirect Named Range Support - Though a named range is created for you automatically, you can't have data validation reference the name of the table directlytable's named range directly or the data validation editor window will tell you there is an error with your expression.

    Instead, you have to define a standard named range in addition to the table's named range, and point the standard named range to the smart column reference of the table named range.  For example, if you have a table 'Containers', and a column 'Name' in this table, then you can use as the list source =Containers[Name] for the range reference on the standard named range.  Excel forces you to do it this way rather than to default to picking the first column of the table, and you can't directly reference the table.  But if you don't mind the extra step, it's as flexible as you need it to be.

    Because of this, it makes sense to give the table a simple name, and then create a standard named range named as TableColumn (for example: Container and ContainerName), and have data validation for list sources use the =TableColumn standard column ranges.
  • Indirect Sorting - Even in Excel 2010, you can't just select the entire sheet where the table sits and press the sort button to default to sorting that table.  You first have to select a cell in that table (any cell) - the sort button is grayed out until you do that.  The odd thing is that if the worksheet does NOT have a table, you can sort the data by selecting the entire worksheet, but the button is grayed out if it has a table.  Just an extra step to go through.
  • Naming Conventions - While you can name a table with the same name as the worksheet on which it appears, if you do so then explicit references to the worksheet name will have to be enlosed in quotes so that Excel can tell the difference between the worksheet and the named range.  If instead you always name worksheets with user friendly / plural names which are different than your singular / fixed / programmatic table names, then you avoid this problem.  Another naming convention would be to always prefix the table name with T, and column references with C, or add a 'Table' suffix to the end of the table name, and 'Column' to the end of the column names.  Choose a naming convention and be consistent in using it within a workbook.
  • Single Row Headers - Sometimes you might want a table which has a header row at the top, and one or more statistical total rows just below it, and you want sorting and freeze panes to keep these statistical rows at the top when you scroll or sort. There is a 'Total Row' on the Design tab, but it is only partially configurable: you can edit the formulas on the total row, but it can only add a single row and it must be at the bottom of the table. Having the row on the bottom of the table gets in the way of data entry of new rows.

    A second possible solution would be to fake the effect as follows: add a new column to the table called Sort, or whatever, and fill in values for it only on the header rows, in the order that the header rows should be displayed, and then hide this column.  Then change the sorting so that the table is sorted first by this column and then by the rest of the columns that it was previously sorted by.  This will keep the header rows at the top in the order that you want them.  Then change the freeze panes to freeze all of these 'header rows' and not just the first row of the table.  This will keep them visible even when you scroll.  However, this solultion makes formulas really difficult, because you can't just sum the values in a column - you will get circular references because the header rows are part of the table.

    A third solution is to put the header rows above the table.  This is much simpler in that it doesn't require a special sorting column to keep the extra header rows at the top, it doesn't interfere with adding new rows, and it keeps the header rows out of the table so that you don't have circular references.  The only problem with this is that it does not look as clean as putting the row(s) under the header.  And, you can't simply merge the the second and third approaches and have both because then you have circular references in formulas again.  However, if you take some extra time to format the above header rows, they can be made to look more presentable.  One visual trick here to copy the duplicate the header row so that it is both above and below the total rows.  This makes it look like a standard table, but with a split at the top between summary rows and data rows.
  • Relative Structured References - If you have a formula that refers to a column in the same table by way of a structured reference (using the name of the column), than that reference is relative.  If you try to drag that formula to the left or right using auto-fill, the name will be changed to the name of the next column in the table, which is usually not what you want.  Instead, you should usually explicitly copy and paste formulas between columns and hand-edit each column's formula to be correct.  This only has to be done once per column, so it is not overly time consuming for most kinds of tables.
  • Limited Data Validation and Conditional Formatting Support - Although you can specify a custom formula for data validation or a conditional formatting rule, if you try to used structured references with the formula, Excel will tell you that you have a syntax error.  In other words, structured references aren't supported here, except for the 'List' option of data validation.
  • Broken Formulas - When you turn a range into a table, Excel looks at the formula in each column and chooses one as the default for new rows.  If all the cells in a column of that new table have the same formula, then Excel creates that column as a calculated column.  However, if some cells disagree, then that column will have formulas and exceptions, and the column will not be a calculated column.

    If you end up with such a column and want to standardize all the cells to a single formula, enter the desired formula into one of the cells.  If it already has that formula, then enter a variation of that formula that does the same thing.  When you enter that formula in Excel 2010, a formula tooltip will appear to the lower right of that cell - use the option there to Overwrite all cells in this column with this formula.  When you do so, the column will now be a computed column where all the cells in that column now are standardized to that formula.  You can then edit the formula in any one of the cells, and all cells will be updated.
  • Broken Row Formatting - When you create a table, the format of the cells in the last row is used as the format to apply to all new rows added to the table.  This can be a problem if you have a format other than the one you want to use in the last row.  If you find that new rows are getting the wrong format, the fix is a little more drastic than fixing a broken column, and it takes a lot longer:

    First, fix the row formatting so that the last row has the format you want.  Then, save a copy of the workbook.  Then open both the copy and the original side by side.  In the copy, convert the problem table back to a range, and then convert it back to a table, naming it as it was before, and choosing the table format that you had before. 

    Then copy all cells from the table in the original file, and first paste formulas only to the copy file, then paste formatting only to the copy file.  Now go back and fix any formulas that refer to the original workbook, and re-apply any formatting differences and column widths.

    From here, you should be able to add new rows to the table in the copy file, and the formatting will be correct.

With this much in mind, these are the advantages of using tables over named ranges:

  • You no longer have to manually fill in formulas when you extend your range of data, and so you no longer have cases when newly added rows are missing formulas.  Also, since you no longer have to fill in formulas ahead of time, the table / range is always only as big as your data - you don't have to create blank rows having only formulas, which leads to rows being skipped when a filter is applied and new rows are added to the bottom.
  • You no longer have problems where named ranges for data validation contain blanks cells past the end of the table, or are missing newly added columns.
  • You no longer have to manually apply cell formatting or data validation to new rows.

And here is a set of best practices for working with Excel tables:

  • Simple Column Names - Column Header names should be simple, not containing explanations or units.  This makes the formulas in the table simpler to read.  Instead of putting the extra documentation in the column name, insert a comment on the header cell to explain the column.
  • Freeze Panes - Usually you will only have only one table per worksheet.  You will usually want to turn on freeze panes so that the header and rows above it are frozen in place.
  • Structured References - Use structured references in place of row and column numbers wherever possible.  This makes your spreadsheet more resistant to becoming broken as columns are moved.
  • Naming Convention - Use a naming convention for worksheets, tables, and named ranges, and stick to it.
  • Formatting Style - Auto-formatting of tables is very good in Excel, use it wherever possible and only override the default format for certain special rows or columns.  Banded rows improve readability a lot - use them.  It is also sometimes good to color code auto-calculated columns (having formulas) so that it is apparent that data should not be entered into these cells.
  • Consistent Formulas - Make sure all the formulas in all computed columns are consistent before turning the range into a table.  If you create the table and the forumlas are not consistent, then new rows might not get the correct formula in the computed columns, and fixing the problem isn't always easy.
  • Conditional Formatting - Rather than explicitly setting a format on individual rows in a table, define rules on columns which dynamically examine cell contents to determine when the formatting is to be applied.  This both makes the table more maintainable, and also keeps Excel from getting confused about what format to apply to new rows (because if Excel does get confused, it is extremely hard to fix the situation).