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.
One of the key benefits of tables is how other features in Excel 12 behave more predictably and more like you would expect when a table is present. This is made possible by the fact that Excel knows exactly where the table starts and ends, where the header row is, which cells make up the data and which columns they belong to, where the total row is, etc. So how exactly does this benefit the user? Here are some of the different ways in Excel’s awareness of the structure of your data changes the user experience.
When a user does something to a column of data in a table, it "sticks". What exactly does that mean? If, for example, a user applies a conditional format to an entire column of a table, Excel assumes that the conditional format is always meant to cover the entire table column. So as new rows are added to the table, either in the middle or at the end, and as rows are deleted, Excel will automatically extend or contract the conditional formatting rule appropriately. The rule “sticks” to the column. We already saw this with the example in my previous post, but it doesn’t just stop with conditional formatting. This concept works with just about anything you can apply to a column, such as formatting (anything in the Format Cells dialog), cell protection, data validation, etc. In addition, this behaviour also applies to anything that holds on to a reference, such as formulas, charts and PivotTables.
How do you make something “sticky”? There’s no explicit gesture for this. Excel just assumes that anytime something is done to an entire column, the setting is meant to always follow the column. So, if a user selects a table column and applies a data validation rule, that rule will grow and shrink with the table. Create a PivotTable that uses a table as its data source, and that Pivot Table will automatically pick up new rows from the table. Ditto for charts. To be precise, when I say “entire column” in this context, I really mean all the data rows. It is not necessary to also include the header cell - this way you don’t have to include the header in order to, for example, make the “currency” number format stick to a column for example.
This stickiness also applies to new columns. For example, if a table is used as the data source for a chart, then any new columns I add to the table will get picked up by the chart. In addition, properties set on the entire table, headers, and the total row (more on what a total row is in a later post) are also "sticky".
Now that Excel recognizes things like rows and columns in my tables, it is only natural that it provide simple ways to select those elements of a table. A single mouse click can select one or more rows, one or more columns, or the entire table. To select a column in a table, simply hover the mouse over the top of the header until the mouse cursor turns into a down-arrow and click.
(Click to enlarge)
To select a row, hover the mouse over to the left edge of a row until the mouse cursor turns into a right-arrow and click. And to select the entire table, hover the mouse over the top-left corner of the table until you see an arrow that points down and right and click.
Note that column and table selection have two behaviours. The first click always selects just the data portion of the column/table. This makes it easy to select a column of numbers and apply formatting or other properties to the data in the column. The second click expands the selection to cover everything in the column/table, header and all. This is useful for copy/paste operations or rearranging columns in a table.
If you are a keyboard junkie (like many of the people reading this seem to be), note that there is keyboard support for these selection behaviours. The existing shortcut keys for row and column selection, SHIFT+SPACE and CTRL+SPACE respectively, have been modified to work with tables. For example, pressing CTRL+SPACE once selects all the data rows in a table column. The second press expands the selection to encompass the entire table column, and the third press expands it again to encompass the entire worksheet column.
What Column am I in again?
This is one of my favorites. When working with large data sets, it’s easy to scroll the headers off the screen and forget which column you are looking at, especially if many of the columns have similar data in them. Once you have declared something a table in Excel 12, this problem will go away - when navigating a large table and the table’s headers have been scrolled off the screen, Excel will insert the table headers into the sheet header row where the A, B, C headings appear (see the screen shot below). For example, when I scroll this table upwards …
… the header text shows up in place of the sheet headers.
This makes it much easier to keep track of things without having to split screen/freeze panes. Note, the table headers will only be visible as long as the active cell is somewhere inside the table. If you select outside the table, the standard A, B, C headers return.
More Conditional Formatting Goodness
Back at the start of my conditional formatting posts, I said that one of the things we set out to do was to provide a better experience in Tables - lowering the bar for conditional formatting rules that previously required formulas or fiddling. Here are two features we added to conditional formatting when a user is working with a table.
First, we added a “format entire row” option that appears when defining a conditional format rule on a table.
When this option is turned on, the entire table row will be highlighted when a condition is met within a specific column. Achieving this in the past required more advanced knowledge of conditional formatting. Now you can treat it like any simple rule – just select the olumn you are interested in evaluating, and select this option if you want the result to show across the entire row.
Second, we added a “compare columns” rule to the list of rules available when setting up conditional formatting on tables. This rule allows you to format cells of a table column based on comparisons with values in another column – you can use this, for example, to highlight any rows where the values in the “2005 Profits” column are less than the values in “2004 Profits” column. Again, the fact that Excel knows that there are columns in your table makes it possible to surface very easy UI to quickly set up these sorts of conditions.
That’s it for now. In my next table post I’ll go into all the work we’ve done around tables, formulas, and referencing.
I guess you are probably going to cover this in your next post!
but presumably these Tables does not suffer from the same sort of problems as the Accept labels in Formulas feature: I hope you have syntax like Table.Row.Column etc?
And I guess there must be a means of resolving conflicts with defined names.
can tables span *entire* worksheets?
If so, will the table's top row of field names automatically replace the standard column headers all the time? Using one's own custom column headings rather than A, B, C, etc. (or 1, 2, 3, etc. in R1C1) is a recurring question in the Excel newsgroups. Full worksheet tables with such automatic or optional behavior would seem to address that set of requests.
For tables that span less than entire worksheets, e.g., a table appearing to cover B2:G25, what happens to the table if the user selects all of row 12 and hides it? Will the 11th row of the table be hidden, or will the table appear to expand down one row, appearing to cover B2:G26? Ditto for inserting entire rows outside of but intersecting the table, and inserting or deleting columns outside of but intersecting the table?
What happens when tables and defined names overlap? For example, with the table above appearing to cover B2:G25, if the containing worksheet's PrintArea is set to A1:H25, will it autoexpand with the table? Or if PrintArea were set to A1:H30, would it expand to A1:H32 if two rows were added to the table?
I don't suppose it'd be possible for y'all to release the new help files ahead of the beta so those of us who were interested could try to figure this out for ourselves?
Thought of another question. What happens to the top row of tables when row and column headers are hidden (Row & column headers unchecked in the View tab of the current Options dialog) and the user scrolls the row covered by the top row of the table off the screen?
For that matter, if a table's top row appears to cover, say, row 101, and the active cell appears to be in the table, what happens when the user scrolls (using the vertical scroll bar) up to row 1 and moves the table outside what's shown in the active window?
All good things, David.
>>Excel will insert the table headers into the sheet header
That is really cool. I can't tell you how many times I've scrolled down, cursed, scrolled up, added a freeze, and scrolled down again. I won't miss that at all.
All really great stuff. A few questions:
1) Are Tables essentially a superset of Lists and therefore we'll be referring to Tables from now on instead of Lists?
2) Seems like things can get confusing with the nomenclature. We already have Pivot Table and Data Table. Now just Table. Have you considered using a more descriptive name that would identify this type of table as something special and different from the exising table types? Also, "Table" sounds very generic. After all, the row and column nature of a spreadsheet make any range a "table", notwithstanding the special features with which the new Table feature is endowed.
Charles – Yes, that will be tomorrow’s post.
Harlan – A table has to be within a sheet, it will not be possible to replace column headings, although that is an interesting idea to think about. The 11th row of the table is hidden. When you insert entire rows, the table picks it up. Currently, print area does not expand unless it is set to the table. What do you think is the right behaviour here? When there are no headers, the standard A, B, C headers stay put. When you scroll the headers back onto the screen, even if the active cell is on the table, the standard headers reappear. And sorry, no help files. You are stuck with just me for a while :-)
Hi Colin - yes, Tables are a superset of Lists. We have found that the word Table works really well at communicating the feature to all classes of users, hence our choice of name.
|A table has to be within a sheet . . .
Can it span an entire worksheet? (If there were sufficient memory.)
|The 11th row of the table is hidden. When you
|insert entire rows, the table picks it up.
I suspected that. Thanks for confirmation. Presumably same for deletion.
With a table spanning B2:G25, what happens if I select E1:H1 and try to insert or delete cells?
|Currently, print area does not expand unless
|it is set to the table. What do you think is
|the right behaviour here?
Since you ask, it'd depend on how tables affect cells adjacent to but not in the tables. For example, if a table initially covered B2:G24, B25:G25 contained something, and Print_Area were set to A1:H25, then if inserting 2 more rows in the table pushed the contents of B25:G25 down into B27:G27, I'd want Print_Area to expand with it.
This wouldn't be much of a problem if it were easy to derive the bottom-right cell of the table. Then I could define Print_Area as
Do tables behave enough like ranges that the bottom-right cell of the table would be given by INDEX(Table,ROWS(Table),COLUMNS(Table))?
But this begs the basic question: if a table, perhaps named MyTable, appears to cover B2:G24, how do MyTable and $B$2:$G$24 differ semantically?
|When there are no headers, the standard A, B,
|C headers stay put. When you scroll the
|headers back onto the screen, even if the
|active cell is on the table, the standard
I'll rephrase. What does the column header show when the active cell is in the table but both table and active cell aren't on the screen? And since tables are subject to worksheet level row height/visibility and column width/visibility, what happens when the worksheet row that appears to contain the table header row is hidden? In that scenario, if you scroll down in the table so that the first data row scrolls above the top of the screen, what appears in the window's column header?
Also, if the table header uses a different font than the row/column headers, will the window column header display the table header in the table header's font or the column header's font?
since I'm on a roll, if the table is narrower than the window, will the column header show the table header only in the table's columns and the normal column letters in the columns on either side of the table? If so, will there be any visual cue to distinguish 1, 2 or 3 letter column names in the table header (e.g., ID) from column letters outside the table?
Great and intuitive behaviours for tables. This will be a lot more productive for every body. I was wondering if these will be possible:
- Move table column just by dragging (without having to do the typical sequence: remove filter, move column, apply filter)
- To have more than 1 line for the header text (like we can typically have in a pivot table). This is only an idea since this could have a big impact.
- To view a visual representation of the conditional formatting directly in the pop up window (like we can se it in the Formatting Rules Legend of one of your previous post). This idea could be also applicable for conditional formatting in general.
(maybe you will answer these on your future table-filter post)
- Could filter still be available on the sheet header when |header text shows up in place of the sheet headers (so we won't have to go back to the top row if we are to far bellow)?
- This is my preferred one. When you have many columns, it could be practical to filter them using the header names (like we can filter rows). For example, let say you have columns 2005-Q1, 2005-Q2,..., 2008-Q4 and you want to see only 2005 ones or only Q1 ones. This could imply to define columns that are alike in order to distinguish from other columns (product, region,...)
Will there be better integration between tables and array formulae?
It's long been a "wish" of mine to have dynamic array formulae a standard part of excel (instead of some functional but kludgy kludges).
Currently insering a row into anything that contains an array vector generates an error. How much better if the system is now bright enough to realise if it refers to a whole table row, table column etc it should be auto adjusted?
I agree with Harlan. Any named-ranges/range-references containing cells both inside and/or outside of a table's range should be updated to cater for the auto-resizing of the table whether adding or deleting row-cells or column-cells to the table.
Although I can see instances where one might want to absolutely anchor the boundries of named-ranges/range-references regardless of table activities, I think more often than not one would prefer it to cater for the table auto-sizing feature.
I might even go further to say that the absolute/relative 'dollarizing' (as it is known where I work) might even require another category (say a '#' as in $D$3:#J#23) where the hash/pound sign would indicate absolute/relative behaviour with regard to table auto-sizing. That way entirerow or entirecolumn inserting or deleting would affect references normally but new data being added to a table covering range $F$6:$J$23 that increased its size to $F$6:$K$30, say, would leave all range references using the '#' notation still fixed to a bottom-right bound of $J$23.
...Um if that makes sense. Thanks,
David, thanks for the great posting. Good work up there in Redmond. A few related and unrelated Qs:
- Table row auto-freeze is good. But can we have auto name defining? ie, when a column "balance" is created in column B, I can immediately go to column C and type a formula use "balance" as the name?
- PrintArea expansion should happen only if additional data rows added, not columns. Many times we use columns to calculate a variable not necessary to print
- Jean's idea of filterable columns is a very practical one. I find myself always needing this function. Especially so since you guys are adding 10K more columns to Excel! To that point, I also find a filterable tabs UI is practical as hiding and unhiding tabs can sometimes be very time-consuming, especially when developing an application where tons of supporting tabs are created
- Is there anyway in this blog where you can group all your postings and make it available as a comprehensive document? (and auto-expand when you add new postings) You are practically writing a book here and I can envision using this document to go to our IT folks demanding an immediate upgrade upon release.
With regard to the comment about upgrading upon release, few if any IT departments would ever do such a thing, and never throughout an entire organization. The new features will be solely for the benefit of the few until most have upgraded.
There's been no equivalent to this since the transition from XL4 to XL5, and Excel/Office were #2 back then. The only Lotus 123 upgrade (back in the late 1980s and early 1990s when it was still #1) close to this was from R2 to R3 in 1989-1991. It took years for everyone to be upgraded to R3. [And I was using XL8 (97) at work up until this last February, and now I use XL10, not XL11.]
Most IT departments are likely to view Office 2006 as an historic & gargantuan PITA.
Harlan, you are right. It's always a bargain game with IT. I can demand; they can ignore. But at least if I scream loud enough they might put me in one of the early adopter lists.
Regarding "what column am I in" and "Note, the table headers will only be visible as long as the active cell is somewhere inside the table. If you select outside the table, the standard A, B, C headers return."
Could this be toggled by some keyboard command. If you have to select outside the table to return the standard A, B, C headers, you could have potentially moved away from the area of interest, particularly when tables are larger than the window.
Different topics, do you intend to add any new data types?
Can you improve the help, so that for example when using answer index or wizard you can narrow the answers down to a particular area. Typically I want to look up VBA related issues for programming, but get results for all of excel. I remember when the help for macros was separate and it seem a lot easier to find information than more recent versions of excel
Keep up the good work though.