# Tables Part 1: Working With Tables Of Data

For the next few posts, I’d like to spend some time explaining the work we’ve done in Excel 12 to improve the experience of working with tabular data in Excel.

One thing that we see pretty much every Excel user doing with some frequency is working with tables.  Tables can mean different things to different people so let me briefly define what we think of when we use the word table.  A table is a simple structure where each row corresponds to a single “thing” (e.g. a specific transaction, an individual product, etc.), and each column denotes a specific piece of information that’s shared by all rows (e.g. amount of each transaction, product quantity, etc.).  Tables typically have a “header” row at the top that defines the information that each column contains. Some examples of tables might be a list of financial transactions or the latest inventory numbers pulled from a server.  Here’s an example of a very simple (and fictitious) table.

(Click to enlarge)

The two-dimensional nature of the spreadsheet makes it an obvious canvas for manipulating and analyzing tabular data.  Excel, however, has traditionally offered very little in the way of features aimed at tabular data because it had no built-in knowledge of what a table is or how it should behave.  We’ve done a lot of work to make tables a native structure in Excel 12.  When Excel knows you are working with a tabular structure, it can react much more intelligently to the actions you perform in the grid.  Let me demonstrate by way of a simple example.  Here is what our table might look like if we formatted the table, applied a data bar to the Profit column, added a chart, and added a formula at the top of the sheet that totals the Profit column.

(Click to enlarge)

The next thing I might typically do is add some more data.  Let’s look at what happens when I type a value just below the table in cell D9.

(Click to enlarge)

After I pressed Enter, several things happened for me automatically:

1. The value that I entered was consumed into the table.  Excel assumed I was appending data to my table (if I wasn’t, one click will tell Excel to not auto-expand the table)
2. When the table consumed the data, my table formatting automatically extended to the new row – and notice that it was smart enough to maintain my alternating row colors
3. The conditional formatting data bar also extended to the new row, so Excel is now using D5:D9 to calculate the relative size of the data bars
4. The formula at the top updated itself to include the new data
5. Finally, the chart updated as well to reflect the addition to our table

Without a table, I would have to manually adjust the cell formatting, conditional formatting, formula and chart every time I append a value.  What would have taken half a dozen steps or more now happens for me automatically thanks to the table feature.  This is because Excel now recognizes features like a table, table columns, table header and so forth, and can use that knowledge to make informed decisions about what to do while I’m working in the spreadsheet.  The best part is that I am just scratching the surface here; it will take me several posts to fully explain the benefits of tables and all the new features surrounding it.  For now let’s cover some basics.

Creating Tables

A table can be created simply by clicking on the Table command on the Insert tab in the ribbon (even quicker, use the CTRL+L shortcut key).  Clicking the Table command brings up a dialog box where you specify the range for your table, and indicate if your data already contains a header row.

(Click to enlarge)

When you create a table, a new tab appears on the ribbon that is specifically designed for tables.  The tab only appears when the active cell is inside a table.  The tab contains options and settings that are geared for tables.  I won’t go into the details right now, but here’s a screenshot.

(Click to enlarge)

Another bit of work that we did was to merge our table feature with the existing external data query functionality, sometimes referred to as query table.  New queries will therefore also benefit from the features of tables (at the moment, web queries and text queries are excluded from this).  I think the benefit of making query tables into tables will become clear over the next week or two as I go over the capabilities of Excel 12 tables.  Note, if you have an existing query, you can convert it to a table using the Ribbon or keyboard shortcuts described above.  Tables are also created when you import XML data using the XML features we added in Excel 2003.

Entering Data into a Table

We already saw in the example above how to add a new row to a table.  Adding a new column works the exact same way.  Place the active cell just outside the table to the right, enter a value and Excel will automatically grow the table to consume the data that you entered.  If this isn’t the desired result, then a simple command will shrink the table back and leave the value outside the table.  This “auto-expand” behavior can also be turned off.

There are a couple other ways to add rows.  Much like tables in Word, pressing TAB when the active cell is in the last column of the last row will cause Excel to add a new row and move the active cell to the first column of the newly added row.  In addition, pressing ENTER when the active cell is in any cell of the last row will also cause a new row to be added.

Finally, if you want a quick way to resize your table to add or subtract rows or columns, click the resize handle in the lower-right corner of the table and drag it in the direction desired.  You can spot the resize handle in the screen shots above.

One of the goals of the table feature is to maintain the integrity of your tabular structure, so the only way to shift cells or create space in a table is to add or insert entire table rows or entire table columns.

By the way, some of this may look familiar if you’ve ever used the list feature in Excel 2003.  The table feature is in fact based off lists, but as you’ll see over the next few days we’ve built a much more comprehensive feature set around tables.

Over the next few days I plan to show you:

• More of the core features of tables
• The great things that happen when tables and formulas collide
• Filtering table data & AutoFilter improvements
• Table Styles and smart formatting behaviors

• Hi David,

Looks nice, seems intuitive.

A detail: how does the alt row shading work alonside conditional formatting? Does the latter override the former, or is the alt row shading done via CF?

Thanks

• Hi Graham - Conditional Formatting will override the row shading (or "Table Style" formatting).  It isn't easy to see in my example, but that's the design.  "Table Styles" are a lot more than alternating row shading - I will cover that in detail in a few posts.

• Boldy adding functionality Lotus 123 provided 6 years ago! Whoda thunk!

Some of this is useful, but some of the rest will be yet more intrusive automatic functionality to be disabled as soon as possible. It will be possible to disable most of this, won't it?

As for the automatic properties you mentioned, just add a line at the bottom of a range containing either ="" or ' in each cell and with number format ;;;"_________", and always insert rows beginning with this row. This already provides the functionality you describe. All you've done is added this to autoenlarging ranges, er, tables.

How does this interact with worksheet protection?

Will tables be accessible as ranges, or will it be necessary to convert them to ranges to access them in formula? I guess I'm anticipating a subsequent post.

• "By the way, some of this may look familiar if you’ve ever used the list feature in Excel 2003."

For me, this was sufficient reason to upgrade for my own use (many clients are still stuck in 2000, so I use that one too). Looks like they're on steroids in 12.

• Hi Harlan - Bear with me over the next week or so while I get through some more details on our work in this area – I think you will see how the work we are doing is pretty fundamental and provides a better solution in most cases than auto-enlarging ranges.  Just based on the stuff I covered today, for example, smartly maintaining formatting like banding isn’t possible with that approach, charts don’t update when you add data, references to the table don’t update, etc.

One thing to note is that we do not auto-guess tables.  Users have to explicitly declare something a table (like in Word or PPT), and once they do, they get the functionality offered by Excel 12.  If a user doesn’t want that functionality and wants to work with auto-enlarging ranges, they simply don’t declare the range a table.

Additionally, if a user has made something a table, they can absolutely turn off the auto-expand of rows and columns (though I believe that in most cases users will want auto-expand, since it seems to be proving quite intuitive and since it fixes up things like the formatting, borders around the table, and a whole host of other stuff).  In addition, every time we auto-add a row or column, we will provide UI (like the UI that comes up when you paste a range that lets you adjust the formatting) that allows you to undo the expansion.  Our design principle is that the user has to be in full control at all times, and that the user has to be able to undo any “auto” action with no more than one or two clicks.

WRT protection, stay tuned for my next post.

Jon – much more to come!

• David Gainer...

...

|Just based on the stuff I covered today, for

|example, smartly maintaining formatting like

|banding isn’t possible with that approach,

|charts don’t update when you add data,

|references to the table don’t update, etc.

...

Reread my setup. If banding is provided by conditional formatting formulas, e.g.,

=MOD(ROW(),2)

and there's an bottom row with empty cell contents but visible text, inserting rows at that bottom row DOES extend banding to inserted rows, DOES extend number formatting (all formatting), and DOES expand chart ranges that include the contents-empty but visually-nonempty bottom row. Would you like a sample workbook to show how it works?

| . . . (though I believe that in most cases

|users will want auto-expand, since it seems to

|be proving quite intuitive and since it fixes

|up things like the formatting, borders around

|the table, and a whole host of other stuff).

OK, I may just be different. Realize that I've worked with query tables in Lotus 123, which work very much as you describe Excel tables. I found them to be more trouble than they were worth, but that's just my experience.

|column, we will provide UI (like the UI that

|comes up when you paste a range that lets you

|adjust the formatting) that allows you to undo

|the expansion. Our design principle is that

|the user has to be in full control at all

|times, and that the user has to be able to

|undo any “auto” action with no more than one

|or two clicks.

...

Oh good. So you're going to extend this exemplary principle to importing CSV files, in which versions through XL11 *IGNORE* quoting of fields containing text that could be interpreted as numbers, making it essentially impossible to import CSV files containing, e.g., US Zip Codes with leading zeros as separate fields. For example, the following one-line CSV file

"XYZ Inc","123 Gizmo Way","Nashua","NH","06350"

which XL11 & prior will fubar the zip code as (numeric) 6530. Will XL12 empower the user with regard to CSV files? Will someone on your team FINALLY include a optional setting to run the text import wizard for files with .CSV extensions the same as it does for files with .TXT or .PRN extensions? Or maybe just do the decent thing like 123's classic /FIN command, and read *EVERY* double-quote delimited field as **TEXT**!

Consistency in implementing your claimed design philosophy would be nice, "the user has to be in full control at all times." Or is that the user gets to have the illusion of control only here & there?

• Harlan Grove: "Oh good. So you're going to extend this exemplary principle to importing CSV files, in which versions through XL11 *IGNORE* quoting of fields containing text that could be interpreted as numbers, making it essentially impossible to import CSV files containing, e.g., US Zip Codes with leading zeros as separate fields."

Rename .csv to .txt, open, choose comma as separator, mark column with zip codes as text. How hard is that?

• SlashDotJunkie:

Not hard, just fiddly. Why can't Excel pay attention to the quotes?

• I find it funny that no one mentions that the *new* Table thing is Office 2003's lists.

• Dave,

I think the changes you have made are tremendous and I have no doubt that I will continue to think so as you reveal more of Excel 12.  I'm not sure how long you have been Group Program Manager for Excel but the whole product seems to have taken a giant leap forward under your care - well done to you and your team.  With a product like this nothing is ever finished or perfect, not even to any one person's own satisfaction, and there are always going to be competitor's products who have slightly different (or even better?) ways of doing things, but that is simply the nature of the beast.

I say all of this because I am growing slightly tired of Harlan Grove's continuing sarcasm.  Clearly he is an Excel expert and knows his beans and so I try to read his comments with interest.  However, I am finding his scoffing tone rather tedious and a little unfair and would prefer it if he could be a little more objective in his questions and observations.

Thank you,

Nigel

• Oops, sorry, the lists from which tables are based are mentioned in the original post.

• Harlan

"As for the automatic properties you mentioned, just add a line at the bottom of a range containing either ="" or ' in each cell and with number format ;;;"_________", and always insert rows beginning with this row....

=MOD(ROW(),2)"

that sure sounds easy...  NOT!

I have to say I like the idea of something that IMO should be *brain dead simple* to be *brain dead simple*.  Banded rows is a canonical example of an task that frustrated the hell out of me in previous versions. The solution you mention to this problem is exactly the oppposite of simple (proof can be found in # NG questions I see on this) but was required before 12.  IF these formatting smarts are as robust as they claim them to be then I'm in with both feet.

looking forward to the next post, formulas + these table things = ?

• Harlan,

Sure, you can twist, turn, butcher and bodge just about anything in Excel, but to suggest that your solutions are as simple or as useful as the methods you describe is ridiculous.

You're a power user, but how many average users would be able to set up a spreadsheet like you describe?

You also give MS a hard time for using feature that are similar to Lotus. I bet you'd *also* be just as critical if MS were not updating table handling in Office 12, saying things like "How come you didn't make it more like Lotus?"

• Alright, here's my questions:

1) Interaction with protection (you said you'ld cover)

2) Any improvements to lookup functions with tables that will allow me to say something like:

TLOOKUP("TableName", "Criteria", "ReturnedColName")?

I'm currently doing stuff like this with VLOOKUP and MATCH. Though eventually I realized the speed was so slow I moved to a database/excel solution.

Either way, if Excel knows what a table is, it should be possible to use that implicit knoledge of what the header is so that I can act on it like I act on databases.

• Great stuff, David!

I know I'm looking ahead but seeing "Remove Duplicates" on the ribbon was very exciting because it's a common NG question, as you know.

I'm also eager to learn how bottom totals will be handled.  Since first grade most of us have learned that's where totals should go, even though (I think) on top makes as much or more sense.

Jim