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
Using Forms to Enter Table Data
Using the Worksheet Window

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

On your worksheet, select a range of cells you want to make into a Table
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.
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
OPTIONAL: If your selected cell range already has headers (i.e., column labels), select My table has headers
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

Select the data that will make up your Table
From the Insert command tab, in the Tables group, click TABLE
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.
OPTIONAL: If your table does not already have headers (i.e., column labels), deselect My table has headers
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
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:

Select a cell within the Table
From the Design command tab, in the Tools group, click CONVERT TO RANGE
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

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.
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.
From the scroll box, select Form...
Click ADD
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).

Select a cell within the Table
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

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.
Click NEW
The form is cleared and ready for you to enter a new record.
In each text box, type the desired information
To move between fields, press [Tab]
To add the current data to your Table and automatically open a blank form for a new record, press [Enter] or [return]
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.
When you are finished entering data, click CLOSE
The new rows appear at the bottom of your Table.

Editing a Record

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.
To move to the desired row, click FIND NEXT or FIND PREV
OR
Press [Up Arrow] or [Down Arrow]
In the appropriate Form dialog box text boxes, make the desired changes
When finished, click CLOSE

Deleting a Record

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.
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
Click DELETE
A confirmation dialog box appears.
Click OK
Click CLOSE
The record is permanently deleted from your database.

Searching for a Record

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

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.
Click CRITERIA
In the appropriate field(s), type your search criteria
Click FIND NEXT or FIND PREV
Repeat steps 3-4 as necessary
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

Click in the first blank row at the bottom of your Table
Select the cell where you want to enter new data
Type the data accordingly
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.