Dick Moffat - Office Developer

Structured References in Calculated Columns in Tables in Excel 2007

I have been doing a project in Excel 2007 and have discovered a new feature that as far as I’m concerned easily justifies movement to 2007.  The enhancements in “Table” technologies within the new product bring a whole new opportunity for users (and developers) in Excel to manage list (including especially lists sourced from External data).

In Excel 2003 Tables were enhanced dramatically but what was delivered was definitely a 1.0 product.  In 2007 we see the results of research and development on the technology applied in an intelligent and productive way.

As in 2003 you can create a Table simply by going Insert – Table from the Ribbon.  If there is an existing range of cells, with data in a tabular format, Excel will ask you whether you want to create a table and the usual question about titles.  At this point you have created a table with a default name.  I suggest before you do anything further that you rename the range using the new “Name Manager” on  the Formula Tab.  This is the data that I created and named “tblNames”:

There are lots of useful features available to users of this Table that I won’t delve into here, but the one feature that I am especially impressed with is the use of “Structured References” in “Calculated Columns”.

Let’s add a Column to the Table by hitting the Secondary Mouse Button  and choosing to insert a column to the table:

Then we will make a title for the new field of “Full Name”.

Now we go to the first cell below the “Full Name” heading and hit the equals sign “=” and then click on the word “Dick” in the second row of the “First” column:

Before you hit Enter, notice the formula that has been created (also because we are not ready to hit Enter yet).  This is a “Structured Reference”.  This kind of formula is the key new technology in Table technology in Excel 2007.

Notice how it has picked up the name of the Table range you are in automatically.  Also notice the automatic picking up of the Field name.  Of course also notice the reference to [#This Row]….. pretty self explanatory (?).

Now type an ampersand “&” followed by the space character in quotes followed by another ampersand and then click on the first cell in the “Last” field:

Notice how it continues to construct your formula using “Structured References”.

Now hit Enter and you will see the results of the formula pull together a full name for that row BUT also at the same time it puts the relative referenced formula in every cell down that column resulting in this:

Now if you select a row and insert a new blank row, the formula in the “Full Name” field gets added automatically in the new row.

This is the beginning of an exciting process of discovery the various ways this technology can be used to your needs.  For example how about this formula:

And this one:

 You can use these “Structured References” in any formula in Excel (if you're referring to a defined Table), giving you way improved self-documenting, self-managing functionality.

What is doubly interesting is that if you create a Table using the External reference sub of the Data tab, the same functionality is available……

This pushes the ability of Developers to create powerful and reliable data-driven solutions within Excel. 

This also gives the Power-User a huge leg up on creating the kind of solutions they have had to stand on their heads using VBA and Copy and Paste with simple structured Tables. 

Get it?    This is Major Stuff !!!   Look into it !!!