Simon Hurst sets out in this ExcelZone guide to improve automation, accuracy, and ease of use of Excel spreadsheets through the use of the Tables feature that was introduced in Excel 2007.
Although at first glance Excel Tables may look to be just concerned with formatting, they do a great deal more than just apply different colours and borders to a block of cells. Perhaps the most important attribute of an Excel Table is its ability to automatically expand to include any adjacent rows and columns into which data is entered. This means that, unlike a 'normal' Excel range, a reference to a Table column can adjust automatically to include new rows.
Tables have lots of other useful features. For example:
- Formulae and formats can be copied automatically to the other rows in the same column and will extend to new rows added to the Table
- Filter and sort dropdowns are automatically added to the Table headings row
- Tables can be given meaningful names which are used as part of the structured formula language available to create references to Table contents
- Slicers can be attached to Tables (from Excel 2013 onwards) to allow Tables to be filtered in the same way as PivotTables
- Tables can be used to quickly remove duplicates from a list
Creating a Table
There are three main ways to turn an existing block of cells into an Excel Table:
- Insert Ribbon tab, Tables group, Insert Table, Table
- Home Ribbon tab, Styles group, Format as Table (this option allows the Table style to be chosen at the same time as creating the Table)
- Control +t keyboard shortcut
Once a Table has been created, selecting any part of the Table will display the Table Tools, Design Ribbon tab which includes the tools available for working with Tables:
In this example using the keyboard shortcut, we have selected a single cell in our block of data before pressing Control+t. Because there are no blank rows or columns in our range, Excel correctly identifies the Table range and prompts as to whether the top row contains headers. Once we click the OK button our Table is created and the Table Tools, Design Ribbon tab is displayed. The Properties group at the left includes the Table Name: text box allowing us to enter an appropriate name for our Table. We will consider Table names in more detail later, but for now we will just name our Table: tblExpenseTypes.
We can remove our duplicate 'Travel' item using the Remove Duplicates option in the Tools group. For Tables with multiple columns, more than one column can be selected and only rows where the contents in all the selected columns are duplicated will be removed:
We have added a Table of detailed expenses to our example and have given it the name tblExpenses. Our Total cell refers to all the cells in the Values column. If we create our formula by dragging the required range, the reference will use the Table and Column name to create a 'structured' reference:
We have also added Conditional Formatting Data Bars to the Value column and Data Validation to the Analysis column based on Allowing entries from a List the source of which is set to our Expense Type Table:
When we enter additional data in the cells immediately below our existing expenses Table we can see that our total adjusts automatically to include the new amount entered in the Value column; the Conditional Formatting Data Bars are automatically copied to our new Value cell and our Data Validation settings are also copied to the new cell in the Analysis column:
Switching back to our expense types Table, we have entered a formula that uses the SUMIFS() function to calculate the total expenses values for each of our expense types. We can just enter this formula in the top row of the column and, when we accept it, it will be automatically copied down to the other rows in the column, with an AutoCorrect Options dropdown button allowing us to change this behaviour just this time, or to change the default behaviour:
Note that the formula again uses structured references and that, where we need to refer to the current row in the Expense type column rather than the entire column, the @ is used to mean 'This Row' (Excel 2007: [#This Row]).
If we add a new item immediately beneath our Expense type column, our formula will automatically be copied to the new row:
In our example, we have included both Tables on the same worksheet. This is often not a good idea, as a Table might need to expand into adjacent rows and columns, threatening an overlap with another Table unless rows and/or columns are inserted manually. However, there is an issue with putting Tables on different sheets. Our Expenses Table, Analysis column uses Data Validation with the List source based on our Expense types Table. You might have noticed that, when entering the source for the list, Data Validation did not use a structured reference but instead just used a standard Excel range. Where the Data Validation cells and the source Table are on the same worksheet, auto-expansion still works despite this. Our Data Validation list will now include our new expense type:
However, if they were on different worksheets this would not be the case and our new item would be excluded from the list. To address situations where structured references are not available, you can allocate a Range Name to the contents of a Table column. Table auto-expansion will ensure that the range that the name refers to will expand, wherever in the workbook the Table is. The Range Name can then be used instead of the reference in the Data Validation source or wherever else it is required.
The incomplete implementation of structured references is not the only issue in working with Excel Tables. A potentially more significant drawback is the inability to use cell locking and sheet protection with Tables. As soon as the Review Ribbon tab, Changes group, Protect Sheet option is turned on, regardless of the detailed settings, it is no longer possible to add rows to any Table on the sheet. This prevents locking the formula cells in a Table in order to prevent them from being changed, if there is also a need to add new rows. The Data Ribbon tab, Subtotal command is also not available within a Table.
Using Tables also prevents you from Sharing a workbook using the Share Workbook command in the Changes group of the Review Ribbon tab, and you cannot move or copy a range of worksheets if any one of them contains a Table.
Copying structured references
If you use the fill handle to copy a formula containing a structured reference across columns, the reference will be treated as 'relative' and will change to refer to different Table columns. If you want to keep the references 'absolute', select the cells to copy, and the cells to which you want to copy them, and use the 'fill right' keyboard shortcut: Control+r.
Although it is easy enough to use a mouse click, or click and drag, to create references from cells on the same worksheet as the target Table, this is not quite as quick and easy if you need to refer to a different worksheet. As an alternative you can leave Excel to do most of the work. If you start typing the name of your Table in any formula in any worksheet in the same workbook as one or more Tables, the Table name will appear and you can select it from the AutoComplete list. You can then type an opening square bracket to see the column headings and modifiers:
To make the AutoComplete process easier if you have many Tables in a workbook, particularly if there are also a lot of Range Names, it can be a good idea to use a standard prefix such as 'tbl' for all Table Names – this can make it much easier to distinguish your Tables from functions and Range Names in the AutoComplete list.
It's easy to forget to give your Table a sensible name when you set it up – adding the Table Name: text box to the Quick Access Toolbar makes it much easier to access.
This has not been an exhaustive examination of all that Tables have to offer, and all the issues with their use, but will hopefully be enough to show that there is a lot more to them than just a quick way to apply formatting.