You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
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. 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.
sam: you can include the header row using the [#Headers] modifier. For example: =Table1[[#Headers],[#Data]] will return the entire table range including headers.
Thanks... Also noticed you could say
sam: Correct. Just be careful with [#All] because it will grab the Total Row as well if it is enabled.
Are there any plans in the works to allow references to table rows (other than #this row)? Say by their index number?
I would also like to see the option of referring to columns by either name or index number. I understand that a number would be less dynamic (say a column were added in it's place) but in many applications that is not an issue.
Are there any plans to set up row-based tables?
What about tables that require both unique column and row headers, so that any cell in the table may be referred to by column and row name?
Distinct Count for Pivot Tables! I don't give a hang about any other feature until this is added.
in Excel 2007, Tables and WebQueries were mutually exclusive - you can't have both (I tried turning a Webquery into a Table, but would loose the connectivity)
Is this resolved in XL 2010? If not is it in the roadmap for a KB note/patch?
Pivot Table 2007 display field only in Grand Total
Dear Excel experts, I have a Pivot Table (Excel 2007). Example, the table consists of two Colum Labels (Expense 1 and Expense 2). Values under the Column Labels are Actual Expense, Year to Date – YTD Budget and Full Year Budget. All Values (Actual Expense, YTD Budget and Full Year Budget) are totaled in the Grand Total. However, Management want to see Full Year Budget only in Grand Total (not under Expenses 1 and Expense 2).
The table will look like this: Expense 1 (Actual Expense, YTD Budget), Expense 2 (Actual Expense, YTD Budget), Grand Total (Actual Expense, YTD Budget and Full Year Budget).
I was manually hiding those values. Is there a way to automate this?