When new versions of Microsoft Office applications are released there are usually some important headline features. However, it is often some of the less prominent changes that can have the most enduring practical benefits. Sparklines, Slicers and the PowerPivot addin are probably the superheroes of Excel 2010 but the unobtrusive and much misunderstood tables feature could well come to your rescue more often, says Simon Hurst, FSN writer and spreadsheet guru. His book “Accounting spreadsheets to the MAX®” is due to be published by FSN early next year.
Tables are not new in Excel 2010, they were introduced in Excel 2007 and preceded by the similar, and similarly underused, ‘Lists’ feature that first appeared in Excel 2003. Tables do themselves no favours by hiding under the cloak of ‘Format as Table’ in the Styles group of the Home ribbon. It’s therefore little surprise that many see them as little more than a way to satisfy the nostalgia for green-listing paper. However, tables are a lot more than just a pretty format.
Filter tips
Apart from the formatting, one of the most obvious results of turning a block of Excel cells into a table is the addition of filter dropdowns to each column heading. These filters are not the mild-mannered Excel 2003 filters that merely let you choose a single existing item or leave you to your own resources in the face of the manual custom sort option. These filters unleash the full power of Excel’s new Data, Filter capabilities.
The Excel 2010 filter allows you to select multiple individual items and, if you need a more complex filter, provides a list of pre-set alternatives such as ‘Begins with’, ‘Ends with’ and ‘Contains’ for columns of text. The filter also adapts to the contents of the column – when it senses number or date values it transforms itself automatically. For number columns a number filter option appears containing filters such as ‘Greater than or equal to’, ‘Between’ and ‘Below average’. For dates, the Date filters option includes ‘This month’, ‘Last quarter’, ‘Next year’ and ‘Year to date’.
A new feature in Excel 2010 allows the list of available filter values to be restricted to values that match whatever is typed into the ‘Search’ box in the filter drop down. This would be very useful for columns that contain a large number of different values.
That’s not all, if you change the font or cell fill colour, either manually or via the use of conditional formatting, then the ‘Filter by Colour’ option becomes active:
Colour, sorted
It is not just filters. As well as standard ascending/descending sorts you can also sort by colour using the same set of options as filter makes available but with the addition of a custom sort option which allows you to define the exact order in which you want your various cell attributes to be sorted:
It’s the little things
It might not seem much, but the increased information on the applied filter and sort settings in each column heading is a significant improvement. The rather insipid change of the Excel 2003 cell heading dropdown from black to dark blue was easily missed as an indication that that column was subject to a filter. In Excel 2010 a filter ‘oil-funnel’ is used to indicate the presence of a filter, and a small arrow indicates the direction of a sort:
Note that in Excel 2003 and 2007 and 2010 the other clue that a block of data is filtered is the change in the colour of the row numbers from black to blue.
Totals
The filter and sort options available in the table column headings are not unique to tables, they can be used in any block of data by applying the Data, Filter option. The next set of super powers are more directly related to Excel tables.
First of all totals. You can include a totals row at the bottom of your table of data by right –clicking anywhere in the table and choosing the Tables, Totals Row option. This adds a totals row to the bottom of the table. Clicking in any cell in that row will display a dropdown of the various summary functions available such as Sum, Average and Count.
AutoExpansion
Perhaps the most significant capability of Excel tables is AutoExpansion. If you enter something into the column immediately to the right of the table the adjacent column will automatically be incorporated into the table. This is the default behaviour. A smart tag will appear allowing you to cancel the AutoExpansion or even to turn the AutoExpansion feature off entirely. If there is no totals row, then the same will happen when you type something into the row immediately beneath your table. If you do have a totals row, then pressing the tab key twice in the bottom right-hand corner cell, immediately above the totals row, will insert an additional table row above the total.
This is not all that AutoExpansion can do. If your entry in the adjacent column is a formula, then that formula will be copied to all the other cells in the column (again, with a smart tag undo option). If all the entries in a column have the same format, then additional rows will inherit the column formatting – including conditional formatting. The same applies to data validation – if an entire table column has the same data validation applied, new rows will be subject to the same data validation settings.
Table formulae
As mentioned, a formula entered into a column adjacent to the table will be copied to all other cells in that column of the table. If the formula relates to other cells in the table, then it will not be created as a normal cell reference type formula, but will instead be a ‘structured’ formula. For example:
=[@UnitPrice]*[@Quantity]
UnitPrice and Quantity are column headings and the ‘@’ is shorthand for ‘this row’.
Tables, columns and modifiers are included within the formula AutoComplete prompts:
If a formula is created outside of the table then, if it refers to table cells on the same row, then the table name will also be included in the reference:
=Northwind_Invoices[@UnitPrice]*Northwind_Invoices[@Quantity]
In Excel 2007 the formula is rather more complicated, incorporating the table name, even if the formula is within the table, and a more explicit ‘ThisRow’ reference:
=Table1[[#This Row],[UnitPrice]]*Table1[[#This Row],[Quantity]]
It is also possible to create references to entire table columns:
=SUM(Northwind_Invoices[Freight])
Generally when you refer to a table column you will just want to include the contents of the column without the header and total, and this is what a straightforward reference to the column name does. However, you can also refer separately to all the different elements of a column using the following ‘modifiers’:
#All – for the whole column including header and total if present
#Data – just the data cells – no header and no totals row
#Headers – just the header
#Totals – just the total
To refer to a column with its header the formula would be:
=COUNTA(Table1[[#Headers],[#Data],[Order value]])
This is slightly safer than:
=COUNTA(Table1[[#All],[Order value]])
Which could include a total if a totals row was added.
In part two of this series I will be looking at how some of these features can be applied in practice.
It’s the little things
It might not seem much, but the increased information on the applied filter and sort settings in each column heading is a significant improvement. The rather insipid change of the Excel 2003 cell heading dropdown from black to dark blue was easily missed as an indication that that column was subject to a filter. In Excel 2010 a filter ‘oil-funnel’ is used to indicate the presence of a filter, and a small arrow indicates the direction of a sort:
Note that in Excel 2003 and 2007 and 2010 the other clue that a block of data is filtered is the change in the colour of the row numbers from black to blue. Totals The filter and sort options available in the table column headings are not unique to tables, they can be used in any block of data by applying the Data, Filter option. The next set of super powers are more directly related to Excel tables. First of all totals. You can include a totals row at the bottom of your table of data by right –clicking anywhere in the table and choosing the Tables, Totals Row option. This adds a totals row to the bottom of the table. Clicking in any cell in that row will display a dropdown of the various summary functions available such as Sum, Average and Count. AutoExpansion Perhaps the most significant capability of Excel tables is AutoExpansion. If you enter something into the column immediately to the right of the table the adjacent column will automatically be incorporated into the table. This is the default behaviour. A smart tag will appear allowing you to cancel the AutoExpansion or even to turn the AutoExpansion feature off entirely. If there is no totals row, then the same will happen when you type something into the row immediately beneath your table. If you do have a totals row, then pressing the tab key twice in the bottom right-hand corner cell, immediately above the totals row, will insert an additional table row above the total. This is not all that AutoExpansion can do. If your entry in the adjacent column is a formula, then that formula will be copied to all the other cells in the column (again, with a smart tag undo option). If all the entries in a column have the same format, then additional rows will inherit the column formatting – including conditional formatting. The same applies to data validation – if an entire table column has the same data validation applied, new rows will be subject to the same data validation settings. Table formulae As mentioned, a formula entered into a column adjacent to the table will be copied to all other cells in that column of the table. If the formula relates to other cells in the table, then it will not be created as a normal cell reference type formula, but will instead be a ‘structured’ formula. For example: =[@UnitPrice]*[@Quantity] UnitPrice and Quantity are column headings and the ‘@’ is shorthand for ‘this row’. Tables, columns and modifiers are included within the formula AutoComplete prompts:
If a formula is created outside of the table then, if it refers to table cells on the same row, then the table name will also be included in the reference:
=Northwind_Invoices[@UnitPrice]*Northwind_Invoices[@Quantity]
In Excel 2007 the formula is rather more complicated, incorporating the table name, even if the formula is within the table, and a more explicit ‘ThisRow’ reference:
=Table1[[#This Row],[UnitPrice]]*Table1[[#This Row],[Quantity]]
It is also possible to create references to entire table columns:
=SUM(Northwind_Invoices[Freight])
Generally when you refer to a table column you will just want to include the contents of the column without the header and total, and this is what a straightforward reference to the column name does. However, you can also refer separately to all the different elements of a column using the following ‘modifiers’:
#All – for the whole column including header and total if present
#Data – just the data cells – no header and no totals row
#Headers – just the header
#Totals – just the total
To refer to a column with its header the formula would be:
=COUNTA(Table1[[#Headers],[#Data],[Order value]])
This is slightly safer than:
=COUNTA(Table1[[#All],[Order value]])
Which could include a total if a totals row was added.
In part two of this series I will be looking at how some of these features can be applied in practice.
