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

Summary: Learn about enhancements to data tables in Microsoft Office Excel 2007 and how working with tables just got easier. See how the changes help you focus on what you want to do instead of how to do it. (4 printed pages)

Frank Rice, Microsoft Corporation

August 2007

Applies to: Microsoft Office Excel 2007

Contents

  • Overview

  • Tables as Native Structures in Excel 2007

  • Creating Tables

  • Adding Data to Tables

  • Conclusion

  • Additional Resources

Overview

In the next few OfficeTalk articles, I discuss new features and improvements to data tables in Microsoft Office Excel 2007. For example, I'll show:

  • Improvements to the way you add rows and columns

  • An increased awareness of table parts, giving you more control over table behavior

  • Persistence of formatting as you add new rows and columns to the table

  • That there is a better awareness of the cursor location in large tables

  • How you format entire rows based on triggering a condition in a column

  • How you format cells based on the comparison of data in two columns

  • That you can now refer to tables or subsets of tables by name instead of cell coordinates

  • That there is automatic fill on calculated columns

  • An increased awareness of a distinct "totals" row

To differentiate tables from other structures in Excel 2007, let me describe what I mean by tables. A table is a simple structure where each row corresponds to a single "item" (such as a region, a transaction, or an individual product). Each column denotes a specific piece of information that is shared by all rows (such as the total sales, the amount of each transaction, or product inventory quantity). Tables typically have a "header" row at the top that defines the information that each column contains.

Figure 1. A simple table of regional sales data

Table of regional sales data

The two-dimensional nature of the spreadsheet makes it an obvious choice for manipulating and analyzing tabular data. Office 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. This has changed in Excel 2007.

Tables as Native Structures in Excel 2007

In Excel 2007, tables are now native structures built into the product. When you are working with a tabular structure, the options available to you are those that logically relate to the actions you perform in the grid. The table in Figure 2 is formatted to apply a data bar to the Profit column, a chart describing the data is included, and a formula is added at the top of the sheet that totals the Profit column.

Figure 2. A formatted table in Excel 2007

Formatted table in Excel 2007

After formatting, the next thing you may typically do is add more data to the table. Look at what happens when you type a value just below the table in cell D9. After I add the data and press ENTER, several things happen automatically:

  • The value that I entered is added to the table. Excel 2007 assumes I appended data to the table.

    Note

    If you did not intend to add the data to the table, when you press ENTER, a smart tag is displayed that allows you to choose whether to undo the table expansion or disable the auto expansion feature altogether.

  • When the data is added to the table, the table formatting automatically extended to the new row. For example, notice that Excel automatically maintains the alternating row colors.

  • The conditional formatting data bar also extends to the new row so Excel is now using the range D7:D11 to calculate the relative size of the data bars.

    Note

    For more information about Excel 2007 conditional formatting data bars, see Additional Resources.

  • The formula above the top of the table updates itself to include the new data.

Without these improvements to the table, you must manually adjust the cell formatting, conditional formatting, formula and chart every time you append a value. What previously took multiple steps now occurs automatically thanks to the improved and new table features. This is possible because Excel 2007 recognizes objects like a table, table columns, table headers and other related parts. Excel uses this information to respond to your actions while you are working in the worksheet. In the following sections and future Office Talk articles, I'll review some basics and present examples.

Creating Tables

You can create a table simply by clicking the Table command on the Insert tab on the 2007 Office Fluent Ribbon (or even quicker, use the CTRL+L keyboard shortcut). Clicking the Table menu item displays a dialog box where you specify the range for the table, and indicate if the data already contains a header row as shown in Figure 3.

Figure 3. The Create Table dialog box

Create Table dialog box

When you create a table, a new tab appears on the Office Fluent Ribbon that is specifically designed for tables. The Table Tools tab only appears when the active cell is inside a table. The tab contains options and settings that are specifically for tables. Figure 3 is a screenshot of the Table Tools tab.

Figure 4. The Table Tools tab in Excel 2007

Table Tools tab in Excel 2007

Office Excel 2007 also extends the new table features to the existing external data query functionality, referred to as "query tables." New queries benefit from the features of tables (however, Web queries and text queries are excluded from these new updates). Future articles in this series illustrate the benefits of making query tables into tables. Note that if you have an existing query, you can convert it to a table by using the Office Fluent Ribbon or by using the keyboard shortcuts described previously. Tables are also created when you import XML data into a worksheet by using the XML features that were added in Microsoft Office Excel 2003.

Adding Data to Tables

You saw in the previous example, a method of adding a new row to a table. Adding a new column works the same way: place the cursor in a cell outside the table to the right, and type in a value. Excel 2007 automatically expands the table to include the new data. If this is not the desired behavior, then a simple command available in a smart tag resizes the table and leaves the value outside the table. There are other ways to add rows. Much like tables in Microsoft Office Word 2007, pressing the TAB key when the active cell is in the last column of the last row causes Excel 2007 to add a new row and move the active cell to the first column of the newly added row. In addition, pressing the ENTER key when the active cell is in any cell of the last row also causes 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 see the resize handle in the lower right corner of the table in Figure 1.

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

Conclusion

As you can see, adding rows and columns to your tables is much easier in Excel 2007. In addition, table formatting persists with these additions. In future Office Talk columns, I'll discuss more of the core features of tables.

Additional Resources

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