Working with a Database or Table
The first row of a database or table has to contain the labels for the columns (fields). Microsoft advises you to format these cells as text before you type any of the labels, but there doesn’t seem to be any real need for this action. Generally speaking, a column label that isn’t text is pretty rare, and the General format, which is the default when you type text, seems to work just fine with all the database operations.
Note
Formatting the cells as text after the fact works without a hitch, so there’s even less reason to do it beforehand. The only possible exception would be if you were using some sort of unusual label (like a date) for your fields.
Creating a database and formatting it as a table
Here’s the database and table creation process, step by step:
1. | Type the field labels in the first row. |
2. | Type the list data starting in the second row, entering one record of data in each row (see Figure 19-1). Figure 19-1. Entering a list of information that will be formatted as a table.
|
3. | Select the range that holds the list of data you entered. |
4. | Select Insert  Tables  Table. |
5. | Leave the My Table Has Headers checkbox checked in the Create Table dialog box that appears (Figure 19-2), and then click OK. Excel converts the list to a table, displaying an AutoFilter arrow at the top of each column and displaying the Table Tools, as shown in Figure 19-3. |
Caution
You should have only a single database or table on a worksheet. Even though it’s physically possible to have more than one, you run a risk of confusing Excel when it comes to manipulating the data or performing other operations like a mail merge. Because you can have multiple worksheets in a workbook, this requirement doesn’t pose any kind of practical problem.
Note
If you stop after Step 2 above, you can still perform operations like sorting the list of data. However, defining the data as a table enables you to take advantage of extra data features in Excel.
Applying a table format
Excel applies a colorful table style to each new table you define in a workbook file. When you click in any cell in the table and then click the Design tab, the tab offers contextual Table Tools (refer to Figure 19-4) for working with the table. For example, you can click one of the Table Styles choices at the far right to apply new formatting to all the cells in the table.
Figure 19-4. You can apply new formatting and more using these Ribbon choices.
You can work with the choices in the Table Style Options group, for example, to specify whether the rows in the table are banded (formatted with different colors and rules or not).
The Tools and External Table Data groups offer useful choices such as Convert to Range, which converts a table back to a regular range of text, and Remove Duplicates, for finding duplicate records (rows) and removing the extras.