OfficeTalk: Bringing Improvements to Tables in Excel 2007 (Part 3 of 6)

  • Article

Summary: Structured referencing in Microsoft Office Excel 2007 makes working with tables and table components easier. See how features such as calculated columns and table Totals rows reduce errors when you enter formulas. (6 printed pages)

Frank Rice, Microsoft Corporation

October 2007

Applies to: Microsoft Office Excel 2007

Contents

  • Overview

  • How Structured References Work

  • Using Structured Referencing in the Table

  • Calculated Columns Can Help Reduce Errors

  • Totals Rows in Tables Encapsulate Significant Functionality

  • Conclusion

  • Additional Resources

Overview

One of the results of the improvements made to tables in Microsoft Office Excel 2007 is a reduction in the overall maintenance required to keep a worksheet functioning well over time. To achieve this, worksheets are now less prone to error because the number of keystrokes necessary to create and update the table is reduced. The interaction between tables and formulas is an important part of meeting that goal.

Excel 2007 provides structured referencing, a new way to add references to tables and the parts of tables. Structured referencing allows you to add a reference to a table and subsets of the table directly by name as opposed to by cell coordinates. This ability resembles named ranges, but with a few crucial differences. First, the reference names are automatically generated when the table is created. Specifically, this includes the name of the table itself (which by default is Table1 or the next number in the sequence), and the names of all the columns. Also, the names are automatically removed as columns are deleted or the entire table is deleted. Finally, and perhaps most importantly, the names automatically adjust as the table grows and shrinks. As a result, structured referencing simplifies table maintenance by eliminating many of the issues associated with named ranges.

How Structured References Work

So how do you use structured references? Structured references are an addition to the syntax of formulas in Excel 2007. Basically, this is how it works:

  • A reference to a table looks like =Table1, so if you want to sum the values in a table, you use =SUM(Table1). Note that =Table1 returns all of the data in Table1 without the headers; this occurs because many of the common functions that work on ranges, like VLOOKUP, assume no headers.

  • A reference to a column looks like =Table1[Column1]. Again, this reference returns just the data. So, for example, if you want to SUM a column, you type =SUM(Sales[2004]).

Figure 1. You can reference tables and columns by name

You can reference tables and columns by name

Another advantage to structured referencing is its tight integration with the formula auto-complete feature. This means that formula auto-complete is available for both table names and columns within tables. For example, to build the structured reference in Figure 1, start with a named table, as in Figure 2.

Figure 2. Start with a named table

Start with a named table

Next, type =SUM(S. Notice that the auto-complete drop-down lists all entries that start with S, including formulas, named ranges, and this table, Sales.

Figure 3. Activating auto-complete in a formula

Activating auto-complete in a formula

After selecting the Sales table, you see all the columns in the table.

Figure 4. After selecting the table, you see the columns in a drop-down list

You see the columns in a drop-down list

With a single click or keystroke, Excel 2007 completes the reference.

Figure 5. A single click completes the reference

A single click completes the reference

The last thing that you need to do is close the parenthesis and press ENTER. It is easy to see how this can improve accuracy and efficiency.

Using Structured Referencing in the Table

You can use structured references inside tables as well. In this area, Excel 2007 has other new behaviors. For example, assume that you are reviewing sales figures and you want to calculate the percentage of change in sales over the last few years for each row in a table.

To do this, you use a formula like =(FY05-FY02)/FY05. First, you add a column by typing Percent Growth after the last column in the table.

Figure 6. You start the process by typing after the last column

Start the process by typing after the last column

When you press ENTER to commit the change, Excel 2007 adds another column to the table, applies the appropriate formatting to the rows, and adjusts the border around the table.

Figure 7. Adding the new column also adds the formatting

Adding the new column also adds the formatting

The next step is to write the formula, so you type =( and then use the keyboard to move the selection to the cell you want to calculate, in this case FY05. Excel 2007 gives you the reference [FY05].

Figure 8. Using auto-complete in a formula

Using auto-complete in a formula

As you see, when referring to a table from within the table, you do not need to prefix the table name. When you use other columns in the formula, they get similar references.

Figure 9. There is no need to reference the table name

There is no need to reference the table name

When you finish with the formula and press ENTER, Excel 2007 automatically fills that formula down for all rows in the Percent Growth column. 

Figure 10. The new column is automatically filled down

The new column is automatically filled down

Calculated Columns Can Help Reduce Errors

The previous example demonstrates another new feature of tables called calculated columns. When you enter a formula into an empty table column, the column automatically fills the remainder of the rows in the column. It is easy to see how this can reduce errors introduced by manual filling or by using copy and paste. In addition, not only does the column fill-down, but it continues to adjust as you add or delete rows in the table. As with other table features, you have control over its behavior—when you type a formula into a cell in a calculated column, you can undo the automatic fill-down using the user interface. You can even disable the feature altogether.

If you need to update the calculated column, you only need to edit one copy of the formula and the change propagates to all rows. In addition, you can change any single row (such as enter a static value or custom formula) so that it is not consistent with the calculated column. Excel 2007 flags such cells visually so that inconsistencies are easy to detect. For example, when you type =RAND() in the middle of a column and choose not to fill down, you see a green triangle in the upper left corner of the cell.

Figure 11. A green triangle indicates possible inconsistencies

A green triangle indicates inconsistencies

Furthermore, if a calculated column contains inconsistencies, subsequent edits to cells in the calculated column do not propagate because Excel 2007 does not overwrite custom values. However, the same green triangle shown in Figure 11 appears allowing you to modify the behavior.

Totals Rows in Table Encapsulate Significant Functionality

Another new feature in Excel 2007 is the Totals row, which is similar to lists in Excel 2003 but with improvements. The Totals row is another special area of the table, similar to the header row. The Totals row exists at the bottom of the table and contains calculated totals for the columns in a table.

Figure 12. The Totals row is now a designated area in the table

The Totals row in the table

To activate the Totals row, right-click when the mouse pointer is over the table. This option also appears on the Table tab in the Office Fluent Ribbon, or by using the AutoSum functionality. An advantage of a dedicated area for totals in the table is that it is included in other table operations. For example, the row grows and shrinks with the table. The Totals row gets special formatting applied to it when you apply a style to the table. Lastly, the formatting of the row behaves appropriately, as you add more columns to the table.

The cells in the Totals row of a table can contain any kind of formula (the formulas do not even have to refer to the table at all) or text labels. The cells in the Totals row also contain drop-down lists that display some of the most commonly used functions, making it easy for you to write formulas.

Figure 13. Drop-down list of functions in the Totals row

Drop-down list of functions in the Totals row

You can choose to toggle the Totals row on or off. Any functions that you enable when the Totals row is displayed persist the next time you choose to display it.

To conclude, I show you how to add a reference to the different table components. To add a reference to:

  • The entire table, type: =Table1[#All].

  • The table headers, type: =Table1[#Headers].

  • To the entire column, type: =Table1[[#All], [Column1]].

  • The header value of a column, type: =Table1[[#Headers], [Column1]].

In addition, you can also combine the keywords as follows: =Table1[[#Headers],[#Data],[Column1]].

Additional points to consider when working with structured references:

  • Table names are globally unique to a workbook and do not require a sheet reference like Sheet1!Table1.

  • Table column names must be unique within the table itself.

  • Table names coexist in the same namespace as named ranges, meaning that you cannot have a defined name range with the same name as a table, and the reverse as well.

  • If the table name or any of the column names change then any formulas that refer to those names are updated automatically as well.

  • You can create structured references using selections made with the mouse (and you can also disable this option). In fact, structured selection is one way to generate structured references quickly when writing formulas.

Conclusion

Structured referencing in Excel 2007 makes adding references to tables and table components very easy. This can significantly reduce errors made when entering formulas and referencing columns and cells. Additionally, calculated columns make fill operations seamless. Totals rows are a special feature in tables in Excel 2007 that contain their own functionality. In future Office Talk columns, I discuss more of the core features of tables.

Additional Resources

For more information about working with tables in Excel 2007, see the following resources: