Chapter 9: Tables and Worksheet Databases

 Download CD Content

One of the most significant new features in Excel 2007 is tables. A table is a rectangular range of data that usually has a row of text headings to describe the contents of each column. Excel, of course, has always supported tables, but the new implementation lets you designate a range as an "official" table, which makes common tasks much easier. More importantly, the new table features may help eliminate some common errors.

This chapter discusses the new Excel 2007 table features and also covers what I refer to as worksheet databases, which are essentially tables of data that have not been converted to an official table.

Tables and Terminology

It seems that Microsoft can't quite make up its mind when it comes to naming some of Excel's features. Excel 2003 introduced a feature called lists, which is a way of working with what is often called a worksheet database. In Excel 2007, the list features evolved into a much more useful feature called tables. To confuse the issue even more, Excel also has a feature called data tables, which has nothing at all to do with the table feature. In this section, I define the terms that I use throughout this chapter.

• Worksheet database: An organized collection of information contained in a rectangular range of cells. More specifically, a worksheet database consists of a row of headers (descriptive text), followed by additional rows of data comprising values or text. I use the term database loosely. An Excel worksheet database is more like a single table in a standard database. Unlike a conventional database, Excel does not allow you to set up relationships between tables.

• Table: A worksheet database that has been converted to a special range by using the Insert Tables Table command. Converting a worksheet database into an official table offers several advantages (and a few disadvantages), as I explain in this chapter.

A Worksheet Database Example

Figure 9-1 shows a small worksheet database that contains employee information. It consists of 1 header row, 6 columns, and 20 rows of data. Notice that the data consists of several different types: text, numerical values, dates, and logical values. Column C contains a formula that calculates the monthly salary from the value in column B.

Figure 9-1: A typical worksheet database.

In database terminology, the columns in a worksheet database are fields, and the rows are records. Using this terminology, the range shown in the figure has 6 fields (Name, Annual Salary, Monthly Salary, Location, Date Hired, and Exempt) and 20 records.

The size of a database that you develop in Excel is limited by the size of a single worksheet. In theory, a worksheet database can have more than 16,000 fields and can consist of more than one million records. In practice, you cannot create a database of this size because it requires an enormous amount of memory, and the weight will cause even a state-of-the-art computer to slow to a crawl.

A Table Example

Figure 9-2 shows the employee worksheet database after I converted it to a table, using Insert Tables Table.

Figure 9-2: A worksheet database, converted to a table.

What's the difference between a worksheet database and a table?

• Activating any cell in the table gives you access to a new Table Tools context tab on the Ribbon.

• The cells contain background color and text color formatting, applied automatically by Excel. This formatting is optional.

• Each column header contains a button that, when clicked, displays a drop-down with sorting and filtering options.

• If you scroll the worksheet down so that the header row disappears, the table headers replace the column letters in the worksheet header.

• Tables support calculated columns. A single formula entered in a column is propagated automatically to all cells in the column.

• Tables support structured references. Rather than using cell references, formulas can use table names and column headers.

• The lower-right corner of the lower-right cell contains a small control that you can click and drag to extend the table's size, either horizontally (add more columns) or vertically (add more rows).

• Excel is able to remove duplicate rows automatically.

• Selecting rows and columns within the table is simplified.

Uses For Worksheet Databases and Tables

People use worksheet databases (or tables) for a wide variety of purposes. For some users, a worksheet database simply keeps track of information (for example, customer information); others use a database to store data that ultimately appears in a summary report. Common database operations include

• Entering data into the database

• Filtering the database to display only the rows that meet certain criteria

• Sorting the database

• Inserting formulas to calculate subtotals

• Creating formulas to calculate results on the data, filtered by certain criteria

• Creating a summary table of the data in the table (often done by using a pivot table)

When creating a worksheet database or table, it helps to plan the organization of your information. See the sidebar, "Designing a Worksheet Database or Table," for guidelines to help you create tables.

Designing a Worksheet Database or Table

Although Excel is quite accommodating with regard to the information that is stored in a worksheet database, planning the organization of your information is important and makes the data easier to work with. Remember the following guidelines when you create a worksheet database or table:

• Insert descriptive labels (one for each column) in the first row (the header row). If you use lengthy labels, consider using the Wrap Text format so that you don't have to widen the columns.

• Make sure that each column contains only one type of information. For example, don't mix dates and text in a single column.

• Consider using formulas that perform calculations on other fields in the same record. If you use formulas that refer to cells outside the database, make these absolute references; otherwise, you get unexpected results when you sort the table.

• Don't leave any empty rows within the worksheet database. For normal worksheet database operations, Excel determines the database boundaries automatically, and an empty row signals the end of the data. If you're working with a table, empty rows are allowed because Excel keeps track of the table dimensions.

• Freeze the first row. Select the cell in the first column and first row of your table and then choose View Freeze Panes Freeze Top Row to make sure that you can see the headings when you scroll the table. This action is not necessary with a table because table headers replace the column letters when you scroll down.

Don't worry if you later discover that your worksheet database or table needs one or more additional columns. Excel is very flexible, and adding new columns is easy.