Table Improvements in Excel 2010

Thanks to Ben Rampson for putting together this post.

In today’s article I will outline three features in Office 2010 that improve table interaction in Excel.  These feature additions build on the work started Excel 2007, addressing areas with significant customer feedback and further improving the feature area.

The first table improvement feature, AutoFilters in sheet headers, builds on work completed in the previous version of Excel.  In Office 2007, when the table header of a selected table is scrolled outside the visible range of a sheet, the table column names snap into the sheet headers for display.  This functionality is extended in Office 2010 to also include the AutoFilters dropdowns in the sheet headers.  This change enables customers to easily access the sort and filter functionality of the AutoFilters on tables without requiring that they continually scroll back to the top of the table.

The left image below shows a screenshot of Excel with a simple table.  The right image shows the same Excel spreadsheet scrolled down such that the header row of the table is outside the visible range of the sheet.  As can be seen circled in red, the sheet headers are modified to display the table column names as well as the AutoFilters dropdowns in this situation.clip_image002
The second feature that improves interaction with tables in Excel 2010 is the introduction of search in AutoFilters for tables.  Customers frequently work with tables containing very large data sets in Excel.  These data sets can be difficult to navigate and explore in previous versions of Office since all members of the field were displayed in the filter dropdown.  Excel 2010 provides search functionality built into the AutoFilters dropdown, allowing fast navigation to apply the desired filter.

The screenshots below shows the search functionality of the AutoFilters dropdown in action.


Additional details about this functionality is available in this previous blog post.

The third table improvement feature in Excel 2010 helps to simplify the notation used in table formulas, making the formulas more compact and readable.  In Excel 2007 the “[#This Row]” notation was used in table formulas to signify the referenced cell came from the same row in the table.  This notation required the explicit mention of the referenced table, often making even simple formulas very long.

In Office 2010 this notation has been condensed to improve the user experience.  The “[#This Row]” notation has been replaced with an “@” notation in formulas and, where possible, the requirement for explicit table name references in the formula have been removed.  This change can be seen in the screenshots below, showing  a formula in Office 2007 on the top and the same formula in Office 2010 on the bottom.


It should also be noted that this functionality change only impacts the display of the formula.  The file format is not impacted by this modification and the files will continue to work as expected with previous versions of Excel. The“[#This Row]” notation is still used in the file format when saving from Excel 2010, keeping in compliance with the file format standards.  Also, either the “@” or “[#This Row]” notations can be used when entering formulas in both the UI and OM, however, they will be modified to the “@” notation for display in the UI once the formula is committed.

In conclusion, these three changes to the tables functionality should have a significant impact on improving the user experience and efficiency of working with tables in Excel 2010.  Please feel free to leave any comments or questions on this area that you may have.