(Legacy) Microsoft Excel 2007: Creating Tables

Excel Tables are useful for managing sets of related data. Excel 2007 makes it easy to set up a Table and add data to it. For basic information on Tables, refer to Tables Overview.

Creating a Table

By creating a table with Excel's Table button, you will have access to Table Tools and the accompanying Design command tab (neither of which are available for normal a data range).

You can either create a blank table or create a table from an existing data range.

Creating a Table: From a Blank Cell Range

1. On your worksheet, select a range of cells you want to make into a Table.
   

2. From the Insert command tab, in the Tables group, click Table. 
   NOTES:
   The Create Table dialog box appears, displaying the selected cell range.
   Behind the Create Table dialog box, the selected cell range is highlighted with an animated border. 
   

3. (Optional) To specify a different cell range, in the Where is the data for your table? text box, type the desired cell range.
   OR
   To select the range,
   1. Click Collapse Dialog Box. 
   2. Select the desired cell range.
   3. Click Expand Dialog Box. 

   4. Click OK.

4. (Optional) If your selected cell range already has headers (i.e., column labels), select My table has headers.

5. To accept the selected cell range for your table, click OK.
   The selected cell range is converted into a Table.
   
   NOTE: For information on Table terms, refer to Tables Overview: Table Terms.

Creating a Table: From an Existing Data Range

1. Select the data that will make up your Table.

2. From the Insert command tab, in the Tables group, click Tables. 
   The Create Table dialog box appears, displaying the selected data range.
   If Excel detects headers (i.e., column labels) in the selected data range, the My table has headers option is automatically selected.

3. (Optional) If your table does not already have headers (i.e., column labels), deselect My table has headers.

4. (Optional) To specify a different cell range, in the Where is the data for your table? text box, type the desired cell range.
   OR
   To select the range,
   1. Click Collapse Dialog Box. 
   2. Select the desired cell range.
   3. Click Expand Dialog Box. 

   4. Click OK.

5. To accept the selected cell range for your Table, click OK.
   The selected cell range is converted into a Table.

To convert a Table back to a data range:

1. Select a cell within the Table.

2. From the Design command tab, in the Tools group, click Convert to Range. 

3. In the confirmation dialog box, click Yes.
   The Table is converted to a range.

Using Forms to Enter Table Data

Once your Table has been created, Excel provides an easy way to enter data called the Form feature. Instead of moving the cursor to each new cell, with the Form feature you can add and edit Table data from a simple dialog box. The Form dialog box is also helpful for searching for records. The Find Next and Find Prev options make locating a specific record easier.

In Excel 2007, to access the Form feature, the Form button must first be added to the Quick Access toolbar.

Adding the Form Button to the Quick Access Toolbar

1. At the top of the Excel window, to the right of the Quick Access toolbar, click Customize Quick Access Toolbar  » select More Commands.
   The Excel Options dialog box appears, with Customize selected.

2. From the Customize Quick Access Toolbar section, in the Choose commands from pull-down list, select Commands Not in the Ribbon.
   The scroll box under the pull-down list refreshes to display several commands not found on the Ribbon.

3. From the scroll box, select Form...

4. Click Add.

5. Click OK.
   The Form button is added to the Quick Access toolbar.

Accessing the Form Dialog Box

NOTES: 
The Form feature cannot be accessed from a blank worksheet; you must have an existing Table. 
If you try to open the Form dialog box from empty cell range (i.e., not a Table), a dialog box will appear giving you the option to either use the first row of the selection as labels and not as data (i.e., the Form dialog box opens), or to cancel and make any appropriate changes to your database (i.e., the Form dialog box does not open).

1. Select a cell within the Table.

2. From the Quick Access toolbar, click Form. 
   The Form dialog box appears displaying the sheet name, your Table's field names (i.e., column labels), and any previously entered row data.
   NOTE: The following two graphics depict a Table and the form dialog box when opened from the table.
   
   
   

Using Form Options

Adding a New Record

1. Access the Form dialog box.
   NOTES: 
   The current view in the the Form dialog box always represents one Table row (i.e., a blank new row or an existing row with previously entered data). 
   The Form dialog box displays existing column labels with corresponding text boxes representing the individual cells under each column.

2. Click New.
   The form is cleared and ready for you to enter a new record.

3. In each text box, type the desired information.

4. To move between fields, press [Tab].

5. To add the current data to your Table and automatically open a blank form for a new record, press [Enter] or [return].

6. Repeat steps 3–5 as needed.
   NOTE: Your Table will automatically expand if you enter more records than the number of rows you initially indicated for the table.

7. When you are finished entering data, click Close.
   The new rows appear at the bottom of your Table.

Editing a Record

1. Access the Form dialog box.
   NOTES: 
   The current view in the the Form dialog box always represents one Table row (i.e., a blank new row or an existing row with previously entered data). 
   The Form dialog box displays existing column labels with corresponding text boxes representing the individual cells under each column.

2. To move to the desired row, click Find Next or Find Prev.
   OR
   Press [Up Arrow] or [Down Arrow].

3. In the appropriate Form dialog box text boxes, make the desired changes.

4. When finished, click Close.

Deleting a Record

1. Access the Form dialog box.
   NOTES: 
   The current view in the the Form dialog box always represents one Table row (i.e., a blank new row or an existing row with previously entered data). 
   The Form dialog box displays existing column labels with corresponding text boxes representing the individual cells under each column.

2. To select the existing record you want to delete, click and hold the scroll bar dragging up or down.
   OR
   Click Find Prev or Find Next.

3. Click Delete.
   A confirmation dialog box appears.

4. Click OK.

5. Click Clsoe.
   The record is permanently deleted from your database.

Searching for a Record

The Criteria feature allows you to search according to desired criteria.

1. Access the Form dialog box.
   NOTES: 
   The current view in the the Form dialog box always represents one Table row (i.e., a blank new row or an existing row with previously entered data). 
   The Form dialog box displays existing column labels with corresponding text boxes representing the individual cells under each column.

2. Click Criteria.

3. In the appropriate field(s), type your search criteria.

4. Click Find Next or Find Prev.

5. Repeat steps 3-4 as necessary.

6. Click Close.

Using the Worksheet Window

If you need to make only minor changes in your Table, it may be quicker to make them in the worksheet view.

Adding a Row

1. Click in the first blank row at the bottom of your Table.

2. Select the cell where you want to enter new data.

3. Type the data accordingly.

4. Press [Tab].
   OR
   Use the arrow keys to move to the next field of the record.
   NOTE: As you enter data and move within the row, that row is automatically added to your Table.

Deleting a Row

For information on deleting Table rows, refer to Working with Rows and Columns: Deleting Rows.

Searching for Specific Terms or Values

For information on searching in your Table, refer to Using the Find and Replace Features: Using the Find Feature.