Database Access

Over the years , most spreadsheets have enabled users to work with simple flat database tables. Excel has some slick tools.

Databases fall into two categories:

• Worksheet databases: The entire database is stored in a worksheet, limiting the size of the database.

• External databases: The data is stored in one or more files and is accessed as needed.

Worksheet databases

Generally, a rectangular range of data that contains column headers can be considered a worksheet database.

NEW  

Excel 2007 enables you to specifically designate a range as a table. Select any cell in your rectangular range of data and choose Insert Tables Table. Using a table offers many advantages: an automatic summary row at the bottom, easy filtering and sorting, auto-fill formulas in columns , and simplified formatting. In addition, if you create a chart from a table, the chart expands automatically as you add rows to the table.

Particularly useful is working with columns of data in a table. Each column header is actually a drop-down list that contains easy access for filtering or sorting (see Figure 2-24). Table rows that don't meet the filter criteria are temporarily hidden.

Figure 2-24: Excel's new table feature makes it easy to sort and filter rows.

External databases

To work with external database tables, use the commands in the Data Get External Data group . Excel 2007 can work with a wide variety of external databases.