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

EZ guide to Excel Tables

by
9th Sep 2016
Save content
Have you found this content useful? Use the button above to save it to your profile.

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.

Overview

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:

Source: Simon Hurst
Source: Simon Hurst

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:

Source: Simon Hurst
Source: Simon Hurst

Auto expansion

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:

=SUM(tblExpenses[Value])

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:

Source: Simon Hurst
Source: Simon Hurst

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:

Source: Simon Hurst
Source: Simon Hurst

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:

Source: Simon Hurst
Source: Simon Hurst

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:

Source: Simon Hurst
Source: Simon Hurst

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:

Source: Simon Hurst
Source: Simon Hurst

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.

Tables drawbacks

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.

Table Tips

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.

Source: Simon Hurst
Source: Simon Hurst

Reference AutoComplete

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:

Source: Simon Hurst
Source: Simon Hurst

Table names

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.

There's more

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.

Tags:

Replies (4)

Please login or register to join the discussion.

avatar
By Fastlane
23rd Sep 2016 15:41

In my experience with XL2007 & 2010, cell formats are NOT automatically replicated when a new row is added to the bottom of the table (whereas formulas are).

Other drawbacks are:
1. Any formula that references a table via a structured reference becomes unwieldy if the field/column heading contains several words. The alternative is to use short, but far less meaningful column headings!
2. Using formula to generate a dynamic field/column heading is NOT permitted.

Thanks (1)
Replying to Fastlane:
Simon Hurst
By Simon Hurst
23rd Sep 2016 21:56

Thanks Fastlane. In my Excel 2007 and 2010 cell, number and conditional formats are copied to new rows if they have been applied consistently to all the existing rows in the table column. Was there a particular type of format you have found that doesn't copy correctly?

You are absolutely correct about not being able to use formulae in headings - I should have included that in the drawbacks. In practice, I have not found using short column headings to be too much of an issue, but I can see how that might be a problem in some situations. Even with the drawbacks I still find Tables can be very useful.

Thanks (0)
avatar
By Fastlane
25th Sep 2016 04:00

I haven't noticed any pattern in regard to which formats are replicated and which are not - but am now wondering whether it could be native/built-in formats vs custom, so will keep an eye on it, and maybe do some experiments.

Re field headings: I quite often use formulas for column headings of tables (whether "structured" or simple matrix) , either just to replicate a heading used earlier in the sheet/model (particularly for time-series headings) or when wanting a dynamic label (e.g. include company name, date, etc.), so was very disappointed with this limitation - especially when the reason for it hasn't been explained by Microsoft (at least not to my knowledge).

Thanks (0)
Replying to Fastlane:
Simon Hurst
By Simon Hurst
26th Sep 2016 10:56

I haven't come across an 'official' reason either but would guess it could be due to complicating the recalculation chain that Excel uses to know which cells need to be recalculated when a change is made. I wouldn't disagree that, as useful as Tables are, there is still room for improvement particularly regarding protection as in this Excel UserVoice suggestion: https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-appl...

Thanks (0)