Microsoft Office Tutorials and References
In Depth Information
Chapter 9: Tables and Worksheet Databases
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
h Entering data into the database
h Filtering the database to display only the rows that meet certain criteria
h Sorting the database
h Inserting formulas to calculate subtotals
h Creating formulas to calculate results on the data, filtered by certain criteria
h 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 “Designing a worksheet database or table” sidebar for guidelines to help you create tables.
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.
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
● 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 to read the labels.
● 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.