Tables Part 2: Stickiness, Structured Selection, And More

re: Tables Part 2: Stickiness, Structured Selection, And More

Lars Hammarberg — Tue, 01 Nov 2005 21:35:57 GMT

I'd like to see a "key" designator in a table - that is, select a column and make that column a "key" column (checking/constraining the data in the column are unique entries - could even be autonumbers!), thus facilitating linking the table data to other apps and making write-back quite simple.

re: Tables Part 2: Stickiness, Structured Selection, And More

SteveA — Sat, 29 Oct 2005 14:23:46 GMT

Hi David

Regarding data types

It would be good to have 64bit and possibly 128bit integers. Possibly useful for people working with 64bit computers, less for the maths more for bit twiddling. Sadly my days of doing such work are long gone :-)

Sometime ago I did hit the current integer limit, but can't remember now what I was doing that gave me the problem.

Also proper support for decimal data type would be cool rather than having it stuck in variants.

On the help, I will check out the seperate blog. But as an additional example. To find the help for data types, I first looked in the excel help with no luck, so then invoked the visual basic editor and look in the help their.

re: Tables Part 2: Stickiness, Structured Selection, And More

David Gainer — Sat, 29 Oct 2005 06:49:21 GMT

SteveA - "We're making Developer content a separate search scope, both for offline content and online content" ... see blogs.msdn.com/mikekelly

Orion - Intersting questions, and I agree - there is plenty of exciting work to be done. We have some thoughts on controls, but nothing in Excel 12. Oh, and yes to your two filter questions - give me two more posts.

re: Tables Part 2: Stickiness, Structured Selection, And More

Orion Adrian — Sat, 29 Oct 2005 01:21:36 GMT

Another thought.

Currently when you filter down a list it hides full rows. While this may be occasionally useful, treating each table as a separate canvas and just showing and hiding cells in that table and not tables around it.

Again this comes back to the canvas in question; and a complaint I've had about Excel and all spredsheets for a long time. Excel makes me think about the canvas (the worksheet) far too often. For example I may want to add row to a table on worksheet, but often it can destroy tables under it. Similarly it adding columns may destroy tables to the right. Deletion has the same effect. Lists even prevent you from deleting a row when another list is below it that is wider than it is.

So my question is why does it work this way? I feel it's because the objects on the canvas are linked through that canvas. As long as they are, then there are going to be problems. This also produces problems when I want to annotate the tables with text or other objects. Shapes and charnts don't use the worksheet as the coordinate system.

I think there is amazing ground to be made by really examining canvas structures. When people ask for more integration in Office I believe it's because they want to really be able to act on these objects and move them around a unified canvas where each object is a first class citizen. I look to Visual Studio's form designer also as a nice tool for laying out things.

Either way, there is still a huge amount of advancement that can be made in Office productivity apps. We're barely touching the surface of where we can go with them.

re: Tables Part 2: Stickiness, Structured Selection, And More

Harlan Grove — Fri, 28 Oct 2005 23:44:16 GMT

In response to Orion,

Leave spreadsheets spreadsheets. It's fine to add database-like functionality to Excel as long as it doesn't have to be used. If I want to use tables exclusively, I'll use Access.

That said, it'd be interesting to be able to replace *SOME* worksheets with tables (thus my questions about tables spanning worksheets), but don't replace them as the default construct.

Unless the new tables provide new formula-based filtering, they're not going to provide any better row referencing than current worksheet cells.

re: Tables Part 2: Stickiness, Structured Selection, And More

Orion Adrian — Fri, 28 Oct 2005 23:03:03 GMT

Has there been any thought to making table the base working unit as opposed to worksheet. I find it interesting that we're finaling moving to a place where the schema is key part of the document and not the canvas.

Any thought to allowing other types of controls on cells other then text boxes and select controls? What about date pickers? Also where do you see Excel ending and Access beginning? With the advent of tables in Excel it seems that Excel is starting to become Access with a better UI, formulas, and charting. My guess is that formulas is really the defining portion, but even that line is being blurred as we start to focus more on tables since it's less and less likely that formulas will change between data rows.

And lastly, two requests: One is can we get filtering past two items on tables? And second can we get sorting based on order in a list of choices.

I have a column in a current Excel list that limits values to one of five states in a process, but when sorting it uses the names of eh states and not the order of the list. This has come up on many occasions for me.

re: Tables Part 2: Stickiness, Structured Selection, And More

David Gainer — Fri, 28 Oct 2005 21:31:00 GMT

Hello there,

Harlan – Yes, a table can span an entire worksheet, although the memory requirements would be huge. Yes, delete entire row also deletes table rows. If you try and insert a range of cells that partially overlaps the table, we prevent the insert, because it would invalidate the structure of the table. Yes, you can do things like INDEX(Table,ROWS(Table),COLUMNS(Table)) – more on referencing tables in next post. To answer your question about the active cell not being on the screen, if the active cell is in the table but the entire table is scrolled off screen you will see the A, B, C headers. If the worksheet row that contains the table header is hidden, the table headers will still appear in the worksheet header space when the headers are scrolled off screen. To answer your final couple of questions, the font in the worksheet header space never changes, and if a table header value is too large to display in the worksheet header it gets clipped. “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?” Yes to your first question, no to your second.

Jean - Yes, if you select an entire table column you can rearrange that column simply by drag and dropping – we reversed the old default (for tables only) that requires holding down shift to “cut and insert”. In addition, Excel allows columns in a table to be rearranged even while in a filtered state. It is possible to use the old trick of pressing ALT+ENTER to insert line breaks, but otherwise the header row is limited to one physical spreadsheet row. You can use the Formatting Rules dialog as a conditional formatting legend at any time. Filter being available on the sheet header is a great idea, but it will not be there in Excel 12. Thanks for the suggestion about filtering using header names.

Mpemba - The answer is “yes and no”. Single-cell array formulas and tables work well together, and as you’ll see in my next post, you can make formulas inside a table grow and shrink with the table itself. Unfortunately multi-cell array formulas are currently not supported inside tables.

Tianwei – Next post on name defining, I promise.

SteveA – Thanks for the toggle headers idea. What specifically are you looking for with respect to data types? I will pass on your thoughts to the team that handles Help. Their GPM also writes a blog that has some info on Office 12 help on it. blogs.msdn.com/mikekelly

re: Tables Part 2: Stickiness, Structured Selection, And More

SteveA — Fri, 28 Oct 2005 20:06:04 GMT

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.

re: Tables Part 2: Stickiness, Structured Selection, And More

Tianwei — Fri, 28 Oct 2005 16:55:44 GMT

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.

re: Tables Part 2: Stickiness, Structured Selection, And More

Harlan Grove — Fri, 28 Oct 2005 16:29:15 GMT

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.