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.
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
- 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
- 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
- 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
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
- 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
- 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
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.
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
- 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