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

  • Article

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Summary: Learn about enhancements to tables in Microsoft Office Excel 2007 such as selecting parts of a table with a single mouse click, quickly locating your position in a table, and maintaining table formatting as you add data. (4 printed pages)

Frank Rice, Microsoft Corporation

September 2007

Applies to: Microsoft Office Excel 2007

Contents

  • Overview

  • Properties Now Persist with the Table Component

  • Selecting Table Components

  • Finding Your Place in the Table is Easy

  • Conclusion

  • Additional Resources

Overview

In my previous OfficeTalk column in this series, I give an overview of some of the improvements to tables in Microsoft Office Excel 2007. One of the key benefits of these improvements is how other features in Excel 2007 behave more predictably when a table is present. This is because Excel 2007 knows exactly where the table starts and ends, where the header row is, which cells make up the data, the columns which contain the data, and where the total row is. So how do these improvements benefit you? In this column, I describe the ways in which improved awareness of the structure of your data improves the user experience.

Properties Now Persist with the Table Component

When you apply a property to a column of data in a table, that property persists with the column. What exactly does that mean? When, for example, you apply conditional formatting to an entire column, Excel 2007 assumes that you intend the conditional formatting to continue to apply to the entire table column. As you add new rows to the table, either in the middle or at the end, or as you delete rows, Excel 2007 automatically extends or contracts the conditional formatting rule appropriately. The rule persists, or stays with, the column.

There is an example of this persistent behavior in my previous column in this series when I added data to an existing column. However, this behavior does not apply only to conditional formatting. This behavior works with just about anything you apply to a column including formatting (anything in the Format Cells dialog box available from the Home tab), cell protection, and data validation. In addition, this behavior also applies to anything that contains a reference, such as formulas, charts and PivotTable dynamic views.

How do you ensure that the property persists with the column? There is no explicit way to do this. Excel 2007 just assumes that anytime you apply a setting to an entire column, the setting should always follow the column. So, when you select a table column and apply a data validation rule, that rule grows and shrinks with the table. Likewise, when you create a PivotTable that uses a table as its data source, the PivotTable automatically includes the new rows in the PivotTable as you add them to the table in Excel. The same action applies to charts. To be precise, in this context, the "entire column" really means all of the data rows. It is not necessary to include the header cell; this way you do not have to include the header to make the "currency" number format stay with a column.

This persistent behavior also applies to new columns. For example, if you use a table as the data source for a chart, Excel updates the chart to include any new columns added to the table. In addition, properties set on the entire table, table headers, and the totals row stay with the item when the table is used with the chart.

Selecting Table Components

Because Excel 2007 recognizes entities like table rows and columns, you now have a simple way to select those elements. You can select one or more rows, one or more columns, or the entire table with a single mouse click. To select a column in a table, simply move the mouse pointer over the header until the pointer becomes a down-arrow and then click (see Figure 1).

Figure 1. You can select an entire column with a single mouse click

To select the entire table, move the mouse pointer to the upper-left corner of the header row until it becomes a diagonal-arrow and click (see Figure 2).

Figure 2. You can select the entire table with a single mouse click

Column and table selection has two behaviors. The first click always selects just the data portion of the column or 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 or table, including the header. This is useful for copy and paste operations or for rearranging columns in a table.

You can also access these selection behaviors using the keyboard. The existing shortcut keys for row and column selection, SHIFT+SPACE and CTRL+SPACE respectively, work with tables in Excel 2007. For example, pressing CTRL+SPACE once selects all the data rows in a table column. The second keystroke expands the selection to encompass the entire table column, and the third keystroke expands the selection again to encompass the entire worksheet column.

Finding Your Place in the Table is Easy

When working with large data sets, it is easy to scroll the headers off the screen and lose sight of which column you are in, especially if many of the columns have similar data. After you declare a table in Excel 2007, this problem is eliminated. When navigating a large table and the table's headers scroll off the screen, Excel 2007 inserts the table headers into the sheet header row where the A, B, C headings appear. Figure 3 shows the table header before scrolling. Figure 4 shows the headers after scrolling.

Figure 3. The worksheet column headers before scrolling

Figure 4. After scrolling, the worksheet column headers change to the table column headers

This helps you keep track of the column you are working in without using split screens or the freeze panes feature.

Note

The table headers are visible only as long as the active cell is somewhere inside the table. If you select a cell outside of the table, the standard A, B, C headers are displayed.

Conclusion

The improved awareness of table structure in Excel 2007 has several benefits. For example, formatting persists with a column as you add values. In addition, column headers change as you scroll through the table so that you always know which column you are working in. In future OfficeTalk 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: