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.
Tips
How-to
News
Videos
Stories
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.
Comments: (22) Collapse
i just wish there was a way to turn this "auto table" feature off. they assume, because i open a workbook with a table, i want it banded with filters. well, most of the time i don't.
worksheets("Sheet1").autofiltermode = false would turn the filter off. but not in this "Auto table" mode. it has no idea the filter is on and will return false.
1) Pivot tables don't compression when using Winzip or WinRar. Is there a solution here?
2) I want to be able to customize the buttons on the ribbon. When trying to format a number I like to see numbers in $321.00 format, but the default is $ 321.00. So I manually have to format cells every time. This is a pain.
3) Paste special format seems inconsistent when coping pivot tables.
4) The graphing package inside of Excel is the one new feature that if improved, I would pay for the upgrade.
5) Cleaning data is much easier with a pivot table view. I would like to have a model where I could clean from within the Pivot and have it update the raw data.
I can't get the first mentioned feature, autofilter in header, to work in the public beta. Still works in Tables.
"I can't get the first mentioned feature, autofilter in header, to work in the public beta. Still works in Tables."
Jim, isn't that a contradictory statement? It's not like autofilter in header works outside of tables.
The new additions to tables are welcome:
1) No more need to use "Freeze Pane" to keep the header visible while scrolling, rendering the column names in sheet header feature useless (unless you never need to filter a column).
2) The new Autofilter search functionality is amazing!
3) With the new ThisRow shorthand, I can now read my formulas that use structured references.
My top table requests for Excel 15:
1) Save table as XML with XSD file.
2) Ability to create a linked, normalized table from a cross-tab data entry table. The normalized table can then be used as a linked table in PowerPivot. To double my chances here, on my PowerPivot v2 wish list, I've requested the ability to convert a cross-tab table to a normalized table when linking to, or querying an Excel table :)
My biggest complaint about Tables in 2007 is that if you want the source data to be an external text file that is automatically imported into the worksheet, you can't use a Table with it. However, you can use an external database table imported into the worksheet as a data source for the Table. Do Tables in Excel 2010 support using imported text files as data sources?
I aggree with gkeramidas - the autofilter menus are a total pain, and often useless in the data area. The place they make sense more often is in the first column, with the row labels. I love the formatting, but those damn menu arrows make it impossible to read the column labels. There should be an off switch on each one of them, so people can turn off the ones that don't make sense.
The biggest problem with Tables in both 2007/10 is that they are not truly dynamic.
1 Create a table.
2 Leave a couple of rows gap and add data.
3 Delete the blank rows.
The table does not expand.
If I had created a Dynamic Name for the table it would have.
Also Table Names dont work directly in the list option of data validation
Nice, Ben, Joseph.
A (simple) missing formatting feature we had talked about in this blog for xl2007 and that is still valid for 2010:
- In your table, cell A6 shows 'Murkowsk'.
- Say the full value is 'Murkowskaski'.
- Because B6 contains 'Lisa', A6 shows only the beginning of lastname.
Now, if you clear the value in B6, then 'Murkowskaski' would cover A6 and B6.
This behavior makes report sometimes unreadable because the data columns do not look aligned anymore.
It would be nice to have an option that would make sure that the displayed string doesn't overflow onto the adjacent cell, whatever the adjacent cell value is. (Maybe the a tri-state wrap/do-not-wrap/do-not-expand)
(... in the end, i just want to see the lastname partially, 'Murkowsk', in A6 only, even though B6 has no value.)
tables and ranges in Excel 2007:
inside a range you can insert a new line with mouse and key: shift
or copy a line and insert it with mouse and keys: Shift and Ctrl
in a 2007 table, you can't.
it was possible to do this inside a "database range" in previous versions of Excel, this was handy.
you can still insert a line of cells in a 2007 table with a tool named CellsInserDialog.
Issue with adding table rows when multiple tables are above each other (right-click > insert > row below) :
- If topTable is wider than bottomTable, it works fine.
- If topTable is narrower than bottomTable, then it fails.
This is because (i suppose) a table inserts a table-row and not a full sheet row.
It would be nice to have the choice so we could have multiple dynamic tables above each other. Maybe a table setting?
gkeramidas, Walter, Colin, Sean, Mark, Sam, Sebastian, Frantz: Thank you all for the feedback!
Please introduce at shortcut to this common need to display "" instead of zero (0) in formula results. NOTE: I know the "supress the zeros" feature, but there are cells where I really want Zeroes to be displayed
This is pain:
=IF(LEN(A2)>0, A1, "")
See answers.yahoo.com/.../index
"Please introduce at shortcut to this common need to display "" instead of zero (0) in formula results."
You can use custom number formatting to do what you want.
DSUM/DGET etc cant handle table names as the header row is excluded in a Table name
Comments: (loading) Collapse